April 16, 2008 at 12:34 pm
I have 2 sets of database one for Prod and other for reporting purpose
marketing
marketing_Report
I want my reporting database (marketing_report) to be sync everynight with production (marketing) they both are on same server. I do not want to use replication. What is the best method to do. I want to automate this on daily basis.
I can do backup and restore but is there any other way to do?
April 16, 2008 at 1:30 pm
It is going to depend on your exact requirements. Does the report database have schema specifically for the reports? It would be common to want different indexes, stored procedures and views in this database.
If that is the case, you would need to build something to either clear the data and copy fresh data or make the necessary updates. SSIS would be well suited for this purpose, or since it is all on one server, you may want to use a group of T-SQL stored procedures. I think I would lean toward SSIS making the assumption that eventually the databases would be on different servers and I would want to avoid using linked servers at that point.
If they will have the same structure, why have two databases? If you are trying to have a point-in-time version of the database, you may want to look into database snapshots.
If you are trying to offload work, having them on the same server will only help with resource locking (and with snapshot isolation you should be able to avoid anyway). However, if this is what you are trying to do, if you are already doing a backup to disk on the server every night, restoring the backup would probably be the least-intrusive solution to your existing processes and give you the benefit of testing your backup daily. You could also use log-shipping to get some built-in tools for this type of solution.
If you do a restore operation, make sure you have a way to ensure nothing is connected to the database or you will have someone leave Management Studio open on their desktop overnight and break your reporting solution.
April 16, 2008 at 4:52 pm
Why don't you want to do Backup/Restore? It is probably your best choice.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 17, 2008 at 5:32 am
Points worth noting -
For Backup/Restore
1> As the databases are on the same server, the necessity for moving data is not there. But, in future, if you plan to move the secondary database to any other system, network issues effect the process.
2> Each time you restore, scripts has to be run to take care of orphaned users.
For Replication
1> For transactional replication only the changes are migrated
2> All the tables for replications have to have a primary key.
3> You can have reporting services running on the replicated database
For Log shipping
1> Only the change move through the network (if on separate servers)
2> Reporting services cannot be setup
Copy database
1> Not recommended
There are other techniques using scripts and logic......which are tidious, time taking and needs thorough testing, but worthy enough
SP + SQL Job
Trigger
Hope this helps......
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 17, 2008 at 7:27 am
Thanks all for your valuable information.
What about Snapshot is it worth using that, how does it work? I need this basically for reporting purpose that to on same server. I mean need to generate report for high level people.
Thanks
Nita
April 17, 2008 at 7:33 am
Nita
There's plenty of information on database snapshots in Books Online. Have a read through that, and post again if anything doesn't make sense to you.
John
April 17, 2008 at 7:36 am
"Snapshot Replication" makes a copy of the entire database on a scheduled basis. This may be an option if you are trying to make a second database just for your reporting.
"Database Snapshot" is not actually a copy of a database, it is a collection of the database pages before they are modified - basically giving you the ability to look at what your database looked at when the snapshot was taken. This would be useful if you wanted to ensure re-running reports would always yield the same results.
I think this thread has illustrated that there are a bunch of options. You should get the requirements together before choosing. Determine how much latency is acceptable, if the users expect re-running reports throughout the day will give the same results, if your reporting database schema needs to be different than that of your OLTP system, how and how much the OLTP system and reporting system will conflict for SQL and hardware resources. Then you need to look at the available options and try to find the peg that best fits the hole for your situation. Take the time to do some testing and be sure to think about the future a bit.
April 17, 2008 at 7:50 am
If your intention is to setup a report database, I will strongly suggest the SQL Server Reporting Services....you will be overwhelmed by the features and things you are able to do with it.....and it is really simple.....just download a white paper or tutorial on reporting services and get going.....Best of luck
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
August 3, 2010 at 7:15 am
What version Of SQL Server are you ruinning? If you are using 2008 think about useing the MERGE command and SSIS to move data from your one server to the other. MERGE is a new feature and will allow you to insert, update and delete all in one command as long as you have good primary keys
August 4, 2010 at 1:37 am
If the database size is small than you can go with the 'Snapshot Replication' to achieve your goal. Which is also easy to maintain compare to other soultions.
Backup and restore restore will fail if some of the users are connected; else you need to kill the users and don't know howlong it will take? In fact, you may not receive any orphan user issue as you are restoring it on the same instance.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
August 4, 2010 at 2:30 am
Hi,
I've had the same problem a year ago when a man from my team was running many reports on the production database and there were a lot of problems because of this.
At that moment, the quickest and best way to solve this issue was to make a job to restore the production database every night on a report database (because I ran full backups anyway after 00:00).
In a few words I would say that I don't know that was the best method, but it worked and solved those issues.
Wish you good ideas! 🙂
Andreea
August 4, 2010 at 11:42 am
I am with Barry on this. Sounds like you have simple requirements and a simple solution is best. Restore backups. Or perhaps do a straight-forward implementation of log-shippping.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 10, 2011 at 7:14 am
If we set the Logshipping on the same Instance...is there any problem...
This post is old..Neeta what procedure u r using now.
Thanks
December 1, 2011 at 2:38 pm
I am working on same scenario you have explained and so far i found two best options
- Backup / Restore
- Transactional Replication
But for now i am going to do Backup and Restore to keep my Reporting Server updates close to production.
What did you choose ?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply