Although it has been two weeks since my last post, I have not been on vacation. I have a few posts that will come out over the next few days, but I wanted to write up a small post to tell you that I have two new white papers that have just been published by SQL Serve Magazine for DELL.
When I returned from the hike, there was an excellent piece of news waiting for me: MS Press has accepted and confirmed the latest edit of the two books written by two Solid Quality teams, so the books are going in print. I was part of both teams, as the lead author of the first one and coauthor of the second one. The books are actually training kits for two SQL Server 2005 exams:
Although SQL Server 2005 RTM is around for more than half a year and has already service pack 1, I still get many questions about the new features it brings. People try to realize why should they upgrade their current SQL Server 2000 installations or why should they migrate from other systems. Therefore, I decided to write couple of blogs with brief explanation of major novelties together with an evaluation of usefulness of them. I have to warn you: everything I write here is strictly my personal point of view. Still, I am going to corroborate my opinions with reasons why I think this way. In addition, I am not going to talk about cool features exclusively; I intend to mention what I dislike as well.
When I think of improvements in the relational database engine in SQL Server 2005, the first thing that comes in my mind is schemas. SQL Server 2005 finally makes the owner-schema separation. Remember that in previous versions object owner was used in place of schema. In a database with hundreds of objects, developers always wanted to group objects into meaningful groups, i.e. namespaces. Nevertheless, with different owners you can easily get into troubles with broken ownership chains. Now, with properly implemented schemas, you can develop more natural data models. You should also not forget that permissions are now hierarchical.
SQL Server Integration Services (SSIS) is an Extract-Transform-Load (ETL) tool. It is probably not the best tool in the market, but if you compare it to the SQL Server 2000 Data Transformation Services (DTS), it is a big step forward. Of course, you still get it free with a valid SQL Server 2005 license.
How can you evaluate an ETL tool? Well, you can program the complete ETL process manually, with the programming language of your choice. You create procedures that do the transformations on the data. A good ETL tool should have many such procedures prepared in advance, with an easy user interface that allows you to set up their parameters and thus program the ETL process graphically. Of course, the ETL application you create should perform well, because typically you have a limited time window for your ETL process.
Many database administrators and developers feel uncomfortable with all of the XML support built in the SQL Server 2005 Database Engine. I count myself between database and not application developers; still, I am very impressed with the XML integration.
No matter whether you like it o not, XML is nowadays de facto standard in applications. Database management system simply has to deal with it. Developers already search for solutions without a relational database, because they find relational model too restrictive. Object and XML databases are evolving. Nevertheless, does all of this make the relational model obsolete?
There is enormous number of enhancements in the Transact-SQL language in SQL Server 2005. I already mentioned all of the new and improved language elements for handling the XML data. You can use string functions for large objects if you use new varchar(max), nvarchar(max) or varbinary(max) data types.
A very effective way of administering Windows is through scripts in a scripting language that use Windows Management Instrumentation (WMI) objects. SQL Server 2005 brings WMI provider for configuration management and WMI provider for server events. You can use the first one for configuring SQL Server through a script, and the second one to monitor events on SQL Server through a script. Here is an example of VBScript script that uses WMI to enlist the protocols and enable named pipes.
The term “Unified Dimensional Modeling” comes from the fact that meta-data in SSAS 2005 is much richer than it was in AS 2005; it is actually rich enough to support the complete data warehouse schema in a single cube. The AdventureWorksDW demo project shows this concept: a single cube includes multiple star & snowflake schemas representing a complete DW.