No additional configuration is required for this chapter. in Visual Basic .NET

Creator QR Code 2d barcode in Visual Basic .NET No additional configuration is required for this chapter.

10

Ian McLean Because replication is relatively straightforward to configure, a tendency exists to use it for almost all data transfers and for disaster recovery. Suppose, for example, you are replicating a database between two servers with a replication latency measured in minutes and you back up the database every night and the transaction logs every two hours. If the database on one of the servers becomes corrupt but the second server is unaffected, the fastest and easiest way to recover probably is to back up the database on the second server and restore it to the first. However, replication uses resources and is not typically the best disaster recovery solution. You should use replication to synchronize changes to remote databases with a central database, to create multiple instances of a database and distribute the workload, to distribute data that updates on a regular basis from a central location to other servers, and to customize data and distribute it to other Subscribers and sometimes as part of a failover strategy. When I first came across database mirroring, it was disabled by default, and Microsoft advised users to enable it only for validation on test networks. I validated database mirroring on my own test network and decided that if I needed to provide fault tolerance for a database and ensure that client computers can connect to the database even if the SQL Server 2005 computer experiences a complete hardware failure with minimal administrative effort and disruption to the user, I would mirror the database on a second computer just as soon as Microsoft told me it was OK to do so. So when SP1 was released, I installed the service pack on my test network, checked it out, checked that database mirroring still worked OK, and then installed SP1 and implemented database mirroring on a production network. I suppose one moral of this story is that test networks are invaluable. However, the main point is that replication has its place, as has clustering, backup and restore, log shipping, and database mirroring. I discuss the use of replication with database mirroring later in this chapter. Each tool has its specific purpose, though these tools might overlap. No one tool fits all.

A database administrator (DBA) needs to know how to configure replication. However, a number of high-level tasks need to be tackled before you start your configuration. You need to formulate the replication strategy and design the replication model that is appropriate to your enterprise. You need to decide what you are going to replicate, when you are going to replicate, and which servers you intend to use to distribute or receive data (or both). Nor is your job finished when you have implemented your replication plan and checked that it works. You also need a plan to monitor replication and ensure that it continues to operate at the efficiency levels you first specified. You need to configure alerts and warnings that let you know about any problems long before they become apparent to your users. You need to configure your replication agents to work at maximum efficiency by generating and configuring agent profiles. Tuning replication configuration is a continuous process, and you can always find a means of significantly improving efficiency if you look hard enough. In this chapter, you learn how to design strategies to configure alerts, monitor replication health, monitor latency and failures, verify and tune replication, resolve conflicts, and configure agent profiles. The chapter also discusses database mirroring and how this new feature works with replication.

The service pack level at the time of this writing is Service Pack 1 (SP1). Unless otherwise indicated, all the information in the chapter applies to both SQL Server 2005 and SQL Server 2005 SP1.

After this lesson, you will be able to:

List the various types of replication and replication topologies that SQL Server 2005 uses and select the appropriate type for a given scenario. Design an alert strategy, and configure replication alerts. Design a maintenance strategy that monitors the replication process and reports on latency and failures. Verify replication by using tracer tokens and checksum. Resolve replication conflicts, modify agent profiles, and tune replication configuration. Discuss the advantages of using replication with database mirroring.