MS SQL - Migrating from Prep to Production

Posted on 2005-04-21
Last Modified: 2010-03-19

We have two seperate Servers hosting the same DB - Server1 + Server2.

Server1 purpose is to receive all updates and compile all reports.
Server2 is a read only database, thus heavily indexed.

We are looking for the best way of migrating all data/updates from Server 1 to Server 2.  The migrate brings it challenges as there are tables that need updated with new and there are tables that need deleted and then all records added (all the tables that contain report data).

The options that we have identified are:

1) Prepare everything in Server 1 & migrate DB Files
2) Extract data in Server 1 & upload into existing tables in Server 2
3) Extract data in Server 1 & upload into new tables in Server 2, the drop existing and rename new.

Option (1) requires DB to be offline for a short while.  We have no issue in automating the file transfer, but how to automate the Start & Stop of the server is a challenge.

Option (2) requires 2 hours when the DB is unavailable, can be scheduled during the night.

Option (3) requires no time when the DB is unavailable, but unsure about the solution of uploading into say table1temp then delete table1 and then rename table1temp to table1.

The volume of data is rather large. On any give batch of updates the size is approx 4GB.  But this is data only.  Indexes are another 20GB.  

In short we are looking for the most reliable solution that requires least effort & less risk.  Also with the volume fragmentation is an issue as well.

I would like to ask your prespective.  Just before I sign off - it may be worth mentioning that we have rulled out Replication as an option due to the fact that we dont presently have sufficient bandwidth between Server1 and Server2.  The options about we can use compression of extracts & files to minimize the hit on bandwidth.

Thanks in advance for your help.

Question by:amacfarl
    LVL 21

    Accepted Solution

    Personally, I like option 2.

    Use DTS to get the info you need into .csv files.  
    Compress the files locally
    Ship them to the destination
    Use DTS to load them into the destination db, either appending or truncate/insert as appropriate

    LVL 2

    Author Comment

    Thanks Kevin for your feedback.

    The only issue is that the process involves
         1. Drop All Indexes
         2. Upload New Data
         3. Create Indexes.

    The reason that I need to drop all indexes is that some of the tables contain 2 million + records and 1 contains 16 million records.  Drop & Upload & Create is the quickest option.

    Hence, I personally am thinking about option (1) or (3). Option (3) is exactly like option (2) except you upload into a mirror of the table and once the process is finished, drop the old copy and rename the new copy to equal the new copy.

    but there again.... I am new at this game... am I on a different planet???? (well... my wife says I am !!... but that is a different story!)
    LVL 21

    Expert Comment

    Appending data to a table should not require dropping and re-creating indexes, unless the amount of data is significant.

    Renaming tables as you suggest will also work, but you still need to rebuild the indexes, as they (and the index statistics) are no longer accurate...the data is completely different (including pointers, page allocations, etc.)
    LVL 2

    Author Comment

    Hi Kevin,

    Again thanks for you feedback.. a few followup questions:

    - If I go for the renaming solution, can I create the indexes then rename (instead of visa versa). I am pretty sure this is ok.. but worth checking.

    - Appending data - to be honest, I am not familar with the T-SQL code for this.   All records are extracted using a custom program and uploaded using the same program with INSERT - can you share how to append please?  If I use append, with the indexes, how does the performance compare to INSERT?

    What are your views on option (1) as it is a direct copy??  From your experience more or less reliable than the others.

    thanks again
    LVL 21

    Expert Comment

    Insert and Append are the same thing.  Append is the word used in the DTS package wizard.  

    >>If I go for the renaming solution, can I create the indexes then rename (instead of visa versa). I am pretty sure this is ok.. but worth checking.<<

    I believe when you rename, the old table and all associated indexes are dropped.  I an not an expert on the mechanics.  If this is not the case, and the indexes persist...would you want an index that was created on old data?  Consider that the old table may have had data like:

    1   A
    2   b
    3   c

    and the new data might be:

    17 Foo
    89 Hi There
    3 C

    Ths old index pages would completely inaccurate....
    LVL 2

    Author Comment

    hmmm... big ouch.  kind of kills the renaming solution as the main driver was to minimise down time and it is a simple swap.

    will have to test.  I have a table called T_Sales with 15 million records (18GB of Indexes alone) and the daily update is 500,000 records .  So I was planning:

    Step 1: Copy T_Sales to T_SalesTemp
    Step 2: Upload updates to T_SalesTemp
    Step 3: Create all Indexes on T_SalesTemp
    Step 4: Drop table & Indexes on T_Sales
    Step 5: Rename T_SalesTemp to T_Sales

    The assumption here is that Step 5 moves the indexes........
    LVL 21

    Expert Comment

    I really think the rename is actually a drop table followed by whatever else can test this by profiling the process on an empty sample table

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now