| Author |
Topic |
|
leoray
Starting Member
5 Posts |
 Posted - 2011-09-08 : 10:00:20
|
| Hi ,We are planning to implement replication in our environment. Basically we are looking to replicate data from server A (sql server 2005) to Server B (Sql server 2008) . But there are a few cases where data on Server B may be updated and that needs to be replicated back to server A. So after a bit of reading, I realized transactional replication with updatable subscribers is probably a good choice. But i also came across a article which suggested that this option would be deprecated in the future. What are the shortcomings of implementing transactional repl with updatable subscriber in a production environment. Also, a few options on which i need some clarity.How can i enable the option of not replicating certain deletes(Stored procs) from subscriber to publisherAny help would be greatly appreciated. Thanks leo |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-08 : 10:24:43
|
| I'd suggest peer to peer instead.If you're publishing a specific table, but don't want deletes replicated, set that in the article properties. Easiest to use the GUI for that -- in the publication properties, go to the articles tab, click the article properties button. In the statement delivery section, change DELETE Delivery Format to Do Not Replicate Delete Statements.By the way, careful with this. In transactional replication, it is atypical to purposely allow the subscriber to come out of synch with the publisher, and validation tests will fail on rowcount rules.By the way, very important to read this since you're using 2008 and 2005. |
 |
|
|
leoray
Starting Member
5 Posts |
Posted - 2011-09-08 : 11:31:11
|
| Hi Russell,Thank you for the quick reply. I'm only concerned about a stored procedure which is supposed to delete data from Server B (subscriber)but not from Server A. How can i implement this. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-08 : 11:43:52
|
| You can't. You can control it at the article (table) level, but not based on what specific stored procedure, or bit of T-SQL performs the delete. |
|
|
|
leoray
Starting Member
5 Posts |
Posted - 2011-09-12 : 12:33:20
|
| Thanks Russell for the quick response. I have used transactional replication before but never in a production environment. My concern here is availability of the production server (server A) irrespective of any replication failure. Server B would be mainly used for reporting with minimal updates. So for me availability of Server A is of utmost importance. It would be nice to have real-time replication so that the production server and the reporting server are always in sync. Also, is merge replication an option in my case. Thanksleo |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-12 : 14:52:13
|
| Are both servers in the same data center and same domain?I ask, becuase if they are, I wouldn't allow updates on the reporting server. Make them on the production server only.Also, it might be possible that what you really need is a data warehouse and not necessarily replication.Creating publications on the Production server won't add any risk of taking it down. |
|
|
|
leoray
Starting Member
5 Posts |
Posted - 2011-09-12 : 16:32:20
|
| Currently we have only one production server which serves as the primary data source for our portal. All the reports from the portal go against this server. All the aggregation jobs run against this server right now. To reduce the load on the production server we have decided to have another server (Server B) against which all the existing reports from portal and all our aggregation jobs would run . Replication will also ensure all the portal users would see real time data . Both are in the same data center and same domain |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-12 : 21:16:50
|
Perfect. But why the need to have changes made at the reporting server pushed back to production?I would only give read permissions to users at the subscriber. If they need to create tables/views etc (and your aggregation jobs will too), then what I'd do is create a seperate database for the aggregations and the user objects, while still allowing them only read access to the subscription databases.Treat subscription databases as read only and you'll almost never have problems. Allow users to modify it and you'll be manually resynching data from time to time.  What is the nature of the changes that might be made on the subscriber that you want reflected at the publisher? |
|
|
|
leoray
Starting Member
5 Posts |
Posted - 2011-09-13 : 11:06:54
|
| Hi Russell,Forgot to mention that we already have a reporting server which has all the aggregated data.In the current set up the aggregation jobs and the front end portal run against the prod database. One of the reasons why we are investing on a new server (New reporting server) which would be basically used for all the aggregation jobs and portal.Some of the tables on the production database get updated when users enter data through the portal. We usually 50-60 updates on a daily basis through the web portal. We are looking to alienate the production server from being queried. Thanks for all your help |
|
|
|
|
Transactional replication with updatable subscribe