setting up non-operation database… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

setting up non-operation database…

Hi, currently I have only 1 database, where my operation users and non operation users will use the same source for their work. Operation users will input transactional data, while the non-operation users will use the data for analysis work. I know this is a bad combination as things that non-operation users draw can sometime be so large that it impacts the non-operation users. At such, I tried to setup another copy of the database on another server for the non-operation user to use. As I can’t figure out how to transport each day’s data from one database to another database, therefore I resort to backup the data on the operation database, and restore it on the non-operation database. But this is a lousy way, as every time I restore the database, the user setup in the database also changed and so many a time, the user will find it a nuisance as their passwords have been changed again… Is there a better way that I can do this?
You should look towards replication or log shipping. There are advantages and disadvantages to both, and in a textbook view of what ypu’ve described, you should use replication, but there are times when log shipping is a closer fit to what you want. HTH, Steve
If the current SQL server edition is Enterprise then you can easily go thru the LOG SHIPPING WIZARD to setup which will take care of the data between primary and secondary servers. If its not then no worries, even you can deploy your own log shipping from this link] and another link for your information. HTH Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.