February 7, 2012 at 12:08 pm
Looking to set up a replication of data hopefully I'm headed in the right direction.
Have a db01 and db02. Would like the data to be mirrored onto db02.
Seems as if database mirroring and log shipping is out of the question if users need to read the data.
Would I be correct to say this would require trans-actional replication SQL Server 2008R2?
Thought log shipping standby/read only would be suffice but if users need to read constantly then restoring interrupt sessions.
What do you guys think?
Jonathan
February 7, 2012 at 12:11 pm
If the users cannot be interrupted by the log restores, then use transactional replication.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 12:21 pm
Thank you Jason,
How much should one worry that the Distributor be the same as the Publisher?
The source data is being used by an application but there are not resources in place to put the Distributor on its on box.
Jonathan
February 7, 2012 at 12:30 pm
Unless there is a significant workload on the SQL Server which hosts the Publisher and Distributor, I am sure you can configure Publisher and Distributor on the same SQL Instance..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 7, 2012 at 2:08 pm
Just an additional thought. Depending on the version of SQL Server that you are running and the requirements around the data you may be able to run a mirror and have the users report off of a snapshot of the mirrored database (it really depends on whether the users require near real-time data)
February 7, 2012 at 3:10 pm
Have you clearly read the OP's requirement? He needs a mechanism to synchronize data from 3 client office databases to a centralized Server. In such a scenario how will Db Mirroring with db snapshot work?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 7, 2012 at 3:56 pm
Bru Medishetty (2/7/2012)
Have you clearly read the OP's requirement? He needs a mechanism to synchronize data from 3 client office databases to a centralized Server. In such a scenario how will Db Mirroring with db snapshot work?
I'm not seeing anything stating 3 client office databases to a centralized server.
February 7, 2012 at 5:06 pm
I agree with Nicholas, I don't see anything about centralization of data.
I am assuming this means db01 data moved to db02 for reporting. Is this real time? How much delay is acceptable (all solutions have some delay).
Mirroring + snapshots work, if your edition supports them. If not, depending on the "real" ness of the data and the tolerance for interruptions of the restores, log shipping might work.
The nice thing about log shipping is it gives you a consistent view and look at the data after every restore. Replication has data being moved over table by table, so it's possible that two reports run minutes apart on the secondary might end up being different. No different than your db01, but users should be aware. Sometimes they expect something different on db02.
February 7, 2012 at 5:21 pm
Nicholas Cain (2/7/2012)
Bru Medishetty (2/7/2012)
Have you clearly read the OP's requirement? He needs a mechanism to synchronize data from 3 client office databases to a centralized Server. In such a scenario how will Db Mirroring with db snapshot work?I'm not seeing anything stating 3 client office databases to a centralized server.
My mistake, completely oversaw the actual post. I was thinking about another thread (http://www.sqlservercentral.com/Forums/Topic1247807-391-1.aspx#bm1248187). Sorry guys....
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 8, 2012 at 7:52 am
Yes this is a two machine configuration. As of right now I do not have a third machine to be the distributor. Machine A is steadily using 77% of the resource monitor. 70% Maximum Frequency continuous for the Cpu usage. This would just be a Machine A to Machine B server to server. The data doesn't not change frequently. The users on Machine B would be looking to read data CST during business hours.
I appreciate all the of the different configurations. The database mirroring with a snapshot looks interesting. Log shipping might also be an option being that if the data only needs to be refreshed weekly. The developer is saying that the data is quite static. I'm still capturing more information to use the right option. Any suggestions as far as what other questions to ask to get the right option.
Jonathan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply