January 28, 2011 at 10:30 am
I am looking into offloading some of the work from our production SQL Server 2005 to a secondary server. Mirroring would not leave the database in read-only mode and log shipping would leave the databases in read only but kill transactions that are running whenever the log restores are taking place. I would like to avoid using replication. Is there any other option that would allow me to have a mirror that can be used to process data?
Thanks,
Eduardo
January 28, 2011 at 10:52 am
I've always used replication for that. Easy enough to set up and manage, and it works well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2011 at 2:03 pm
you could mirror the database and then take regular snapshots of the mirror to allow users to read data
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 30, 2011 at 4:21 pm
Why do you want to avoid replication? Seems like a push or pull transactional replication is what you are looking for.
January 31, 2011 at 5:15 am
It is what I have used in the past but the database is still in constant change and there is a lot of internal opposition to replication. I would not like to give them the opportunity to blame application performance issues on the database.
January 31, 2011 at 5:16 am
Wouldn't I need two sets of databases for that?
January 31, 2011 at 5:42 am
With replication you'll have the publisher and subscriber along with a distributor to keep track of things. Simplest is if any changes made to the subscriber don't have to be made to the publisher too. That's simple transactional replication. If you do, then it's either transactional replication with immediate or queued updates, peer-to-peer replication or merge replication.
I would suggest that you read up on the different types of replication before deciding.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 7:46 am
I think Perry has the best recommendation here - snapshots on the mirrored databases.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 31, 2011 at 7:51 am
What about stopping log shipping restores when processes are running and restoring them after they are complete?
January 31, 2011 at 11:05 am
TheSQLGuru (1/31/2011)
I think Perry has the best recommendation here - snapshots on the mirrored databases.
If the data is constantly changing as the OP stated, using snapshots on mirrored dbs would not be a good idea.
January 31, 2011 at 12:57 pm
nadabadan (1/31/2011)
If the data is constantly changing as the OP stated, using snapshots on mirrored dbs would not be a good idea.
the OP doesn't actually state that anywhere, but, it is a database system and likely to change at any point anyway. The OP is obviously keen to avoid replication and its pitfalls and is looking for another solution.
Eduardo Log shipping may still be viable for you, you could set the log file restores to occur out of hours. The real question is, how "up to the minute" would you like the data on the secondary database?
Provide these details and it would be much easier to advise a configuration
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 31, 2011 at 5:21 pm
Perry Whittle (1/31/2011)
nadabadan (1/31/2011)
If the data is constantly changing as the OP stated, using snapshots on mirrored dbs would not be a good idea.the OP doesn't actually state that anywhere, but, it is a database system and likely to change at any point anyway. The OP is obviously keen to avoid replication and its pitfalls and is looking for another solution.
Eduardo Log shipping may still be viable for you, you could set the log file restores to occur out of hours. The real question is, how "up to the minute" would you like the data on the secondary database?
Provide these details and it would be much easier to advise a configuration
You are right. I thought the OP was talking about data constantly changing when he wrote "It is what I have used in the past but the database is still in constant change...", but now it looks like he was just referring to the database design. Eduardo needs to give us more detail about what he is trying to accomplish. Because at this point mirroring with snapshots, log shipping or replication ( transactional or snapshot ) could be viable options.
Eduardo where is the secondary server going to be located ( in the next room or across the country )?
How reliable/fast is the connection between the servers?
How often is the primary written to and how current do you need the secondary to be?
February 1, 2011 at 5:21 am
I use SQLsafe for my backups and they suggested checking off the disconnect users when doing restores which will prevent the restore from stopping a running transaction but that would still force me to add delays to the processes to account for restores already running.
February 2, 2011 at 2:50 am
You will love SQL Server 2011 when it is released. One of the new features is the ability to have multiple targets for your mirroring, and all of those targets are available for querying. I think this will be a major driver for people to upgrade to SQL 2011.
See Brent's blog about this... http://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 2, 2011 at 8:50 am
I've been considering setting curfews for the extraction that takes the most time to run, which is OLAP. Since it starts at 23:00 and should be done within two hours, most of the time it only takes 30 minutes, I am thinking I can put a curfew on log shipping restored from that time to 01:00. The others I would probably have to modify the jobs to test for the database to be available before doing anything and make sure it doesn't run in the first five minutes of every hour because I am planning to have hourly restores instead of the 15 minutes restores that are the default.
Thank you all for your assistance
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply