October 27, 2005 at 3:35 pm
Hi All,
My situation is this: I got one staging server which on a given interval should push data to 2 production servers; it can completely override the data on production, however there are few tables on the two production servers that it doesn’t overrides; they get their data from elsewhere, and those few tables have to mirror each other on the 2 servers.
So I set up merge replication between the two production servers, for those specific tables, and snapshot replication from the staging server to production for the other tables. The question is: would it better to do complete snapshot replication to the 2 servers for the tables which data is overriden, or, since they already got merge replication going on, to extend merge replication to cover all their tables, and have staging push to only one server, let the merge replication handle the other production server?
Any insight, anyone? (I hope the question is clear… let me know if it isn’t)
thanks
October 28, 2005 at 5:32 am
Perhaps you should consider revisting your solution. Replication isn't the best solution to get data from a staging environment to a production environment. The replication between the 2 production servers may be ok, depending on what you're trying to accomplish (need more details/clarification). I think you could avoid using replication on the prod servers as well.
Why use resources on a producton box for replication which isn't necessary? You're adding rowguid columns to tables which are not really necessary. To get the data from staging to production you could use the import wizard, or set up a DTS package and schedule it to run as needed. There are also other 3rd party tools for moving data such as Red Gat SQL Data Compare.
TO ANSWER YOUR QUESTION:
1. "extend merge replication to cover all their tables, and have staging push to only one server, let the merge replication handle the other production server"
No, you're creating a more complex solution to a simple problem. It's hard for me to sanction this type of solution.
2. Create a DTS package which will push the data to both servers at once.
October 28, 2005 at 10:07 am
Hi,
thanks for your replay. I've been reading about snapshot replication in the SQL Server books online, and the situation that they describe there - when to use snapshot replication - is just exactly the situation that I am dealing with. I could use DTS as well; its still not clear to me what is the downside for using snapshot replication? becuase of the addition of rowguid ? (btw, are they added also for snapshot replication? i thought that its needed only for transactional\merge... snapshot replication simply pushes all the data from scratch every time, doens't it? so why does it need rowguid columns?)
also, the push the production has to be done from within a script. is there a SP or something that could activate a snapshot push? and if i choose the DTS way, how would i do THAT from within a script? (i dont need all the details... a quick reference to the right keywords or places where i could find it that would save me some time would be great)
and, oh, as for the two production servers: both of them support the same website, they share the same load of web calls. and they must match - hence the merge replication. do you know of any other solution? would appreciate any insight..
thanks for your help!
Yoni Sion
October 28, 2005 at 10:26 am
1. I actually have a job that starts replication, if it has stopped. We are running the agent continuously, but essentially I use this sproc:
EXEC @ERR = sp_start_job @job_name = @JOB_NAME
I do a lot more than just this job, but this sproc will start the job for you.
2. Still not sure exactly what you need to do.
You would create your DTS package and schedule it to run. No script needed. Within the DTS, you set up the SQL Server connection to your Staging box and a connection to your prod box. Then you select the "Transformation" task between the two connections. In this task you tell it which table to pull the data from and which table to insert the records into. In addition you would probably TRUNCATE/DELETE (execute SQL task) the data from the production server as a first in this package. Then just save the package and schedule it to run as a job, or you can execute the job from a DOS .bat file, whatever your requirements are.
I hope I'm helping.
October 29, 2005 at 8:53 pm
Hi,
well, it comes down to using DTS vs snapshot replication. what i need is: complete overriding of a set of tables with a set from another DB. You said that snapshot replication has its overhead (for example, creaintg a GUID column). what i want to know is:
1. what is this extra overhead comes down to? for example, i tested snapshot replication and i didn't see any GUID field created. snapshot replication seems convenient to use, and i am looking to see what is its downside and the upside of creating a DTS package.
2. I do have a VB script that does the rollover to production (pushes web site files to production, etc.) and as part of doing it it needs to activate the push of the data. i am wandering if there's a stored procedure that actives a snapshot replication push, and also how can a DTS package be activated from a script (is there a stored procedure to execute a package?)
thanks!
October 31, 2005 at 5:59 am
1. Snapshot won't create the guid as a erroneously stated, the merge replication will, which I think you may have mentioned for earlier in the thread about 2 other prod servers, but I should have read more carefully. With replication you'll be adding the additonal snapshot agent, publisher, subscriber. In addition, when you generate the snapshot it creates the .BCP files on your servers hard-drive. Depending on the size of the tables your loading, this could be of significant size. If storage isn't a problem then this probably isn't much of an issue for you either. What you're doing is more of a data load. Traditionally data loads are conducted through bcp, or DTS. That being said, since you already have this solution nearly in place, why not use it.
2. "Is there a stored procedure to active the snapshot?" Yes, you would run the sproc I mentinoned above. [SP_START_JOB]. Replication creates snapshot jobs. You would execute the appropriate job name, or ID.
2a. "Can DTS be activated by script?" Yes, in fact VB.net easilly handles DTS executions. DTS packages can also be executed via DOS command line therefore you can place the statement in a .bat file and call it from the VB script if your not using .net.
October 31, 2005 at 9:52 am
thank you! that really helps. i will go with the DTS solution. but just out of curiosity: to start replication with a SP i do sp_start_job , but what do i give as a job name? i tried to do that and give the JOBID of the replication, something like:
exec msdb..sp_start_job @job_id = 0xDAAD06677C87324AA2F4CB51A0D40F25
and it didn't work. any idea?
thanks again!
October 31, 2005 at 10:15 am
According to the BOL, the sp_start_job will accept @job_name or @Job_id. I have been using @Job_Name as the parameter to pass.
set @Job_Name = (select name from sysjobs where name like 'mymergejob-%')
I do it this way because everything you drop the replication you get a new # appended to the old job name, so I didn't want to have to change my scripting.
I just ran my job using the Job_id and it worked. Did you have the job_id in single quotes?
sp_start_job @job_id = '6A3F11B5-D55C-437A-86BE-7101409DF5A7'
October 31, 2005 at 3:40 pm
thanks! i followed your advice and using the DTS solution. thanks for your time!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply