Microsoft SQL Server Replication

What is Microsoft SQL Server Replication?

Microsoft SQL Server Replication is a set of tools that allow data to be automatically moved from one server to another. There are three distinct type of replication available within Microsoft SQL Server:

• Transactional Replication – In this type of 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 it only moves the changes to data between the servers. It is a good choice for a situation where 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 – In 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 Sales Rep, needs to download information, make updates to that information while disconnected from the primary database 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 rules be created to resolve conflicts. Merge replication also requires that a GUID (Globally Unique Identifier) field be added to each table that will be replicated.

Who Uses Microsoft SQL Server Replication and Why?

A business organization that needs to offload reporting to a separate server

Microsoft SQL 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.

A business organization that wants to build distributed system

Data distribution is a tool that helps companies put necessary data in the hands of local decision-makers yet maintain firm central control over the data. With Microsoft SQL Server replication, data can be shared and replicated between databases, allowing system designers to put the data where it's needed.

A business organization that wants to have additional backup strategy

In conjunction with backup, Microsoft SQL Server replication strategies seek to complement traditional approaches by providing 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.

A business organization who wants to set up failover system

Mircrosoft SQL Server 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 such as routine maintenance, software upgrades and etc. It protect during unplanned downtime due to machine/network outage, for example. It also provides disaster recovery. Any system providing HA services should provide continuous availability of data in any scenarios.

A business organization that needs 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 we provide

Xtivia’s Engineers will provide recommendations and strategies to set up Microsoft SQL Server replication to meet your organizational objectives. Xtivia also provides VDBA system that monitors your replication server activities. We deliver:

• Initial assessments and review requirements
• Design replication system
• Define the approach & plan
• Estimate schedule & cost
• Perform installation
• Test and validate
• Training to administer replication server
• Monitor replication server using Virtual-DBA when requested

For more information on how Xtivia can help your organization with replication server needs, please contact an office near you or visit our web site at www.Xtivia.com.