Post reply

Best Option to Replicate Database?

  • April 6, 2004 at 7:02 am

    #86322

    We have a database that consists of a number of static tables, several tables that are rebuilt from scratch every night, a couple of SP's and a gazillion views. Currently it resides on a old SQL Server (old hardware, current software) and I'd like to migrate it to a newer High-Availability Clustered SQL Server environment our SQL folks just built. The problem is this database is referenced via ADO, ODBC and JDBC connections from literally hundreds of programs so I can't just pick up the database and move it.

    I assume the best option for migrating this database is to

    1. replicate it on the new server,
    2. make the new copy the master copy and rebuild that every night
    3. replicate the new database back to the original as a copy
    4. migrate apps as time permits until nobody is using the old version anymore.

    So here are the questions:

    1. Does this sound like a reasonable approach?
    2. If you have a database on Server A and replica on Server B can you switch so that Server B becomes the master copy and Server A the replica? If so, how?
    3. What is the best type of replication to use given that there is no real-time transactional updating of data in this database. It's static from 5am through 5am.

    I'm not a DBA and our SQL DBA's are really Intel Server folks who moonlight as DBA's when they have time so they're looking to me to tell them how to do this. Of course I don't have a clue, its not really my job and my boss won't send me to SQL classes (because its not really my job). Oh, if they're not really SQL DBA's then how'd they build the HA SQL Cluster? They hired an MS consultant for a couple of days to do the SQL part once they had the hardward and Windows part set up.

    Any help would be greatly appreciated.

     

  • April 6, 2004 at 8:03 am

    #501873

    I am not an expert with clustered servers, but if it is possible to rename a clustered server then I recommend moving all the backups to the new server, restoring them, and test to make sure everything is fine.  Move all the logins and once you are sure everything is fine then you simply take the old server off line and rename the clustered server to the name of the old server.

    If you are not up to investigating if this is a viable solution for you, then in my opinion merge replication could be the simplest solution since it would require setting up once and then you wouldn't even need to worry about where a change is made because it would get replicated to the other server.  Since you say it is static except for tables that get rebuilt then it could work for you since you would have little activity to replicate on a regular basis.

    You say some tables get rebuilt from scratch.  I hope this means emptied and refilled with data because with all replication, if a source table is replicated you will not be able to drop it and recreate it.

    Others will have other opinions so you may want to wait and see if they respond to this and then make your decision.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • April 7, 2004 at 4:57 am

    #501971

    Renaming the clustered server is not possible for a couple of reasons:

    1. We are running on one instance within the server. There are multiple instances running other critical databases.
    2. The instance on which we're running already has critical databases on it. Renaming it would involve a large number of coding changes.
    3. The problem server we're trying to eventually get off of (and the name to which we'd have to rename the cluster server) has about 50 databases on it, most of which aren't mine. If I renamed the cluster server to the old server name I'd have to bring over all those databases and at least one of them is causing the problems that is motivating me to move my database.

    I was looking at merge replication, but didn't want to influence anyone's answers. There are two tables that are rebuilt via TRUNCATE and batch load. The only other changes to the database are infrequent additions of small, static lookup tables (i.e. Zip Codes for a Region - maybe one per month), and Views used to subset the data for lookup purposes at the rate of about two per month.

    My biggest concern is that if I initially set up replication from Server A to Server B, can I at some point change it so it replicates from B to A? Or is a better approach to backup/restore from A to B then set up replication from B to A right off the bat?

     

     

  • April 7, 2004 at 1:27 pm

    #502052

    If it matters which server has its tables published for replication then set it up that way first if possible. 

    I just looked at a few tables from the publisher and subscriber servers that are using merge replication.  All have the same table structure and same indexes and same data.  I think it is more in the mind which is the master copy since they are both identical.  We mostly make our changes on one server and that is the one that is the publisher.

    I think as long as both are up and merge replication is in place then it doesn't matter which is the publisher and which is the subscriber.  I would do some testing with test tables and data if possible.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply