Triggers or replication?
-
Hi all, I frequently use the forums here and they are a fantastic resource. I just hadn't signed up until today, so hello :). I'm a bit of a SQL newbie and I've only been working as a junior DBA for a couple of months so any help would be really appreciated.
I have a client who is trying to achieve the following and I'm not totally sure about the best method to go about setting this up.
He has a web application which is entering rows on Intance1.dbo.Table1, he wants the data to be copied across onto Instance2.dbo.Table1 whenever new data is submitted. Instance2 is a test environment so the data will be modified and played around with and definitely shouldn't synchronise the modified data with Instance1.
My first thought was to use a trigger but it didn't work as one of the columns is text which apparently don't get a long so well with triggers that you're trying to use insert with.
I then tried transactional replication but got a few error messages back, and after hours of searching I'm still drawing blanks as to what the issue is.
Is there another simpler way? The client is pretty flexible about whatever methods are used so any help you can give would be really appreciated.
Thanks a lot,
Jimmy
-
Welcome aboard Jimmy;
A few questions:
Instance1 and Instance2 are seperate servers, right, and not databases?
so the commands are really referencing ServerInstance1.LiveDatabase.dbo.Table1 and you want to migrate some data to OtherServerInstance.TestDatabase.dbo.Table1?
Or are what you called "instances" just two different databases on the same server?
in general, i think if the databases are on the same server, you *might* be ok with a trigger to migrate data....but i'd look towards replication first...it's not hard at all to set up.
triggers that move data across servers via linked servers are not really a good idea, as the linked server can become unavailable due to the network, or username/password issues, among other things.That can make the original insert rollback and result in lost data. You'd want To avoid that, for sure.
How "fresh" does the data need to be on test? would a once a day backup be on? upt to a half hour old? absolutely "right now" kind of data? the refresh toleranc ewill help guid the best solution.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (8/8/2011)
Welcome aboard Jimmy;A few questions:
Instance1 and Instance2 are seperate servers, right, and not databases?
so the commands are really referencing ServerInstance1.LiveDatabase.dbo.Table1 and you want to migrate some data to OtherServerInstance.TestDatabase.dbo.Table1?
Or are what you called "instances" just two different databases on the same server?
in general, i think if the databases are on the same server, you *might* be ok with a trigger to migrate data....but i'd look towards replication first...it's not hard at all to set up.
triggers that move data across servers via linked servers are not really a good idea, as the linked server can become unavailable due to the network, or username/password issues, among other things.That can make the original insert rollback and result in lost data. You'd want To avoid that, for sure.
How "fresh" does the data need to be on test? would a once a day backup be on? upt to a half hour old? absolutely "right now" kind of data? the refresh toleranc ewill help guid the best solution.
They are two different servers, that's correct.
ServerInstance1.LiveDatabase.dbo.Table1 and OtherServerInstance.TestDatabase.dbo.Table1 would be right. Sorry I didn't make that more clear in my first post, since becoming a DBA there has been a definite lack of sleep and an awful lot more working in my life. 😛
It needs to be pretty fresh, almost immediately would be ideal as there's an application in the test environment which will be processing the newly inserted rows. I believe they want to find out how well it works vs the application they currently use.
-
If it need to be pretty fresh, then your option is transactional replication. But there is a drawback when you want to use it.
You cannot allow your developers to delete a record. Reason behind this is if in your production, that particular record is updated, it will cause for your replication.
Other than that just follow the security requirement and it should be easy to set up.
-Roy
-
Roy Ernest (8/8/2011)
If it need to be pretty fresh, then your option is transactional replication. But there is a drawback when you want to use it.You cannot allow your developers to delete a record. Reason behind this is if in your production, that particular record is updated, it will cause for your replication.
Other than that just follow the security requirement and it should be easy to set up.
So if we use transactional replication, we can't delete rows on the test server, is that what you mean?
Also doesn't the DB go into 'Restoring' mode when you use replication?
-
Restoring mode comes up when you have log shipping. Not for transactional replication.
Yes, you are correct, the developers should not delete records on their test server (Subscriber). Also keep in mind that you can replicate only tables that have PK.
If you use snapshot replication, you can delete or do what ever you want but the data wont be fresh. Depending on the size of the DB you can schedule the Snapshot replication.
-Roy
-
You could also use a SSIS package to copy your data, it will not be as fresh as with Transactional replication, but you can decide how often will you run the package.
-
Thanks everyone for your replies.
I've attempted to set up replication but I'm getting errors on the Log Reader Agent.
I've looked a bit online but I can't find anything concrete for these, any help would be really appreciated
Error messages:
The process could not execute 'sp_replcmds' on 'CMA-L-DB1\CMALIVE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)
Get help: http://help/15517
The process could not execute 'sp_replcmds' on 'CMA-L-DB1\CMALIVE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
Thanks,
Jimmy
-
Sorted it.
Used that guide and it all works perfectly now.
-
Thx for the Update Jimmy.
-Roy
-
Jimmy Nixx (8/9/2011)
Sorted it.Used that guide and it all works perfectly now.
When you have setup the replication, keep an eye on the replication monitor or configure alerts to alarm you. If there is some error which caused the replication to fail, then your distribution grow and also, no further transactions would be applied to your subscriber.
-
Actually, I'm not there. This is becoming more and more of a pain in the butt.
Okay, on the Live database there's a column named Processed which is either set to a 1 or 0 depending on whether it has been processed or not. They get processed within around a second generally.
The client wants all the data that is currently in the Live database to be on the Test database (these are all set to 1), but after all that, all the newly added rows which get replicated to the Test database from the Live database need to be set to 0 until they get processed on the Test server.
Problem is that the transactional replication is replicating the 1 marker across when the rows in the Live database get updated. I tried using a filter to replicate those set to 0 but then this got rid of all the old data which has already been processed!
So I'm stuck again and need your help once more SQL Server Central, and any suggestions you have or alternative methods would be appreciated, thanks
Jimmy
-
You can alter the stored proc used for replicating the inserts to suit your needs. The SP will be in your subscriber DB (TestDb). The stored proc will have the name dbo.sp_MSins_tablename. it will have a simple insert statement. Just hardcode the value for the column called Processed as 0. That should work.
-Roy
-
Roy Ernest (8/9/2011)
You can alter the stored proc used for replicating the inserts to suit your needs. The SP will be in your subscriber DB (TestDb). The stored proc will have the name dbo.sp_MSins_tablename. it will have a simple insert statement. Just hardcode the value for the column called Processed as 0. That should work.So I would have to wait until the databases are synchronised before making that change in order to preserve the already processed items, correct? And then when our application processes the row it can change the value to 1?
-
That is correct.
-Roy
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply