Overview
The objective of this article is to give the reader a perspective on SQL Server from a person who has specialized in SQL Server for over three years.
What is Microsoft Bringing to the Table with 7.0
SQL Server 7.0 is a radical improvement over previous versions of SQL Server because the architecture has been totally rewritten. Many of the things that Microsoft wanted to do with SQL Server
simply could not be accomplished because of the Sybase architecture of the database.
The next few sections represent the most significant highlights of what SQL Server provides.
DTS
This feature is at the absolute top of the list of added great features. DTS stands for Data Transformation Services. DTS is a new feature of SQL Server. It is a graphical data transfer and
transformation tool. This function is designed to be a powerful and easy to use ETL (Extract Transform Load) tool.
DTS can use virtually any data source and destination. For example, the source may be a DB2 database and the destination Oracle. Of course, Microsoft is recommending that the destination is SQL
Server, but that does not have to be the case.
The transformation part of DTS is very flexible. There are several ways of defining the transform logic. The graphical interface permits the developer to draw lines between source and destination
fields, define a SQL query, use query builder, create VBScript, create JavaScript, or create Perl Script.
DTS builds packages using the DTS Designer. These packages permit the developer to establish workflow.
For example, DTS also has the ability to track data lineage right down to the row level. In other words, for a specific row it can actually reveal what version of a DTS package and what load date
brought in the data. It can also provide comprehensive dependency information. The benefit of this information is the ability to know the impact of a schema structure change.
The features included just in this one technology could (and should) become books. There have been no authors to my knowledge planning on addressing this topic as of the time of this writing.
However, authors should consider writing about this feature because there is a very large market not being filled by anyone comprehensively.
OLAP
The next largest new feature is the MS OLAP Services. Bundled with SQL Server is the ability to create multidimensional data analysis structures. These structures are called cubes.
Excel 2000 has been designed to read the cube and generate a pivot table. This pivot table can be charted and exported into a web browser using XML and Active X.
Literally, the technology to bring web based OLAP to your organization is bundled with SQL Server and Office 2000.
Oracle Beware
SQL Server shouldn’t be a replacement for Oracle in every situation. Also, it is very healthy for the consumer for Oracle to continue to do well as a database vendor. This gives Microsoft
motivation to continue striving to make SQL Server better. Of course, the opposite is also quite true. The better SQL Server does, the more motivation Oracle has for improving their database
software product. However, this being said, the new version of SQL Server performs extremely well.
One example of how SQL Server 7.0 scales well is found with the Unysis implementation of a 2.4 Terabyte data warehouse. They leveraged the new filegroup feature of SQL Server against 200 physical
disks to create a very impressive throughput.
Microsoft Repository
There is a tremendous need for development tools to exchange metadata. Various initiatives to satisfy this need have met limited success. Microsoft stands a much greater chance for success with
their initiative because they are leveraging their development efforts and vendor alliances.
At this point in time the repository is just beginning. The repository does function. But, right now it is just a database structure. Both the structure and the tools to interact with it will
undergo great changes in the next couple of years.
The bottom line of this feature is the fact that having SQL Server in the architecture may be critical to realizing better meta data management objectives.
Other Items
The actual count of improvements to SQL Server is a very large list. The on-line documentation provides explicit detail on these improvements.
If you haven’t already noticed, each of the items on the top of the list of improvements are all strongly focused on data warehousing tools. If there is one predominate theme that you can discern
from Microsoft’s SQL Server efforts it is clearly “Data Warehousing is our target”.
Observations Thus Far
Generalizations are sometimes very tricky. As Mark Twain once stated, that a foot in a bucket of ice water and a foot in a bucket of scolding hot water hardly average out to a comfortable
experience. There are some great experiences with version 7 and some very disappointing experiences. However, there is reason to be very optimistic.
Pros
Microsoft has hired some of the top names in the field of database software development. Because of this fact, all the new features in this product are very stable for essentially being version
1.0.
Overall version 7.0 has proven to be much more stable than version 6.5.
This version provides rich database and development functionality at a very low cost.
Cons
Several technical support calls with Microsoft has revealed that some of this advanced technology is not yet quite understood.
Some of the quirky things that one will find are the inability to create a calculated field whenever several dimensions in a snowflake schema are built and the abnormal termination when using a
group by in some circumstances.
Future
There were definitely features that Microsoft opted not to include in this version. The scope of the rewrite of the entire database had to have some form of limits. I’m sure that there are others
like myself that wish there were a few features that they would have included, but there is always the future version of 7.5 or 8.0.
Features that one could expect to be in some near future release:
- Pre and Post triggers (as opposed to only post)
- Stored procedure as a function able to be included in a select statement
- A logical model to compliment the existing physical model
- Integration of the model into the repository
- Additional advances with DTS
Summary
There are plenty of Microsoft “bashers” who seek every opportunity to proclaim negative things against Bill Gates. But, as a whole, Microsoft has been leading the way in showing rich
functionality improvements along with a very competitive pricing. This strategy stands in stark contrast to the expensive data warehousing tool vendors who will have you spending hundreds of
thousands of dollars to implement the project whereas Microsoft has included the tools as part of the database software. This pricing strategy was very smart for two reasons. First, it is one heck
of a competitive advantage. But more importantly, it reduces the ROI barrier that formerly prohibited many from tackling the data warehouse initiative.
