March 28, 2010 at 6:05 pm
I've a fairly large OLTP db and I need to create some indexed views for reporting. Trouble is, I also need this db updated on a nightly, maybe weekly, schedule.
Currently log shipping for DR and mirror/SS for reporting, which is working just fine, but our report writers are now needing to report against a 15million+ row table - so I'm thinking some indexed views are in order.
I've tried using the database copy wizard before, however I run into issues when things are not created in the correct order, I'm also thinking all the timestamp fields are going to be an issue (350+ tables).
Anyone found an approach for this sort of thing without writing a custom script to copy only non-auto value fields? Even that would be quite the job to run on a nightly basis!
March 29, 2010 at 1:24 am
Have you considered snapshot or transactional replication for the reporting database?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 29, 2010 at 4:10 am
is there any issue in creating indexed views?
Why are u using copy database wizard?
Please explain.
March 29, 2010 at 6:10 am
Why not just do a restore-from-backup to the reporting instance and create the indexed views as part of the job that auto-restores for you?
EDIT: Then add specific perms for the stuff you want the users to see. Make that a separate job step.
March 30, 2010 at 10:08 pm
is there any issue in creating indexed views?
Why are u using copy database wizard?
Please explain.
Indexed views can only be created on writable databases and log shipping/mirroring doesn't allow that scenario.
I am trying to steer clear of creating the indexed views in the produciton database as we have a couple of inefficient queries doing inserts on the larger tables (external providers so I've not control over them)
The "copy database wizard" use was an attempt with another database of a similar structure and failed when things were not copied in the correct order and I figured I'd run into the same issue here!
Have you considered snapshot or transactional replication for the reporting database?
the application has a lot of DML going on in there and replication is seen as quite the administration overhead
Why not just do a restore-from-backup to the reporting instance and create the indexed views as part of the job that auto-restores for you?
EDIT: Then add specific perms for the stuff you want the users to see. Make that a separate job step.
this is my fall back position - which looks likely to get a run
Thanks guys.
March 31, 2010 at 4:20 am
Glad we could help. BTW, setting up a restore as an auto-job is probably going to be the hardest part. Once you have it set up, you can pretty much ignore it until it fails because the backup file from the previous night didn't run or something.
Some advice on how to setup the job (distinct job steps):
1) kill all users
2) Drop or rename old database & DB data / log files
3) Restore new database - you can use a UNC to link to your production backup instead of moving the file from one server to another
4) Change DB owner to non-user account
5) Set DB recovery to SIMPLE
6) Fix DB Users (cursor for sp_change_users_login) and set any specific perms for this version of the DB
7) Encrypt PII & set any production email addys stored in DB to test email addys so no one gets emails they shouldn't
It may seem overly complicated, but it's a solid job philosophy for this sort of thing.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply