Accidental Technologist

Musings about Entrepreneurship, Technology and Software Development

  • Home
  • About
  • Still River Software
  • Privacy Policy

Powered by Genesis

You are here: Home / SQL Server / Microsoft SQL Server 2005 Replication Automation Resources for SQL Management Objects (SMO)

Microsoft SQL Server 2005 Replication Automation Resources for SQL Management Objects (SMO)

August 22, 2007 by Rob Bazinet

Tweet
Background

I have recently been involved in a project which makes heavy use of Microsoft SQL Server 2005.  We have many database servers all over the company including several clusters with locations all over the world.

My role on the project is to be able to synchronize all of the databases across the company so each database has the same information in it as close to real-time as we can.  I decided to use SQL Replication Services in a transactional push configuration.    I used this method in a test environment and found the performance to be very good and the results very reliable.

Setting up SQL Server Replication is pretty straight forward using SQL Management Studio as the wizards guide you through the entire configuration.  I had about 100 tables that needed to be replicated out of about 250 tables, selecting them was where it became a bit time consuming but setup was still easy.

The Challenge

The replication solution was to run in a development environment during the later parts of system development so we could see the behavior of replication from both a performance and latency standpoint.  The results were extremely fast even across the Atlantic Ocean, we are talking milliseconds here for changes to data.

The challenge we faced came about when we needed to make schema changes on the server who was the replication source (publisher).  The process we use is to compare the old schema to our new schema and generate a SQL script to execute on the replication source database.  Prior to using replication we had no trouble performing this task but with replication running there is a problem when changing an existing column in a table, SQL Server will NOT allow it.  The issue stems from the fact that SQL Server drops the column and re-creates the column with the changes, which cannot happen to replicated tables.

The Solution

So, the way I approached this problem was to be able to easily remove replicated tables from a publisher, make schema changes and re-create the replication.  This is a straight-forward but tedious task using SQL Management Studio but is error-prone and tedious.

I started looking at using SQL Management Objects (SMO), which I had used on another project for automating other SQL tasks.  I discovered SMO has a subset of classes call Replication Management Objects (RMO) that are designed just for this task.  The only problem I faced was learning how to use them in a timely manner and be able to write the code to do all that was needed.

The key part to learning to use RMO is to learn how SQL Server Replication is designed and how it works.  Once this is understood, it is pretty easy to see how RMO maps to how SQL implements replication. 

It took a bit of effort to find the right resources on the web to learn how RMO works and locate the resources to turn to if I had a question.  The main point of this post is not what I did but more of the resources I used to build my solution.

The Resources

Most of the information I found and used was directly from Microsoft’s MSDN web site but provided here to help group them together.

Transactional Replication Overview – learn what it is all about.

Configuring Replication (RMO Programming) – probably the best resource for writing an RMO application based on C# or VB.NET.

Microsoft SQL Server Replication Forums – good to read about those who have done it and get answers relatively fast.

MSDN Magazine Article – great way to get started.

Making Schema Changes on Publication Databases – what you need to understand.

The tool I use for comparing databases is SQL Compare from Red Gate Software.   This tool is great, easy to use and allows the user to save it’s results in a SQL script.  Red Gate has released SQL Toolkit which allows for managing replication and some other things.  I have not looked extensively at this product but it may wrap RMO and make it a bit easier to automate your RMO tasks.

All in all RMO is a great way to programmatically manage replication in your application.  It does have a bit of a learning curve, as does any new technology, but once over the curve it works really well. 

Technorati Tags: Microsoft, SQL Server 2005, .NET

Share this:

  • LinkedIn
  • Twitter
  • Facebook
  • Email
  • More
  • Pinterest
  • Tumblr
  • Pocket
  • Reddit

Related

Filed Under: SQL Server

Care about your privacy? I do and use Fathom Analytics on this site.

Fathom Analytics

Recent Posts

  • How to Fix Rails Flash Rendering When Using Hotwire
  • Hotwire Fix for CORS Error when using Omniauth
  • Fix Installation of Ruby using rbenv on macOS Big Sur
  • RailsConf 2021 and the Future of Conferences
  • Fixing Out of Diskspace Errors on Amazon EC2

Categories

Services I Love

HatchBox - Easy Rails Deploys Fathom Analytics
Follow @rbazinet

Rob Bazinet
@rbazinet

  • Exactly this…. https://t.co/yWj7fZ01HR
    about 23 hours ago
  • https://t.co/EmagdpLoNv "Introducing GitHub Copilot X · GitHub"
    about 1 day ago
  • RIP Gordon Moore: https://t.co/c5J9LaHrj8
    about 2 days ago
  • Our daughter works as a teaching assistant at a local K-8 public school. It’s become apparent that students today r… https://t.co/p2t912GVyc
    about 2 days ago
  • Working to wrap up my current consulting gig by mid-April. I will be looking for the next thing soon. If anyone has… https://t.co/sg3cSV9yqM
    about 3 days ago
  • RSS - Posts
  • RSS - Comments
Find me on Mastodon