SSIS offers so much more than DTS. Rather than view it as the next version of DTS, I view it instead as version 1 of a new product.
During the process of migrating to SQL Server 2005, many development organizations will still be forced to support SQL Server 2000 installations. As a result, a common requirement is the ability to reverse-migrate: move databases back from SQL Server 2005 to SQL Server 2000.
Unfortunately, this is not as easy. Backed-up databases from SQL Server 2005 instances cannot be restored in SQL Server 2000 instances. Nor can detached SQL Server 2005 databases be reattached to SQL Server 2000.
Because backup/restore and detach/attach are not supported, the only methods available for moving data from SQL Server 2005 installations to SQL Server 2000 installations are based on copying data. Data and schemas can be scripted using a tool such as Red Gate's SQL Packager and scripts can be applied on the SQL Server 2000 installations. Another option is to use tools such as DTS or SSIS, both of which include data copying wizards.
However, copying data and schema is a potentially troublesome experience. SQL Server 2005 supports many features that are not present in SQL Server 2000 and can cause problems during cross-migration. It's important to be cautious when attempting to develop solutions for both platforms, and my recommendation is that development should be done using SQL Server 2000 and the code and data migrated forward to SQL Server 2005, rather than the other way around.
What is the relationship between SQL Service Broker (SSB) and Microsoft Message Queuing (MSMQ)?
SQL Server 2005 introduces a native, queued messaging system, SQL Service Broker. By using Service Broker, developers will be able to create asynchronous and pipelined database applications, without relying on application code for queuing capabilities.
Developers should understand that SQL Service Broker and Microsoft Message Queuing are very different technologies. Although they are both queuing systems, MSMQ is not natively supported in SQL Server, but is instead implemented using .NET or COM code. MSMQ can send and receive messages between a variety of application platforms.
SSB queues, on the other hand, can be implemented entirely in T-SQL, without the need for external application code. Service Broker, unlike MSMQ, can only send messages to and receive messages from SQL Server—either local or remote databases.
Each of these technologies is useful for different purposes, but perhaps the most important distinction is that SQL Service Broker allows data-driven queues to be implemented without the use of external application code—keeping data logic close to the data, where it belongs.
SQL Server 2005 developers that need to access MSMQ from within SQL Server can use CLR routines that make use of the System.Messaging namespace.
Native support for .NET routines in SQL Server is an important enhancement to the product, but one that brings many concerns. One such worry is security: Can a DBA ensure that an assembly will not try to access resources that it should not? For instance, what if a third-party library is purchased?
The solution to these problems is permission sets, which are named collections of access rights that can be assigned to an assembly. If an assembly tries to use a namespace or class not allowed by the permission set to which it belongs, an exception will be thrown and execution will stop. Through careful use of appropriate permission sets, a DBA can enforce access rights, even for non-trusted assemblies.
SQL Server includes three built-in permission sets: SAFE, EXTERNAL ACCESS, and UNSAFE. SAFE allows access to .NET's mathematical, string manipulation, and other standard libraries, as well as ADO.NET for data access. EXTERNAL ACCESS extends the rights allowed by the SAFE permission set, adding classes from namespaces such as System.IO for file system operations, and System.Net for network operations. The UNSAFE permission set is unrestricted—all namespaces and classes available within SQL Server can be accessed, and even unmanaged code is allowed. Be careful with the UNSAFE permission set!
Although the default permission sets are not editable, it is possible to create your own, as discussed in Niels Berglund's this blog post.
SQL Server has supported triggers for several versions. These "normal" triggers are known as DML (Data Manipulation Language) triggers, and fire when data modifications occur as the result of INSERT, UPDATE, or DELETE statements.
SQL Server 2005 adds a new type of trigger, DDL (Data Definition Language) triggers. These triggers fire when database or server metadata modifications occur as the result of CREATE, ALTER, or DROP statements. For instance, a DDL trigger can be created that will fire every time a user is added to a database, or every time a login is added to the server.
There are a few important distinctions that must be drawn between DDL triggers and DML triggers. First of all, DML triggers are created on tables, at the database level. DDL triggers, on the other hand, can be created at either the database or the server level. This allows DDL triggers to be created to capture server events such as ALTER LOGIN or ALTER DATABASE, in addition to database-level events such as ALTER TABLE or ALTER PROCEDURE.
The second major distinction is that only AFTER DDL triggers are supported. SQL Server 2000 supports INSTEAD OF DML triggers that fire instead of the data modification instead of afterwards. This is especially important because one of the major use cases for DDL triggers is restricting certain operations; for instance, you might have a DDL trigger that rolls back any ALTER TABLE operations for certain tables. But because the trigger fires after the operation completes, the entire operation will have to be rolled back—a potentially expensive process. This is something to watch out for, but in the end it's a small price for the functionality afforded by DDL triggers.
The final difference is that DDL triggers, unlike DML triggers, do not make use of the inserted and deleted virtual tables. Instead, you'll use the EVENTDATA function to get information about what event caused the trigger to fire. For more information about EVENTDATA, consult SQL Server 2005 Books Online.
Event Notifications is a feature similar to DDL triggers but with slightly different capabilities. The most important difference is that Event Notifications uses SQL Service Broker to asynchronously deliver messages, rather than working in the scope of the transaction, as do DDL triggers. This means that whether or not the transaction completes, the Event Notification will still be delivered. Likewise, it means that Event Notifications have no control over the transaction, and cannot be used to restrict operations—no rollback is possible within a notification.
Like DDL triggers, Event Notifications can fire on database or server-level DDL operations. However, Event Notifications also has the capability of firing on trace events. For instance, an Event Notification can be created that will fire any time a deadlock event occurs. This capability offers a lot of flexibility for capturing server events, which was only possible via traces previously.
Also similar to DDL triggers, Event Notifications uses the EVENTDATA function for programmatic access to information about the event that fired the notification.
Event Notifications can be used instead of DDL triggers whenever asynchronous activities make more sense than transaction control. For instance, if you need to log a certain event, but do not need the ability to roll back the transaction if something isn't correct, Event Notifications are probably a better choice.
The SQL Native Client is a new OLE DB and ODBC API that provides data access providers with new functionality and datatypes provided by SQL Server 2005. By using SQL Native Client, applications can make use of features such as Multiple Active Result Sets (MARS), SQL Server 2005's XML datatype and CLR user- defined datatypes, and SQL Service Broker.
Developers using .NET languages such as C# or VB.NET in conjunction with ADO.NET will not need to worry about the SQL Native Client—all of the features it exposes are present in the ADO.NET classes. However, developers programming COM-based applications that need to access SQL Server 2005 databases may wish to expand their functionality by using the SQL Native Client for data access.
SQL Server's Query Optimizer has the ability to auto parameterize certain types of queries in order to prevent recompilation. If a predicate uses a literal instead of a parameter, SQL Server can internally optimize the query as if the literal were a parameter, thereby producing a reusable execution plan. However, SQL Server's default simple parameterization can only auto-parameterize certain types of queries. Complex queries may not be parameterized, and therefore the database may be doing a lot more work than necessary recompiling query plans. This is especially prevalent in systems that make much use of ad -hoc T-SQL.
In order to help with this problem, SQL Server 2005 offers a new database setting called forced parameterization. This setting enables a more robust form of auto parameterization that can parameterize a much larger variety of queries, thereby reducing the potential for recompilation if similar queries are repeatedly used. This feature should only be enabled if the number of recompilations in a database is especially high, even though many similar queries are being executed. Forced parameterization has a higher overhead than simple parameterization, and so may cause performance problems rather than fixing them if used in the wrong circumstances.
This feature can be enabled by using ALTER DATABASE and setting the PARAMETERIZATION option to FORCED. The option can be set back to SIMPLE in order to return to the default behavior.
SQL Server's 8060-byte row limit has been quite a thorn in the side of developers working with the DBMS. In SQL Server 7 and 2000, the only way to get around this limit was to either use large object types (TEXT/NTEXT/IMAGE) or to split up data into multiple tables—neither option very friendly.
SQL Server 2005 introduces an important feature called row overflow, which greatly relaxes the 8060-byte restriction. In short: It is now possible to insert rows with more than 8060 bytes of data, as long as the columns that cause the row size to exceed 8060 bytes are of varying types. Should a row exceed 8060 bytes, the data for varying columns (typed as VARCHAR, NVARCHAR, or VARBINARY) will be automatically moved off-row into the large object area, rather than terminating the data modification.
Note that table size is still limited by fixed-length types, as well as internal headers and row pointers. This means that you still cannot create a table with a row size of greater than 8060 bytes if that table is comprised of all numeric columns, for instance. In addition, each column that is moved off-row will occupy a 24-byte on-row pointer. You must make sure when creating large tables that the pointers will not cause rows to overflow the 8060-byte limit, or data modifications may be terminated as a result of too much data in row.
As one of SQL Server 2005's most exciting high availability features, the announcement that Database Mirroring would not be enabled in the RTM version of the product was quite a shock to the user community. Many rumors surfaced, including the assertion that the feature was completely removed from the product.
Database Mirroring was not removed from SQL Server 2005, and as a matter of fact is still in the product as well as the documentation. However, the feature must be explicitly turned on before it can be used, and is supported for development purposes only—use in production environments is only allowed with prior Microsoft approval. The feature was delayed in order to ensure its performance and stability through more extensive testing than was possible during the development cycle. It will be made available for general use during the first half of 2006.
In the meantime, DBAs can enable this feature by using startup trace flag 1400. See SQL Server Books Online for more information.
SQLCMD is the SQL Server 2005's command-line query tool. It replaces the older isql and osql command-line tools, both of which are deprecated. isql is no longer shipped with SQL Server, and osql is scheduled for future removal from the product. Organizations using ISQL in SQL Server 7 or 2000 should switch to osql before upgrading to SQL Server 2005. After upgrading, try to switch to SQLCMD as soon as possible in order to avoid future problems.
SQLCMD provides a large number of scripting options that allow for a great amount of flexibility for dynamically running scripts. For instance, scripting variables can be embedded in scripts, which can be used as substitution parameters at runtime, thereby allowing generic database scripts to be written for large installations.
There is not enough space here to properly cover this tool; please consult SQL Server 2005 Books Online for a complete rundown.
|ABOUT OUR EXPERT:|
Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has
implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer
performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of
Pro SQL Server 2005, published by Apress.|
Didn't find what you're looking for? Pose a question to our SQL Server experts.
This was first published in December 2005