MS SQL - Migrating from Prep to Production

Posted on 2005-04-21
Medium Priority
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
  • 4
  • 3
LVL 21

Accepted Solution

Kevin3NF earned 1500 total points
ID: 13833429
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


Author Comment

ID: 13833759
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

ID: 13833919
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.)
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 13834293
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

ID: 13834442
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....

Author Comment

ID: 13834530
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

ID: 13834579
I really think the rename is actually a drop table followed by whatever else happens...you can test this by profiling the process on an empty sample table

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
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…

864 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