How do I coordinate an SSIS data migration with updating the code's connection strings?

Question

I am planning to migrate a large database table from our main SQL database to a separate one. Because it's a high volume/one time process, I'm going to use SSIS. Does anyone know how best to time migrating all the data and changing the code's connection string?

Quick background: Our system uses a table to log every notification that is sent out. The code to send out the notifications runs every half hour. This table doesn't have any updates or deletes, just inserts and selects. Before sending out a notification, we do a select on this table as a definite check that we're not sending duplicates.

My basic plan right now is to do the following:

1. Move all the data the the new database using SSIS. (Could take a long time)
2. Wait til that's completed. Then, turn off the notification service.
3. Update the code with the new connection strings.
4. Move any data that was inserted between the SSIS package completed and when I turned off the notifications. (Probably won't take long - not much new data)
5. Turn the notification service back on.

Is this the best way to do it, or is there a better way?

Also if I do it this way, I'm not sure the best way to migrate only new data (step 4). Ideally, I could set it to "insert only if it doesn't already exist", but I'm not sure if that's an option with SSIS. My idea right now is to move the big chunk of data (step 1) with a query (select * where InsertedOn < @SomeDate), then move the rest with another query (select * where InsertedOn >= @SomeDate).

Answer 1

If this was me...

1. Stop the system that sends out the emails and whatever inserts the new data.
2. If needed change the identity seed of the new table to be higher than the highest value in the current table.
3. Copy across the records that haven't been sent out by the nofitication process
4. Change connection strings and start up the software.
5. Start copying across the records that are left, ignoring the ones that have already been copied and processed. At this point we don't care how long this takes. If you want the easy way out, edit the transformation, and tell it to ignore errors and have tell SSIS to copy everything over.

Answer 2

Hi, your connecttion string problem can be solve by putting your connection string in a varialbe and then you can use the expression attribute that can switch the connection string on execution time :)

Answer 3

If you have decided to do what you said (SSIS export/import), I think your initial 5-step plan was fine. Denny's plan is very good and covers a few other aspects you may not have covered/thought of. The only drastically different thing I could think of is whether or not you could take a backup of the source, restore it as a new db, and then just remove whatever objects you don't need? Then you could just run a "get changes since last backup" script/package. I'm assuming you cannot (a) detach the source, (b) copy the files, and (c) attach as a new db since your notification service must be running. If you could do the detach/copy/attach approach, it would likely be very fast (depending on the size of the other objects in the database). Of course, both of my options take it for granted that you want the same physical structure for the new database (i.e. same file layout).

If you can time this to occur when you take your normal FULL backup, you might be able to do it fairly easily and without too much impact on regular users (assuming your normal FULL backup is taken when there are a minimal number of users on the system).

Questions/things to consider before going this route:

• Not to question your need/business case but are you sure this is necessary? Why not just move the big table to a separate filegroup on another drive?
• Are you planning on storing the new database's files on separate hard drives than the original database? If not, I wonder how much of a value this would be.
• How are you planning on handling referential integrity between the databases (or does that even matter)?