January 24, 2012 at 4:18 am
We want to use Materialized views on our Live DB with excesive updates and deletes. But I have read from forum that its not recomended on busy database. So I have decided to do merge replication and shift data from Live to Replicated DB at night. Then We will create indexed view and more indexes on the replicated DB. Because this DB will not used for updation and delete. So we will use this DB for only Reporting purpose with One Day delay. One Day delay is accepted in our organization.
I have this scenario in my mind I want your expert opions on this matter. What are pros and cons and any new and better idea.
Thanks in advance.
January 24, 2012 at 5:10 am
Based on your description, Merge replication doesn't sound like the correct option. Merge replication is used when you want changes made to the Subscriber database(s) to be "merged" back to the Publisher. For a reporting-only subscriber database, that shouldn't be necessary. It sounds like you want either Snapshot or Transactional replication. You could also set up log shipping or mirroring to accomplish this. Or, you could develop a separate "data warehouse" database, with a schema specifically designed for reporting purposes, and populate it via regularly scheduled SQL jobs, SSIS packages, or some other ETL tool.
The correct choice for you really depends on a number of factors. How rapidly does the data change? How large is the database? What resources do you have available (time, money, hardware, DBA availability)?
January 24, 2012 at 11:22 pm
Thanks for your time.
Actually we have replicated DB with Transactional Replication But sometime When User print reports with current date then our data did not synchronized. At the same time we have decided to have Indexed view to make fast retreival of reports. I think If we create extra indexes and create indexed views on the transactional replicated DB then it can create overhead on the Replicated server because in transactionl replication every transation is pucshed to subscriber.So Subscriber would be busy to update indexed view as well.
I am not sure, am I right or not?
So I think that snapshot replicated DB would be better choice to configure its snapshot at at mid night and we will create indexed views on that as well.We can ask the user to print reports one day back.
For Dataware house ,We dont have much experties right now.
Please tell me wether we should create Indexed view on Transactional Replicated DB or On Snapshot Replicated DB.
Thanks
January 25, 2012 at 10:59 pm
Well, I need to populate 2 different servers with data from my warehouse everyday. I have 6 tables all of them about 10 million rows, and what Im currently doing is to truncate the 2 databases and then to do a bulk insert into the other databases. For all of this it takes about 10 mins per server, so overall not a bad solution as I didnt want to start messing around with replication and things I dont know anything about at this stage.
I do the inserts via SSIS so I can first check if the servers are up and if there is in actual fact new data to be copied across.
As said, might not be "right" but it works for me
January 25, 2012 at 11:37 pm
I have more than 272 tables in Databse and with some huge tables. Is your solution would be a better choice.
Please share me some code if poasible or Explain me more.
Thanks
January 26, 2012 at 12:05 am
Sounds to me like a solution using CDC (Change data capture) or CT (Change Tracking) used in conjuction with ssis would be a good alternative here.
January 27, 2012 at 5:07 am
We faced the same situation a while ago. The solution we used was to restore the nightly full backup of the OLTP database into a separate reporting database, which was then switched into read-only mode. Making the reporting database read-only stops anyone inadvertently updating it, and also improves performance because SQL Server doesn't need to take out locks.
We created an SSIS package to automate this, but you should also be able to do this using T-SQL.
_______________________________________________________________
Website : www.sqlmatters.com
January 27, 2012 at 5:51 am
azhar.iqbal499 (1/24/2012)
We want to use Materialized views on our Live DB with excesive updates and deletes. But I have read from forum that its not recomended on busy database. So I have decided to do merge replication and shift data from Live to Replicated DB at night. Then We will create indexed view and more indexes on the replicated DB. Because this DB will not used for updation and delete. So we will use this DB for only Reporting purpose with One Day delay. One Day delay is accepted in our organization.I have this scenario in my mind I want your expert opions on this matter. What are pros and cons and any new and better idea.
How long it takes to restore a full backup of your production database? how big is it?
A straight forward solution for a Reporting Database that is one day behind Production is to "clone" it at night then build the additional indexes that would serve Reporting. The easiest way to "clone" it is to restore a full backup of Production as Reporting.
Just for your reference, "Materialized Views" is an Oracle concept somewhat matched by SQL Server's "Indexed Views".
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 27, 2012 at 6:05 am
You might also consider Log Shipping the database to another server and scheduling the log restores to run nightly. Another option is to mirror the database to another server and create a database snapshot then create a fresh one each night. Using one of these options depends on how they might affect performance. I've set up both in different situations and it just depends on what you need and what you (or the system) can support. 🙂
January 29, 2012 at 10:08 pm
We have Backup of 11GB and it takes alsmost 20min to restore. Please share me how to clone back up using SSIS package.
Thanks
January 30, 2012 at 3:34 am
azhar.iqbal499 (1/29/2012)
We have Backup of 11GB and it takes alsmost 20min to restore. Please share me how to clone back up using SSIS package.Thanks
If you want to clone using the latest backup file, I'd recommend doing this using T-SQL as this is simpler for what you've outlined, though of course this could be rolled into an SSIS package.
The likely steps are :
1. Work out the file name of the latest full backup by querying the backupmediafamily and backupset tables in the msdb database.
2. Restore the backup file into your reporting database (overwriting any existing database).
3. Apply any extra permissions required for the reporting database. Also add any extra indexes for reporting (as another contributor mentioned). You can also change from full recovery to simple recovery and shrink the transaction log to reduce disk space.
4. Set the database to READ_ONLY.
Books Online should provide the detail of how to implement each step.
_______________________________________________________________
Website : www.sqlmatters.com
January 30, 2012 at 3:47 am
Thanks
One more Question in my mind. When new Database backup would be restored then, would this create problem for Indexed view or not? Because Indexed views would be recalculated on every restore.
Actually I want to use indexed views only on Separate DB on which I will restore Backup.
In this case I have to recreate Indexes and Indexed view Everytime whenever I restore the DB with overwrite options.
January 30, 2012 at 5:09 am
There shouldn't be any extra issues around indexed views. If you want to create an indexed view on the reporting database then you can do so immediately after the restore.
_______________________________________________________________
Website : www.sqlmatters.com
January 30, 2012 at 5:49 am
So total time for this operation would be SUM of following:
• Time to Backup at source server
• Delay between backup & copy
• Time to copy the database backups to target server
• Delay between copy & restore
• Time to restore backups at target server
• Delay between restore & index view creation
• Time to build index view (clustered index)
You may optionally add 2 hrs for unexpected issues & troubleshooting
January 30, 2012 at 6:39 am
azhar.iqbal499 (1/30/2012)
ThanksOne more Question in my mind. When new Database backup would be restored then, would this create problem for Indexed view or not? Because Indexed views would be recalculated on every restore.
Actually I want to use indexed views only on Separate DB on which I will restore Backup.
In this case I have to recreate Indexes and Indexed view Everytime whenever I restore the DB with overwrite options.
Indexes that existed in the base OLTP database will be fine after restore as Reporting database.
New indexes and indexes views needed exclusively for reporting have to be recreated after restore - this can be scripted and executed as part of the "reporting refresh" job. For an 11 Gig database this shouldn't take too long.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply