Accidental Technologist

Musings about Entrepreneurship, Technology and Software Development

  • Home
  • About
  • Still River Software
  • Privacy Policy

Powered by Genesis

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

Filed Under: SQL Server

Microsoft SQL Server Management Studio Express Edition

December 19, 2005 by Rob Bazinet

Tweet

The recent release of Visual Studio 2005 came with it the express edition of SQL Server 2005.  This is a trimmed down version of the full SQL Server 2005 we have all been waiting for but it’s included in the box.

One of the main drawbacks to the Express Edition is the lack of a tool we are all used like Enterprise Manager from SQL 2000.  Sure, you can use Server Explorer in VS 2005 and do some management of stored procedures, views and such but not the management we are all used to.

Enter Microsoft SQL Server Management Studio Express Edition, which provides a graphical management tool for SQL Server Express databases, with a bonus, you can also manage SQL Server 2005 databases as well.

Below you can see a screen shot of the main window.  What a great tool, and it’s free.  After using it for a few days I can say the interface is so much more enjoyable than the old SQL Server 2000 Enterprise Manager I have been using for the last few years.

Sqlexpress

Technorati Tags: SQL Server 2005, Microsoft

Share this:

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

Filed Under: SQL Server

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 1 day ago
  • https://t.co/EmagdpLoNv "Introducing GitHub Copilot X · GitHub"
    about 2 days 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