March 25, 2011 at 9:54 am
Dear all,
i have SQL 2008R2 2-node cluster used for apps. New requirement is to create replication of database, 1 per hour, on separate db that will be used by reporting servers. That db will be mounted by another sql installation, not cluster. So, every hour replication will occur and reporting will be done on copy instead of live db.
Could u please advise me how to setup this solution, briefly, what is procedure and what tools to use, just in few steps? Since i am not sql administrator, i am infrastructure guy that admins sql cluster as a MSCS service, is it possible for me to setup this or it requires extensive sql language and scripting knowledge? Is it complicated or tools will do most for me.
Sorry but i don't know where to start, need a advice mostly 🙂
tnx
Srkey
March 25, 2011 at 10:16 am
Every hour? How big is the database?
Honestly I would look at transactional replication unless there is just a TON of data loading going on in the main database. That will keep your reporting database up to date continually with little latency between the two and you won't have to manage much. It does bring some complexities so, read up a bit on it to determine if this is the right solution for what you have.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 25, 2011 at 5:00 pm
If you have a SAN, many SANs come with a method to almost instantly duplicate data on a reporting server.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 1:25 am
We are waiting for NetApp that have snapshot manager for sql but i am not sure it will do all the work. I was thinking about sql transactional replication, i suppose that is right feature that i need for this? I hope there is not much sql language in this procedure..
Srdjan
March 28, 2011 at 2:41 am
if the secondary server database is purely reporting purpose then you can consider log shipping where you will be restoring the backup(full or log) with standby option which will leave your database as read purpose.
----------
Ashish
March 28, 2011 at 6:16 am
You can also use database mirroring to move the data to another server, then run an hourly database snapshot to report off of.
March 28, 2011 at 6:47 am
I would prefer Log SHipping in this case.
As the primary server is in Clustering mode and Secondary server is in stand-alone mode.
March 28, 2011 at 7:05 am
chetanr.jain (3/28/2011)
I would prefer Log SHipping in this case.As the primary server is in Clustering mode and Secondary server is in stand-alone mode.
If you had a SAN that would do that almost instantaneously for you, would you still prefer Log Shipping?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 7:41 am
srdjan.katic (3/28/2011)
We are waiting for NetApp that have snapshot manager for sql but i am not sure it will do all the work. I was thinking about sql transactional replication, i suppose that is right feature that i need for this? I hope there is not much sql language in this procedure..Srdjan
NetApp SnapManager can do all that work very handily and very quickly as Jeff pointed out. I would sincerely consider that as an option if you can really take the outage on the reporting server while the "swap" takes place.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 28, 2011 at 8:09 am
There is one potential issue since vmware will be hosting virtual Sql 2008 R2 cluster. For SQL cluster vmware has to deploy raw disk format instead of vmdk disk format. Simply put lot's of features will not be available since raw format means less flexibility when it comes to moving, replicating, cloning etc...so i am afraid that whole SAN+vmware solution will be inadequate for sql replication.
March 28, 2011 at 8:10 am
.
March 28, 2011 at 8:14 am
srdjan.katic (3/28/2011)
There is one potential issue since vmware will be hosting virtual Sql 2008 R2 cluster. For SQL cluster vmware has to deploy raw disk format instead of vmdk disk format. Simply put lot's of features will not be available since raw format means less flexibility when it comes to moving, replicating, cloning etc...so i am afraid that whole SAN+vmware solution will be inadequate for sql replication.
SQL Replication is within SQL Server so your platform will not impact that. As for SAN replication I'm sure that they will be able to handle the raw format as well. Best to talk with your SAN vendor / engineers on that though. SnapManager is not free, or cheap from what I recall.
As for the other SQL server options they should all support what you want to do. The transactional replication option will provide you with the highest uptime on the reporting server but there are other caveats associated with that. All should be read up on and pro's and con's considered for your situation. There really isn't a simple "this is the solution" answer here. A bunch of good options though. 😛
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 28, 2011 at 8:20 am
chetanr.jain (3/28/2011)
@Jeff,SAN replication always has edge over Log shipping if cost is not a factor.
I hope my statement is correct.
You're correct. I was just making sure that people know about the SAN option if they have a SAN. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 8:24 am
David Benoit (3/28/2011)
srdjan.katic (3/28/2011)
We are waiting for NetApp that have snapshot manager for sql but i am not sure it will do all the work. I was thinking about sql transactional replication, i suppose that is right feature that i need for this? I hope there is not much sql language in this procedure..Srdjan
NetApp SnapManager can do all that work very handily and very quickly as Jeff pointed out. I would sincerely consider that as an option if you can really take the outage on the reporting server while the "swap" takes place.
Thanks, David.
As a sidebar, not only is the method quick (just a minute or two), some of the SAN software (I'm thinking of EMC in particular) also repairs "damage" to the reporting server if someone writes to it (although a lot of folks set it up as a Read Only DB on the server).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply