May 18, 2011 at 9:28 am
I'm just stepping into a new environment whose database architecture and environment leaves a lot to be desired. They have one DB server that handles all of their site's ecommerce functionality and receives between 15k-20k inserts per hour. Vital to the business are the reports that are generated from this data. Previously, they had been creating backups of all of the DB's on this server, file copying them over to a server that they've set up for reports, and then restoring them there. This entire process would take between 7-8 hours a night.
While replication would seem like a decent endpoint for replacing this horrid process, they're not quite ready to take that step yet, so I set up log-shipping to the report server, and then left the DB(s) in read-only mode so that reports could query said data. This alleviated a lot of the headaches that were presenting when I first got here, at least around reports.
I've recently been approached by a couple report designers who are complaining that their queries are taking a long time to run...this isn't a new problem, they've just never had anyone to ask about this before. In evaluating their queries, it is obvious that some index creation is definitely in order. Since the report databases are in read-only mode, I can't create the indexes there, at least not through any methods with which I'm familiar. Since the production databases don't have any fail-overs, and must remain online, I can't create the indexes there, lest table locking occur and we lose a good deal of business.
I had initially thought that I could perhaps briefly revert back to their old method of transferring/restoring a backup onto the report server, creating the index there, then re-configuring log-shipping, saying that the database was already initialized...however, a) I don't know for sure that the indexes would stay (though I can't see why not) and b) because of the lengthiness of the backup/restore process, they'd be missing 5-6 hours of report data. That might be acceptable to them in the end, if we were able to get the reports to run more expediently.
Thoughts/Ideas?
Thanks in advance..
May 18, 2011 at 9:36 am
Is there anyway to add 2 steps to that process?
Transfer the fulls backups and start the restore.
Now once you're close to being done on the restore start a diff backup... move and restore.
Then finally take a tail log (but without the NORECOVERY option obviously).
Then restore that on the new server.
In that case, you'd lose only minutes of data rather than half day.
I have no experience with the rest of the question so I can't help there... however I'm really intrigued by what's possible!!! Could use that system here in the near future.
May 18, 2011 at 9:42 am
coronaride
when using log shipping, all modifications to the secondary database must be applied first to the primary, including indexes, adding users, etc.
Replication would probably be a better option but this itself has it's own pros\cons.
Is it really not possible to create the indexes on the primary server are they really that large
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 18, 2011 at 9:49 am
there's no way to have different indexing in a log shipped environment.
15k rows an hour isn't particually a heavy load, depending upon your hardware of course - benchmarking tests I use insert 4 million rows in about 15 mins on a reasonable box.
Index build/rebuilds are tricky, I have a 7 x 24 system so I appreciate the issue. Adding an index doesn't always cause blocking - rebuilding an index often does. Again this does depend upon your table sizes and hardware.
I'd say that you should pick a quiet time ( there must be a time quieter than others ? ) and build your indexes then, one at a time and monitor blocking whilst it builds.
Partitioning helps with index rebuilds on large tables.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 18, 2011 at 10:26 am
CREATE INDEX with ONLINE?
You can't restore a backup onto reporting, create the indexes and then start log shipping. The log restores will fail saying there are no files ready to roll forward (because the DB had to be recovered to create the indexes)
If you need different indexes on prod and reporting, then replication is the way to go.
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
May 18, 2011 at 10:33 am
@ninja's - thanks again for another good suggestion. Just spoke with the business owners and they would rather have sluggish reports than miss data for a brief period of time. Oh well 😛
@perry - I agree, replication definitely has its pro's and con's, but this seems to be a perfect fit for it. That's my end goal for the reporting environment.
@colin - Unfortunately, the crappy part of this is that this box is a single point of failure - it has no backup whatsoever. I just can't risk the potential of the database being inaccessible for longer than 2-3 minutes, even during our off-peak hours...and even then, I doubt I could get management approval for it.
Anyhow, thanks again, folks, for the suggestions and feedback. I just need to push forward with a proper rearchitecture of the environment.
May 18, 2011 at 10:36 am
@Gila - I had thought of that and completely forgot to ask - will log-shipping transfer database objects as well or is it only data records after the initialization?
May 18, 2011 at 10:38 am
Everything that gets logged (and that's every single change of any form within the database) is transfered by log shipping.
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
May 18, 2011 at 10:50 am
Thanks. In theory, it sounded like it would work.
May 18, 2011 at 10:53 am
coronaride (5/18/2011)
Thanks. In theory, it sounded like it would work.
There's nothing like testing it for knowing for sure !
May 18, 2011 at 10:58 am
Egads - on a production system? 🙂 No, you're right. Should've added a harmless sproc or something.
May 18, 2011 at 11:00 am
coronaride (5/18/2011)
Egads - on a production system? 🙂
No. In a lab environment (and 2 instances installed on your local machine can be a lab environment)
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
May 18, 2011 at 1:19 pm
umm - the whole point about log shipping is that it copies everything, that's why it's the most simple but effective way to safeguard an environment, not least because every backup is restored e.g. the only good backup is one that's been successfully restored. Just like replication it'll faithfully drop an object which has been dropped by mistake but at least you can wind it back if required.
As to the issue of the indexes - at some point clients/businesses have to be stood up to - if you think seriously about this - you need indexes to improve reporting - you can't have the same people say no - there has to be give and take.
I sort of have a simialr issue with needing more storage for a server - I have three copies of a production database on a server ( for training ) very shortly I will not have enough room to restore all three copies, so one will have to go if I don't get more storage. I've got two sides, one wanting databases, one not wanting to buy additional storage - it's a situation that cannot continue and one side or the other will have to give. Your situation is the same so the business/those in charge will have to jump one way or another.
So how do you cope with index rebuilds, backups and consistancy checks just now? If you rebuild indexes then adding will be less invasive. Sorry to go on but I hate these somewhat silly scenarios being imposed.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply