August 21, 2002 at 10:55 am
Hello GURU's out there,
I am learning a lot about replication at this moment but I have the following problem.
I have a database with a publication in it. During the night, the database get's
refreshed and records are added. After this process some maintenance jobs run.
During this process nobody should be allowed to synchronize.
Is there a way to make this possible....
What should happen is the following...
1. Check if anybody is merge replicating the article.
2. If noboy is replicating, Disable publishing of the article
3. Run the data refresh process
4. Do some maintenance jobs
5. Enable publishing of the article
Is this possible ?????
August 21, 2002 at 11:56 am
You can. Lot of work. If you're not modifying schema, you can just stop the replication jobs if you just want to pause the changes being pushed to the subscriber. If you bypass replication to make changes, your pub is out of sync, forces you to snapshot a new copy.
Maybe you could expound some on what the maint jobs do?
Andy
August 22, 2002 at 3:22 am
I know i can temporarily disable publishing on the server.
Then run the maintenance job and enable publishing, this works.
But the maintenance jobs BCP data out and we need to make sure
nobody changes anything during the different BCP's.
So a merge replication takes 1 minute on the network and
8 minutes via a modem.
What i need to know if it is possible to detect the
running synchronisation sessions and kill them.....
Is this possible....
Oh and andy you reacted to a performance proble we encountered. The solution was to define
the correct clustered index. Merge rep uses views and i believe ( not for sure) a view is ordered in the easiest way in SQL ( so thats clustered) and this made a HUGE difference instead
of a table scan on 1.000.000 it now does a clustered index scan. Making a huge difference.
Times have gone down from one hour to one minute........
Edited by - well0549 on 08/22/2002 03:36:46 AM
August 22, 2002 at 5:04 am
Interesting. For any one file, using a table lock would probably work, but are you looking to keep the results consistent across multiple files? Was thinking you could generate a snapshot, use those files possibly? If not, how about just running a proc that generates a series of permanent temp tables inside a transaction, then you can take your time exporting them?
Andy
August 23, 2002 at 3:06 am
No customer is DEMANDING that every sync is killed as soon as refres starts
so i could kill all the spids that are synchronizing.
But how do i find out who is synchronizing.....
And is KILL <SPID> the best way of killing the sync.....????????
August 23, 2002 at 4:12 am
If it is listed as a system process it will not let you kill them. If not then no KILL can have the effect of stopping the connection permanently, forcing a reboot and is not suggested except as a last resort.
Now if this is a one way sync then you could use DTS or a JOB with Active scripting in if to use the SQLNSCommandID items in SQLNamespace object. There is an item SQLNS_CmdID_STOP_SYNCHRONIZING that is to Stop Synchronizing, Immediately stops synchronization of a subscription. Run this on all replicated databases and then run your refresh. Once done then you should be able to start syncing again and all updates are forwarded or a snapshot is generated and reapplied. I have not worked with this but a little coding help can be found here:
http://www.swynk.com/friends/mccarty/SQLDMONSP2.asp
http://www.civilsolutions.com.au/publications/managing_sql_server2.htm
This is general use of Namespace object and can provide pointers on basic coding. Sorry cannot find nor do I have an example of what exactly you need to do. But look at SQLNSCommandID in BOL and the code examples above plus MSDN and a solution should be able to be reached. Otherwise if someone else wants to post code to do it based on this please do so.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 23, 2002 at 5:33 am
Not sure about merge, what I do with transactional is to stop the job which stops the logreader/distribution agent.
Not sure the customer understands the impact of what they are asking. Maybe better to ask what the desired result is, then design a process that will do that. My opinion of course.
Andy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply