June 2, 2005 at 7:06 am
Being a SQL2000 newbie, this may be a simple question (or two):
Scenario: I need to pull data from one MSSQL2000 server (ServerA) to another server (Server B: today SQL2000, tomorrow possible Oracle). The problem is that ServerA is a "product" database, where any changes to the stucture will impact support from the supplier. Therefore, the idea is to port the data from ServerA to ServerB, where the format can then be altered. I have been trying to use the SQLServer Replication function, but I am running into a few problems:
1) ServerA Database tables do not have Primary Keys. I grouped columns within most tables (80%) to create a Primary Key. However, not sure how to handle the remaining tables.
2) When using the SQL2000 Replication service with a Subscriber setup to Pull data, it by default replicates all Insert, Modify, AND Delete transactions. I would like to set up the Subscriber/Publisher to only replicate the Insert and Modify functions. Is there an easy way to do this? I renamed the Stored Procedures (MSDel) within the Subscriber's database; however, errors are generated and upon every Delete action. Not what I would want....
Maybe I am going about this the wrong way!!! Is there a better way to replicate a SQL database from one Server to another aside from using MSSQL Publisher/Subscriber tool?
June 6, 2005 at 8:00 am
This was removed by the editor as SPAM
June 7, 2005 at 9:21 am
1- When using merge replication, a column with the property ROWGUIDCOL, along with a unique index is added to each published table. This can serve as your primary key on the table if you don't have another way to uniquely identify each row.
2- You are on the right track with renaming the delete procs, but instead remove or disable the delete triggers that replication adds to each table. You'll need to do this on both publisher and subscriber. This will cause your publications to become out-of-sync when a delete occurs.
A more elegant solution is to prevent deletes by setting permissions.
Good luck!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply