SQL Server Replication

SQL Server Replication

technology

​​​​​

Database replication is all about distributing data. There are many common scenarios where replication may make sense, whether it is to enable a mobile workforce, to allow intensive reporting or data mining without interfering with day-to-day operations, or to improve data availability.

At the most basic level, changes made to one database (the publisher) are distributed to one or more targets (the subscribers). This way, you can distribute data to different locations and to remote users over local networks or the Internet.

The first step in deciding whether replication is a good fit for your organization is to consider the business problems that need to be solved. Microsoft breaks these down into two broad categories: “server to server” and “server and client” (where a client may be a workstation, tablet, laptop, mobile device, etc.):

Server to Server Scenarios

Scenario

Description

Improving scalability and availability

By automatically writing data to multiple servers in near-real time, you can achieve a double benefit:

  • redundancy in the case of downtime
  • the ability to distribute a “read” workload across multiple servers

In terms of availability, replication is just one of several available technologies. It should be noted that replication is not a replacement for a proper backup strategy!

Data warehousing and reporting

Replication can be used to move data from your database to another server where it is then processed for use in reporting, dashboards or other business intelligence systems.

Integrating data from multiple sites

Data is often "rolled up" from remote offices and consolidated at a central office. Similarly, data can be replicated out to remote offices.

Integrating heterogeneous data

It is not uncommon for organizations to store data in databases from multiple vendors, perhaps even running on different operating systems. In addition to other integration technologies (such as Integration Services), SQL Server can support data replication with certain Oracle and IBM (DB2) databases.

Offloading batch processing

Certain batch operations may have a lot of overhead that could interfere with day-to-day operations if run on the main server. Offloading this means that the processing can occur on another server, and the (relatively small) result can be passed back to the main server.

Server and Client Scenarios

Scenario

Description

Exchanging data with mobile users

Many applications require data to be available to remote users, including sales people, delivery drivers, and so on. These applications include customer relationship management (CRM), sales force automation (SFA), and field force automation (FFA) applications.

Consumer point of sale (POS) applications

POS applications, such as checkout terminals and ATM machines, require data to be replicated from remote sites to a central site.

Integrating data from multiple sites

Applications often integrate data from multiple sites. For example an application that supports regional offices might require data to flow in one or both directions between regional offices and a central office.

Topologies

Once you’ve determined that replication is a good fit, the next step is to determine what topology to use in your scenario.

Microsoft SQL Server 2008 R2 provides several different topologies when it comes to replication. Selecting the appropriate type of replication depends on many factors including the physical environment, the amount of data to be replicated, and whether the data can be updated at the subscribers (and, therefore, conflicts are possible).

Replication typically begins with an initial synchronization of the published objects between the Publisher and the Subscribers – a “snapshot” is created, which is a copy of all of the objects and data specified as part of the publication. After this initial synchronization, what happens depends on the type of topology you’ve chosen.

This TechNet article gives a good overview of the suitability of each kind of replication:

Replication Type

Is Appropriate When

Is Often Used

Snapshot Replication

  • Data changes infrequently.
  • It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
  • Replicating small volumes of data.
  • A large volume of changes occurs over a short period of time.

Server to Server

Transactional Replication

  • You want incremental changes to be propagated to Subscribers as they occur.
  • The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
  • The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
  • The Publisher has a very high volume of insert, update, and delete activity.
  • The Publisher or Subscriber is a non-SQL Server database, such as Oracle.

Server to Server

Merge Replication

  • Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.
  • Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
  • Each Subscriber requires a different partition of data.
  • Conflicts might occur and, when they do, you need the ability to detect and resolve them.
  • The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row changes only once at the Publisher to reflect the net data change (that is, the fifth value).

Server and Client

SQL Server Editions

Once you’ve determined the topology that makes sense for your scenario, you need to ensure that you are using an appropriate edition of SQL Server:

Feature

Enterprise

Standard

Web

Workgroup

Express

Snapshot replication

Yes

Yes

Subscriber only

Yes

Subscriber only

Merge replication

Yes

Yes

Subscriber only

Up to 25 as Publisher

Subscriber only

Transactional replication

Yes

Yes

Subscriber only

Up to 5 as Publisher

Subscriber only

Publishing data from SQL to non-SQL subscribers

Yes

Yes

No

No

No

Publishing data from Oracle to SQL

Yes

No

No

No

No

 Recent News

select to filter posts

Gamification: Coming to a workplace near you!

by Scott Johnston

Video games aren't just a wave of the future; they're the current reality for millions of players who take to their consoles and PCs every day.

read more

Managing Voter Experience

by Jim Ward

I had the chance to run a political campaign for a friend in the last civic election in Edmonton. A terrific experience, and full of surprises. But how could a computer guy possibly be qualified to run a campaign? If you’ve never been involved in one, ...

read more

Red Adair Rings True In IT

by Kerry Wiebe - Director of Business Development

“If you think it's expensive to hire a professional to do the job, wait until you hire an amateur.” - Red Adair This quote resonated with me when I recently took on a do-it-yourself (DIY) renovation project at my home. I decided to do the project ...

read more

My History with Umbraco

by Terence Jee

Umbraco is an open source, free CMS that allows users to download it and use for their own needs.

read more

Prototyping with Twitter Bootstrap

by Davis Levine

Front end frameworks can be powerful tools in a designer's development process. It can save a ton of time in the project process, and with most frameworks employing a fluid grid system, scaling to different devices becomes less of a headache.

read more

Knowledge of Possibilities

by Ammneh Azeim

Recently, I read a great blog post by Daniel Burrus which states “Give your customers the ability to do what they can’t currently do but would want to if they only knew it was possible.” The key here is knowledge of possibilities and knowledge which comes

read more

Safety Codes Council Adopts Microsoft Dynamics CRM

by Andrew Slessor

Recently, the Alberta Safety Codes Council adopted Microsoft Dynamics CRM as their platform for the Master Electrician Management System.

read more

Legal Aid Alberta Implements Microsoft Dynamics® CRM

by Andrew Slessor

Legal Aid Alberta Improves Client, Case and Financial Management with Microsoft Dynamics® CRM Legal Aid Alberta (LAA) provides cost-effective services to 200,000 Albertans in need each year.

read more

World Usability Day

by iomer

​​ This Thursday, November 7 is World Usability Day, a day dedicated to making the world a more user friendly place.

read more

PanEx 2011 Unites Developers and UX Professionals

by iomer

Last night I attended PanEx 2011 organized by UX YEG, Edmonton's User Experience community group.

read more

Committed to Disruption

by Jim Ward

​ One of the things I love about our industry is the counterintuitive truths that make themselves evident on a regular basis.

read more

SharePoint 2010 Data Access with LINQ to SharePoint

by Anthony Haxby

In SharePoint 2007 a popular and (relatively) straight-forward method of querying SharePoint Foundation data from lists was using the SPQuery class and involved writing the query using the Collaborative Application Markup Language (CAML).

read more

Case Study: Human Security Report Project

by iomer

The Human Security Report Project (HSRP) is an independent research centre affiliated with Simon Fraser University’s School for International Studies in Vancouver, Canada.

read more

Exciting new features in Dynamics CRM 2011

by Andrew Slessor

​​​​ We have been implementing Microsoft Dynamics CRM 2011 for a few months now and I am excited with many of the new features that have been added to the system.

read more

Bringing Teams Together: Calendar Overlays in SharePoint 2010

by iomer

Calendar aggregation is a topic that will come up time and time again when discussing desired functionality for a collaborative environment.

read more

An Opportunity to Pause

by Jim Ward

I was at the Microsoft Canadian Leadership Summit in Redmond last week. It's a great event for learning about future trends and technologies, as well as considering and applying the lessons presented by Microsoft experts and futurists.

read more

SQL Server Replication

by Mike Hebert - solution architect

Database replication is all about distributing data. There are many common scenarios where replication may make sense, whether it is to enable a mobile workforce, to allow intensive reporting or data mining without interfering with day-to-day operations,

read more

Stop Chasing Butterflies

by Jim Ward

Had some great learnings last week. Picture this. You've put your strategic plan together, and your team is busy doing the right things.

read more

The Coffee Still Matters

by Jim Ward - Founder

Former Starbucks CEO Howard Behar penned a wonderful book on leadership called “It’s Not about the Coffee”, based on his experiences building the Starbucks brand.

read more

Top Ten Reasons for Collaborative Governance

by Andrew Slessor

Collaborative Governance is the principal of sharing ownership of the intranet with various groups or individuals in an organization. Instead of no owner, or a single one, a collaborative team governs through the application of policies, standards and tem

read more

Website Sustainability

by iomer

​Website sustainability may sound like a complex topic but the concepts are quite straightforward and are fundamental to successful website development and growth.

read more

Diagnostic Logging

by Steven Sirockman - solution manager

As a SharePoint Technology Specialist, I have had many phone calls from clients asking me to come in and troubleshoot their SharePoint Server environment. One of the first questions I ask them is if they have configured diagnostic logging.

read more

Intranet Users Roles and Responsibilities

by Andrew Slessor

In order to manage a sucessful intranet, users must be certain of what tasks they are responsible for on the site. The roles for intranet users can be seperated into seven categories; these are described below.

read more

Snippet Compiler versus LINQPad

by Mike Hebert

Jeff Key’s Snippet Compiler is a popular developer tool that has been around for years – I first learned about it in a 2004 MSDN article titled “Ten Must-Have Tools Every Developer Should Download Now”.

read more

Dashboarding SQL Performance Counters

by Gord Barker - solution architect

In a previous blog I wrote about benchmarking SQL performance. This is important for those rare occasions when someone says the system is not running fast enough.

read more

3 Tools for Clearer, More Effective Writing

by Kael Nicholson

Need to convey a wealth of information in a tiny space? When working in the technical world, writers are constantly torn between the need to accurately describe complex ideas and the desire to make them clear and accessible.

read more

Integrating AJAX and Telerik with SharePoint

by Gord Barker

​To extend your SharePoint site to include Ajax, you will need to perform a few steps

read more

Logging SQL Performance Statistics

by Gord Barker - solution architect

One of the most important tools in analyzing a SQL Server performance problem is a good understanding of the normal behaviour your system exhibits.

read more

Five Things I'm Looking Forward to in SharePoint 2010

by iomer

Microsoft has released some Sneak Peek videos giving a preview of what we can expect to see in SharePoint 2010.

read more

SharePoint Application Performance

by iomer

Recently we were approached by a customer who was having performance issues with a custom application built on top of SharePoint.

read more

A Program to add files to a Zip file

by Gord Barker - solution architect

I recently had to retrieve 45 days worth of Air Quality data from a web site. I used WGET to retrieve 35000 tiny text files to a sub directory.

read more

The Top 10 SQL Scripts You Must Have

by Gord Barker - solution architect

Here is a collection of important SQL Script gathered from around the net (including the Steve and Chris Show on SSWUG). These are scripts you should know about or have in your toolkit.

read more

Edmonton Technology Community

by Christina Gray

As technology continues to advance, keeping up and prioritizing what you ‘need to know’ becomes more of a challenge.

read more