November 30, 2005 at 12:08 pm
I have a production server and a test server. The production server has one giant database that's being used by 2 applications. My goal was to break that database into 2 separate databases, one for each application.
About a month ago, I made the necessary changes on the test server. I started with a copy of the (giant) production database. Then I created a second database, and began moving database objects.
At this point, my production server has the original database (DB_Original), and my test server has the 2 new databases (DB_Original, and DB_New).
I need to update the tables on the test server with the production data, but I need to keep the permissions of the objects on the test server since some of them have changed. Also, I'm concerned about maintaining referential integrity between related tables. Once I've sync'd the data, I believe I can just attach the 2 new databases to the production server to finish my changes.
What is the best way to copy the production data to the test tables without losing permissions on the test tables and while maintaining referential integrity between related tables?
Thanks in advance for any help.
November 30, 2005 at 2:35 pm
Hi,
First question would be, aside from splitting the database into two databases on the test server, have you altered any of the tables? I notice you have altered permissions, but have you altered indexes, added or deleted columns?
I would suggest you look at creating a couple of DTS packages. You could create just one DTS, but for simplicity, I would create two packages. One DTS to each feed the databases on the test server.
By creating a DTS package, you can move data extremely easily between databases and you can also map source columns to destination columns.
I've used DTS for this many times and found it to be the quickest route.
That should get you started.
Clive
November 30, 2005 at 2:50 pm
It Will be good , if you can specify size of your db,giant will not
help here to suggest exact solution.
you can create few DTS packages to copy data from production db to test.
Another option you can try is replication.
But this idea of copy of production database to 2 db on test server and
then attaching 2 db from test to production environment
involves very high risk
What I suggest here is look at your production db find tables with
too many records
Create new Archived database and create few archived tables of your
production db in this new ArchiveDB .
E.g. If you see table1 in prod db with million row
create table1_archive in ArchiveDb
Schedule a job every night/week/month that
copy data from table1 to table1_archive
based on some condition [say creation_date < getdate()-90]
and then remove that rows from prod db table1.
Doing this way all table in production db will be in same db
and you will not find issues related to referential integrity
plus access to table will be fast.
Hope this helps.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
November 30, 2005 at 4:54 pm
Thank you both for the replies.
Clive,
I have not altered any tables aside from permissions. And from all I've read, I agree that creating DTS package(s) is how I should proceed. I just want to be sure I do it correctly. For example, I don't want to recreate the tables because I would lose the permissions that I want to keep. However, one concern I've had is that by not recreating the tables, I may have issues with identity columns. Say I have a customer table with a CustomerID field that is an automatically updated identity column. In the production db, there are 120 rows, so the next id will be 121. In the test db, there are only 100 rows, so the next id will be 101. If I copy the data from production to test, will my test table still want to assign 101 to the next inserted row? That would cause a problem. I'm just hesitating because I don't know if there are other things that I may be overlooking that will bite me later.
Sameer,
I apologize for the misunderstanding. What I meant by "giant" db is that there are a large number of tables and stored procedures. Not that the database is large in disk size... it's actually not. I want to separate the database objects that are used by the 2 applications into 2 separate databases.
Thanks again to both of you.
November 30, 2005 at 5:16 pm
Hi dsql,
In DTS, you can map column to column. Its a drag and drop scenario.
With regards to the identity column, you can use the function SET IDENTITY_INSERT <Table> ON|OFF. This will allow you to explicity insert values into the identity columns. When you are done with the migration, don't forget to turn identity_insert off again to re-enable identity inserts!
Feel free to keep posting on here any issues you have with the DTS packages.
Clive
November 30, 2005 at 11:42 pm
Clive,
I'm beginning work on some DTS packages and have done some testing. I started with a package that would copy the data from several tables in production to the test database.
I set my options as follows...
1. Do not create the destination objects (to retain my permissions).
2. Replace the existing data.
3. Exclude users, logins, and permissions.
4. Copy indexes, triggers, primary and foreign keys.
After the first run there was an error that "SQL Server could not truncate table 'Customers' because it's being referenced by a foreign key constraint." This brings up a couple of questions:
1. In general, when a package step fails, can I assume that nothing has been changed? In other words, if there were 5 tables involved in my scenario, and the error occured on the 4th one, were the first 3 tables modified? And can I view a log somewhere that shows exactly what did and did not complete?
2. How might I change my package settings to avoid the error above? Is it as simple as not copying the primary and foreign keys?
Thanks.
December 1, 2005 at 11:46 am
Mapping column to column directly in the Transformations tab is dangerous because of a bug in DTS designer. Occasionally, upon opening a package, the mapping will reverse: A-Z, ..., Z-A. To avoid this, I always use an ActiveX script to map transformations.
In the transformations tab, choose Delete All, click New, click ActiveX, choose your columns and edit the script under Properties as necessary. I usually build my script outside of the designer, but for your first try just use the column selector to build the script.
December 4, 2005 at 3:24 pm
Red Gate (I believe) has 2 products, SQL Compare and SQL Data Compare. I don't know what the cost is, but they may be helpful. SQL Compare will compare 2 databases and will let you look at the objects and see which are different and gives you the option update some of the objects in one DB to match what is in the other. I use the all the time to copy stored procedures.
SQL Data compare will Match the actual data in the tables you select. I have only used this a few times but it seems to work.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply