SQL Server Replication is a suite of tools that allows you to move data automatically from one server to another. There are three distinct types of replication available within Microsoft SQL Server.
Transactional replication—Information is moved from one server to the other as the transactions occur. This is a good choice for large databases because once it is set up and an initial snapshot is applied, only the changes to data are moved between the servers. It is also a good choice if you want to move reporting off the primary server, but still require up-to-the-moment data. Since the primary server must be able to exactly identify the rows of data to change on the target server, it requires primary keys on the tables to eliminate any possible ambiguity.
Transactional Replication with updatable subscriptions – This is similar to Transactional Replication but updates are allowed to subscriptions. This may be an alternative to Merge Replication in some cases. There are many requirements for this type of replication as far as changes that are made to tables, allowable data types, and the use of foreign key constraints among others. Xtivia can help you determine if this type of replication would work for your organization.
Snapshot replication—An entire copy of the data to be replicated is moved to the target server. This is a good choice for smaller databases where it is acceptable for the data to be perhaps a day old. Since Snapshot replication is copying entire tables, it doesn’t need to have primary keys on tables. When it’s impossible to modify table structures, Snapshot replication may be the best way to replicate.
Merge replication—Merge replication allows users on the target system to modify data and have that data “merged” back onto the primary source. This is commonly used in applications where someone—perhaps a salesperson—needs to download information, make updates to that information while disconnected from the primary database and then apply the updates to the primary when reconnected. Because changes could occur to the same record on both the primary and target machines, this type of replication requires some rules regarding usage.
Why You Should Consider SQL Server Replication
You want to offload reporting to a separate server: Replication can reduce the stress on your production environment caused by user reports. Replication allows your users to run their reports against a replicated copy of your database. This copy can be near real-time (transactional replication) or if there isn’t a need for real-time a copy of production data can be moved to a reporting server off-hours (snapshot replication). In either case it frees up your production server for the important work of serving your customers.
You want to distribute data: Data distribution allows you to make critical data available to local decision makers while maintaining firm central control over the data. With replication, data can be shared and replicated between databases allowing you to put the data where it's needed.
You want to augment your backup strategy: In conjunction with your routine backup, replication provides alternative levels of data protection and integrity, while minimizing user disruptions. Replication creates a point-in-time copy of the data to be used as the backup source.
You want to create a fail-over system: Replication maintains a near real-time “warm standby” database to which applications can switch with virtually no downtime if the primary site fails. You can manage planned downtime like routine maintenance and software upgrades. It protects during unplanned downtime due to machine or network outage. It also provides disaster recovery. Any system providing HA services should provide continuous availability of data in any scenario.
You want to replicate data from Oracle to SQL Server: SQL Server Enterprise edition supports heterogeneous replication from Oracle to SQL Server and from SQL Server to Oracle. Xtivia has experts in SQL Server replication as well as Oracle replication that can make heterogeneous replication work for you.
What You Can Expect from Xtivia
Xtivia’s certified SQL Server engineers will provide recommendations and strategies to set up a replication server to meet your organization’s needs. Xtivia also offers a Virtual-DBA system that monitors your replication server activities.
Xtivia delivers:
Initial assessments and review requirements
Replication system design
Approach and plan details
Schedule and cost estimates
Installation
Testing and validation
Training to administer replication server
Replication server monitoring using Virtual-DBA when requested
