October 4, 2009 at 7:39 pm
Right now we have backup/restore from live system to a Reporting only Database, to alleviate I/O to one server/DB all the time.
What are some good options/alternatives to this model?
This DB is 110GB. It takes about 45 minutes to backup/restore; we do it 3 times a day for this system. The backup is done to the network drive, that both the servers can see, and hence restore happens easily.
Thx for ur input.
Dan
October 4, 2009 at 11:28 pm
On one of our SQL2k systems we had log-shipping set up for our 1TB database to a read-only copy so users could run reports against that copy: more-or-less what you've got set up (although it sounds like you're using full backups).
When we migrated it to SQL2k5 we canned the log-shipped copy and instead enabled "read committed snapshot" on the database. Users now report directly against the live database even though it's grown to ~1.3TB, although the tempdb has blown out from ~100GB to ~300GB (at least some of that tempdb expansion is due to one bad report: we've never got around to optimising that).
An added benefit of snapshot locking is that it greatly reduced the number of deadlocks we are getting: from a few a day to less than a couple a fortnight.
October 5, 2009 at 6:25 am
Glenn
What about I/O? Do the users see slow responses? Why did you not consider a separate server or a different model like Snapshot replication etc.
I see you gained in deadlock issues. But what do you like to have or miss from your existing setup?
Ours is a heavy read reporting DB; hence we need to keep it separate.
Just trying to learn from other who have done it differently.
Thx
Dan
October 5, 2009 at 8:26 am
I'd ask how much data changes? And how often? You could use transactional replication, either on a schedule or continuous, to move data across. That would avoid the downtime with restoring.
October 5, 2009 at 9:11 am
I would think that your networking people are a bit upset with you 3 times per day. That is quite a load, performing a full backup and restoral across thenetwork. You do not say if your database is growing at all, or even if the users need all of the data for their reporrts.
We have numerous I/O intense databases and many power users who want to be able to perform reporting on up to the minute data. A lot of business decisions are made on this data at times hourly.
We are performing transactional replication on only the tables required for reports over to a reporting server. At times, we are also using an ETL tool to pull/push data from various servers and loading it into the reporting server. On the reporting server side, we can make use of performing agregations on some data in order to make reports more efficient. We use Crystal Reports for the majority of our reporting.
Doing this will eleminate your down time making your users happy as well as drastically reduce your network utilization making your networking people as well as other users happy. It also keeps your data up to date real time. (You did not say if real time is a requirement, but it is inferred by the number of times that you do a backup/restore.)
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
October 5, 2009 at 10:48 pm
If the data for both servers are stored on a SAN, then SAN Snapshots are the best in my option. Once setup, it only takes a minute or two to snapshot a tera-byte server and requires no downtime or even "slow time" on the production server. The reporting server will go down for the duration but it's a very short duration.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 6, 2009 at 5:48 am
Steve, it is an OLAP. So 200MB of bulk data a day, spread throughout, in chunks.
Jim, yes, it is growing!, and yes, 100s of users need reports everyday. The data does not have to be up-to-the-minutes, but fresh once in a few hours.
Both of you have pointed me to Transactional Replication. Generally speaking, does it well with OLAP?
October 6, 2009 at 5:49 am
Jeff
Does the DB folks setup the SAN Snapshots (if you have access to the drives where the mdf database is stored) or is it part of the network team's job?
This is not related to Glenn's thought of read committed snapshot, is it?
I do not know about SAN Snapshots, but is it like Database snapshots in SS2005?
http://www.sqlservercentral.com/articles/Administration/2733/
Thx
Dan.
October 6, 2009 at 7:36 am
A database snapshot is not like a SAN snapshot. Once the SAN snaps the disks, a complete, and separate copy is available on separate disks. That takes advantage of the SAN, and your SAN administrators should be able to help you here.
A read committed snapshot is an isolation level for your queries. The issue with log shipping is that when you restore the db, you need to disconnect users. Depending on your needs, that may or may not work.
Replication works in smaller chunks, essentially moving the data across as transactions from one system to another. That may or may not work as well. If not having a large chunk of data moved together, this can be an issue. In terms of OLAP, if these are still relational databases, the architecture is the same. The difference is how you may index and lay out files.
I think you might need to check on each of these technologies, spend a few hours reading on MSDN, SQLCAT.com, etc. Then make a smart decision. It sounds like the SAN snapshot would be great here if that works for you.
October 6, 2009 at 7:54 am
Steve
Thanks for the nice comparison of the different technologies that can help.
Does the SAN enabled for Snap feature, or does it take extra hardware/software to make it work?
Thanks again
Dan
October 17, 2009 at 8:03 pm
Does the SAN enabled for Snap feature, or does it take extra hardware/software to make it work?
Thanks again
Dan
Most of the SAN's are Snapshot enabled check with with your Network / Storage Engineer.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 18, 2009 at 10:24 am
If you are running Enterprise Edition on your reporting server, you can create a database mirror and a database snapshot off of the mirrored database.
Perform the db snapshot every 4/6/8 hours as needed.
However, if you have the ability to perform SAN snapshots - that is going to be the better option. Just be aware that some SAN's require the database files be closed before you can snap - others are integrated with SQL Server (using VDI) to freeze SQL Server and perform the snap. Your SAN vendor can help you out with that.
One other thing, if you do perform SAN snapshots - you might want to consider setting up the databases as read only copies. This will eliminate some locking and contention on the reporting server and could also give you an opportunity to scale out and use multiple instances of SQL Server to access those databases. Lookup scalable shared databases in Books Online (note: Enterprise Edition feature only).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 18, 2009 at 10:49 am
I believe the SAN snapshots we were doing operated kind of like transactional replication where the "clone" was always kept up to date in a parallel fashion. The production source database never needed to be "frozen" and suffered virtually no performance loss. It does take a wee bit of extra disk space though I thought it was very well worth it. We could do a "restore" on the reporting database as many times as we needed right in the middle of the day.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2009 at 10:57 am
Jeff - without knowing which SAN it was, it's hard to say what is actually possible. For example, I have two types of SAN's available. Netapp and HP EVA's - on the EVA's we can create snapshots easily enough but the database cannot be accessed when it is done (I believe the ability is there on later models, but as with most vendors it is an added cost to buy the additional software needed).
On the Netapp, we have the ability to create a SAN mirrored database and snapshots off that mirror. So, we could essentially do the same thing that you have outlined and it would work the same way.
If you need your system up 24/7 - and you have not invested in the utilities to allow for this, then are you truly a 24/7 shop?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 18, 2009 at 6:13 pm
Jeffrey Williams-493691 (10/18/2009)
Jeff - without knowing which SAN it was, it's hard to say what is actually possible.
Heh... Agreed... Everything is impossible if you don't know what the possibilities are. I was just stating what we did. It was a while back so I'm not 100% sure bit I believe the SAN was a Clarion. They did the same thing at the last job I left and the only thing I know about the SAN is that it wasn't a Clarion.
Excellent point on the 24/7 thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply