 IP
|
 |
| Joined: 12 Apr 2005 |
| Total Posts: 47 |
| |
|
Transactional Replication
Posted: 12 Apr 2005 05:48 PM |
I want to keep backup of Database on another server so that I can get a copy of database whenever something wrong happens with Live version.
I want to keep latency time of 2 seconds.
Suppose, if I save a record on live database then it should transfer to backup databse within 2 seconds.
Same for delete ,modify etc.
Is transactional replication useful for this purpose?
Can you plz provide info regarding how to do TR(step by step)?
Any other ALTERNATIVE To do this at databse level?
Thanks
-IP
|
|
|
 |
|
|
Re: Transactional Replication
Posted: 12 Apr 2005 07:21 PM |
Hi ip
Transactional replication isn't too hard to set up, so I'll have a stab at summarising for you... I'm not sure where any step by step guides exist (hopefully someone who knows might chime in with this) but you can easily achieve this with the wizards.
(1) Start on the source server - right click on the Replication node in the Enterprise Manager (EM), select New\Publication. Walk through this wizard to create the "Publication" - it's fairly straight forward - you simply choose the database to be replicated, the fact that you want transactional replication etc.
(2) On the source server again, right click on the Replication node again & select the "Configure Publishing..." menu. You set up where the "Distribution" server is going to be here & which servers can "Subcribe" to the publications on the distributor.
ok, I'll stop here to make sure you understand that there are three databases involved in replication, which can be on 1, 2,3 or more servers. If everything's onone server, this is a no-brainer, but if you're replicating to a secondary server, you'll often want the distributor on that secondary server rather than the source server as this can reduce load on the source server.
(3) On the secondary server (assuming the Distributor's here), navigate down to the "Replication Monitor" node, then into the Publishers, through the source server node & onto the Publication node. Right click on it & choose "Push New Subcription". Again, you should be able to follow the wizard & find your way home from here.
I may be trivialising things a bit in this post, but generally speaking, transactional replication is the easier of the replication options to choose from & newbies can usually get away with simply following the wizards, as long as you've got a basic grasp of the fundamentals - what a publisher is, what a distributor is & what a subscriber is. I suggest you read whatever you can find to ensure you grasp these concepts first as this will help you a great deal with getting around the replication toolset.
Do post back if you're having trouble getting things set up..
Cheers,
Greg Linwood |
|
|
|
|
|
Re: Transactional Replication
Posted: 13 Apr 2005 11:52 AM |
Hmmm ... haven't worked with replication in ages, but isn't the replication agent run via a scheduled job?
With a latency of 2 seconds wouldn't that rule out replication as you can only schedule jobs every minute? I think there is a hack that allows you to schedule jobs with finer granularity, but would you trust it on a production server?
If the 2 second latency is flexible and can be moved to 1 minute, how about good old log shipping? Removes the complexity of having to handle schema changes and extra guids.
Using the log shipping built into SQL Enterprise Edition, we log ship every minute from a production server in Melbourne to a stand-by server in Sydney. On the other end of the scale, we also use a custom built log shipping routine to log ship between two Standard Editon servers at the same site.
|
Thanks
Phill
Colt 45 - the original point and click interface ;)
http://philcart.blogspot.com/ |
|
|
|
|
Re: Transactional Replication
Posted: 13 Apr 2005 12:02 PM |
You'd run the log reader in under continuous mode for this scenario. 2 seconds is a bit iffy, but continuous trans replication usually keeps things within a few seconds.
Cheers,
Greg
|
|
|
|
|
IP
|
|
| Joined: 12 Apr 2005 |
| Total Posts: 47 |
| |
|
Re: Transactional Replication
Posted: 13 Apr 2005 02:02 PM |
Thanks Greq
I had followed the steps as you suggested.
The puplication,distributer and subscription configured successfully.
And I tested the transaction in following way.
I put one record in one table of publication.
And checked same table at subscription but opps I do not find the updated record at subscription side.
----------------------------------------------------------------------------------------------------------
I have done following steps:
1)Start on the source server - right click on the Replication node in the Enterprise Manager (EM), select New\Publication and follow the wizard.
2)On the source server again, right click on the Replication node again & select the "Configure Publishing..." menu and follow the wizard. I had configured another server (Secondary Server) as Distributer Here.
3)On the secondary server (Distributor's here), navigated down to the "Replication Monitor" node, then into the Publishers, through the source server node & onto the Publication node. Right click on it & choose "Push New Subcription". Followed the wizard.
----------------------------------------------------------------------------------------------------------
I navigated as
Subscribers replication monitor-->Publisher's -->Pubilsher server name-->Pubilcation name.
Then I found following status in right side window.
Snapshot-Status:Never Started
LogReader-Status:Never Started
ScbscriberServer:SubscriberDBName-Type:Push,Status:Never Started
----------------------------------------------------------------------------------------------------------
I navigated as
Subscribers replication monitor-->Agents-->Snapshot Agents
The I found status as follows:
Publication Name -Status:Never Started
Same for log reader and distribution Agents
----------------------------------------------------------------------------------------------------------
All Misc Agents having status succeed except for Reinitilise subscription having validation fetures-Status:never Started
----------------------------------------------------------------------------------------------------------
From source database side I navigated as
Source database-->Replication-->Publications-->Publication name:PublicationDBName
In right side window I found subscription name status as "Active"
----------------------------------------------------------------------------------------------------------
Sql Server Agents on both servers are running.
----------------------------------------------------------------------------------------------------------
I do not understand why I am not getting the data replicated in Subscription database?
What else should I do?
Which step I am missing?
Plz advice me.
Thanks,
IP
|
|
|
|
|
|
Re: Transactional Replication
Posted: 13 Apr 2005 02:59 PM |
Hi IP
Right click on the Snapshot Agent & select Start Agent. Depending on the size of the publication, this could take a while.
Are you doing this on a production system? If so, be aware that initialising a snapshot can be a resource intensive activity & can block users (there are options for concurrent snapshoting though).
You might also need to start the Log Reader Agent.
HTH
Cheers,
Greg |
|
|
|
|
|
Re: Transactional Replication
Posted: 13 Apr 2005 05:03 PM |
IP
Interestingly enough this article released on SQLServerCentral
http://www.sqlservercentral.com/columnists/aMachanic/bookreviewofaguidetosqlserver2000transactionalands.asp
|
Thanks
Phill
Colt 45 - the original point and click interface ;)
http://philcart.blogspot.com/ |
|
|
|
IP
|
|
| Joined: 12 Apr 2005 |
| Total Posts: 47 |
| |
|
Re: Transactional Replication
Posted: 20 Apr 2005 12:14 PM |
Thanks all of you
I don't know what happened but now replication is running.
Regards,
IP |
|
|
|
|
gwood
|
 |
| Joined: 23 Jun 2004 |
| Total Posts: 9 |
| |
|
Re: Transactional Replication
Posted: 23 May 2005 07:59 PM |
@IP- you stated 'I want to keep backup of Database on another server so that I can get a copy of database whenever something wrong happens with Live version.'
I hope you dont replicate your problem!
have a think about what you are really trying to achieve, is it covering for a hardware or site failure or is it application/data errors?
|
|
|
|
|
IP
|
|
| Joined: 12 Apr 2005 |
| Total Posts: 47 |
| |
|
Re: Transactional Replication
Posted: 09 Jun 2005 11:54 AM |
| It is for application/data and manual errors. Thanks |
|
|
|
|