MS SQL SERVER DBA

Replication needs should be clearly defined before creating a replication topology. Successful replication can be difficult and requires much pre-planning. Ideally, publishers, distributors, and subscribers should be on separate physical hardware. Create, document, and test a backup and restore strategy. Restoring replicated databases can be complex and requires much planning and practice. Script the replication topology as part of your disaster recovery plan so you can easily recreate your replication topology if needed. Use default replication settings, unless you can ensure that a non-default setting will actually improve replication performance or other issues. Be sure that you test all changes to ensure that they are as effective as you expect. Fully understand the implications of adding or dropping articles, changing publication properties, and changing schema on published datab...

            Replication is a way of keeping data synchronized in multiple databases. Implementing and maintaining replication might not be a simple proposition: If you have numerous database servers that need to be involved in various types of replication, a simple task can quickly become complex. Implementing replication can also be complicated by the application architecture.  Microsoft SQL Server has supported replication since version 6.0, and setting up replication has become significantly easier over the years (in fact, 99 percent of replication setup can be accomplished by clicking through replication wizards). However, replication involves much more than setup, and unfortunately there aren't many sources of information for implementing and troubleshooting it. The only way to learn replication is to dig through the knowledge base articles and figure things out on your own....

The principal database and the mirror database should be on separate physical hardware, and ideally, in different physical locations. The witness server should be on separate physical hardware, and be on a separate network (best if at a third location). Initial database mirroring setup should be done during less busy times, as the setup process can negatively affect performance of the production database being mirrored. Use high availability mode whenever possible, and high performance mode only when required. The hardware, along with the OS and SQL Server configuration, should be identical (at least very similar) between the two servers. While a fast connection is not required between mirrored servers, the faster the connection, and the better quality the connection, the better. You will want to optimize the performance of the mirrored database as much as po...

Database states for each server are kept during the database mirroring session, recorded on each partner server, and reported by the sys.database_mirroring catalog view. The mirroring_state column returns a number for the state, and the mirroring_state_desc column returns the descriptive name for the state. State information about the witness is also reported from the same catalog view. In addition to the states reported for each database, there are three phrases that are useful in describing the servers and databases involved in database mirroring. 1. Exposed - The data on the principal is exposed when it is processing transactions but no log data is being sent to the mirror. When a principal database is exposed, it is active with user connections and processing transactions. However, no log records are being sent to the mirror database, and if the principal should fail, the mirror will not have any of the transactions from the pr...