What is necessary to implement a SQL Server Redundancy Solution?

Posted on 2005-04-25
Last Modified: 2010-03-19
I would like to set up a complete redundancy solution for a business database our company uses. This will include new server hardware and SQL
server replication between the two servers.

I would like to move the current live environment to a new server, and use the current as the redundant back up. I would also like SQL server
replication of the database between the two, to ensure the redundant  server is completely up to date with the live one.

Can this be set up within SQL Server regardless of the database, or do the database tables have to have CLSID columns for example or any other changes? If it is possible, what needs to be done to make this happen?

I would also like to know if it will be possible to set up the clients to use a live server, but have a simple option of selecting the redundant one
if necessary. If so, how is it done?
Question by:purplesoup
    LVL 11

    Expert Comment

    Any database can be made to publish for replication, SQL Server will add the GUID column's as required and you set the rest of the options such as identity ranges

    As for clients swithing to the redundant server do something like ->

    If Not Connection.Open Then
          Connection.ConnectString = "Redundant Server Con String"
    End If

    Author Comment

    Interesting - unfortunately neither of these are realistic options. Contractually we can't change the schema of the database we are using, so we can't add GUID columns, and we can't get to the source code of the application, so we can't add the code to switch the connection if the first one fails.
    LVL 11

    Expert Comment

    If you can't change the schema forget Replication. You need to add fields, tables, views and another database for distribution. Try another method such as Log Shipping or backup/restoring on schedule.

    If the application uses a DSN to connect to the database then you are in luck because to switch to another SQL Server you just change the DSN. Find were the connection string is stored - it is rarely in the application - check the registry, an ini file, a config file.

    If it is Access then you change the connection property under the file menu (for an ADP project) or the Linked table manager for an MDE/MDB

    Author Comment

    I thought you only needed to add GUID columns if you were running two-way replication - isn't this scenario just a one-way real-time replication where the transactions from one database are just automatically applied to the backup. Why do you need fields, tables and views to apply a transaction twice?

    I'm still not clear how you automatically change DSN if one database goes down? Do you just mean the users have to know the DSN to the backup database and manually change it? It doesn't sound a very smooth way to handle a database going down.

    Can you explain more about log shipping - how does that work?
    LVL 11

    Accepted Solution

    You will need two way replication because if the primary server goes down and users operate on the backup you will need a method to get the data back to the primary server ni real time (or close enough) when it recovers. The difference between log shipping and replication is that log shipping will create a read-only copy. That is if your users change add or delete data on the backup server it will not be reflected on the primary server.

    The fields, the tables and the views all get created to aid replication - the schema is significantly different once you setup a database to publish. Replication provides more complete redundancy in my opinion and enables transparent operation for users.

    Redundancy can go on for ever depending on what you want to spend and the time you have. My prefered method is to install MSDE on every client, and set it up for merge replication to 2 backend servers (with options set to use server2 if server1 fails) no DSN changes needed if the database is sitting on the local machine and replication will deal with failing over for synchronisation

    As for the DSN since you can not chaneg the app source you can always craft a tiny exe that will do the swap for them - then the end user can click the exe and the DSN will be acurate, then just restart the app.

    as for log shipping as per books online
    In Microsoft® SQL Server™ 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers.


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now