SQL Server replication has become a core infrastructure component for many organizations and a mainstay that allows DBAs to quickly and easily move data. Unfortunately, replication has not been given its fair share of attention, compared to many other new SQL Server 2005 enhancements. In this tip, we will outline some of the new SQL Server replication features.
SQL Server 2005 replication enhancements
1. Replication security enhancements
You will be glad to know that SQL Server 2005 has made great strides in securing replication. In SQL Server 2000, replication ran as the SQL Server Agent login. This is not the case with SQL Server 2005. In SQL Server 2005, replication enhancements include a separate Windows login that you can set up for each job. The Windows login/user can be assigned only the needed rights to complete the job. This offers a great deal of flexibility and tighter controls to data, which is a big win for protecting sensitive data.
2. Simplified replication password changes
More security flexibility usually equals more headaches when changes are required, especially password changes. With SQL Server 2005, it is possible to change the replication password in one place for a single SQL Server. This is achieved in Management Studio by navigating to the Replication folder then right clicking on the folder to select the "Update Replication Passwords" option. Once the Update Replication Passwords GUI loads, select the account
3. Snapshot delivery auto restart
To start replicating data, a snapshot of the database on the publisher is taken. This is typically the single most resource-intensive aspect of replication because the entire database is moved across the network. In SQL Server 2000, this process became even more resource intensive when the snapshot failed and had to be reissued. The process has been improved with SQL Server 2005's replication ability to automatically resume the process from the failure point without having to resend the completed data. This is a big win for time, error handling and network bandwidth. To learn more about this feature and similar features, visit Replication availability enhancements.
4. Oracle publishing configured in SQL Server (really?)
Yep, you are reading that correctly. You can configure SQL Server 2005 snapshot and transactional replication from Oracle 8.0.5 or later to SQL Server. Can you believe it? The executables, distributor, SQL Server agent jobs, etc., remain in the SQL Server environment with some tracking tables and triggers created in the Oracle environment to support the data collection and replication processes.
5. All SQL Server 2005 data types supported
BLOB data types were not supported in SQL Server 2000. These data types as well as the new varchar(max) and varbinary(max) data types are supported in SQL Server 2005. Replication supports these data types by setting up triggers. Although triggers are needed, it appears as if no custom programming is required during the configuration process.
6. Introducing Replication Management Objects
Programming with Replication Management Objects (RMO) fully supports SQL Server 2005 replication features, which replace the earlier Distributed Management Objects (SQL-DMO) that shipped with SQL Server 2000. RMO is backward-compatible with SQL Server 7.0 and 2000, so this single model should be able to support all of the replication needs.
Discontinued replication features
Some SQL Server 2000 replication features were discontinued in SQL Server 2005, and others are marked to be removed from future releases. It is critical to review these items; they will cause issues immediately in your environment and then begin to change code. For example, no ActiveX scripts are supported in SQL Server 2005. To maintain the functionality, one option is to call an assembly with the SnapshotGenerationAgent Class.
To see how your applications will fair with the SQL Server 2005 replication changes, reference the following URLs:
- Deprecated features in SQL Server 2005 replication
- Discontinued functionality in SQL Server 2005 replication
- Behavior changes in SQL Server 2005 replication
- Breaking changes in SQL Server 2005 replication
For additional information on SQL Server 2005 replication enhancements and functionality, visit the following Web pages:
- Category listing of SQL Server 2005 replication enhancements
- Category listing of what's new in SQL Server 2005
As you are upgrading or deploying SQL Server 2005 at your organization, leverage its new replication features to benefit the organization. Be aware that some of the replication configurations, parameters, models, etc., have changed and the corresponding code must be changed as well to fully support SQL Server 2005.
About the author: Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Kadlec is also the SearchSQLServer.com Performance Tuning expert. Visit Ask the Expert to pose a question to him.
This was first published in July 2006