Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
settai.murugan
Starting Member
2 Posts |
 Posted - 2014-10-10 : 02:41:43
|
| Hi Friends,I have joined a new company where SQL server is DB. As of the following is happening.Every day mid night thru an auto schedule, backup of entire DB is being created.In the early morning, a person will copy the backup file from DB server into Report server (for report processing)Then the copied DB will be restored in Report server.The above process is not right to my knowledge. I would like to do the following instead of entire DB only the incremental should be taken as backupThe same has to be synced with report server automatically (don't mind one day lag in report server. But for sure if the sync process can be done in better way and the lag is going to be less, I would love it)Both report server and DB server are in same location (3rd Parties Data center and not in our own premises)If the above is going to work successfully, I would like to extend the same logic to sync my Development server which is located in our office.Help me guys.Murugan |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-10 : 09:05:46
|
| Why not use SSIS to build an ETL job? No mucking about with backup/restore |
 |
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-10-10 : 10:40:37
|
| If users are not running query all day long and specifically run reports a particular period of time you can configure transaction log shipping. Make sure logs are not restored at time when reports run.If you have enterprise edition you can create snapshot and run reports on that. Although there are few disadvantages with snapshot. Read Typical uses of Snapshot http://technet.microsoft.com/en-us/library/ms175472%28v=sql.105%29.aspxReplication can also be configured but IMO Log shipping would be more favorable. If you are thinking of taking differential backup instead of full you can but I dont have idea about how backup policy is configured in your system.If backup size is less I dont find any issue with what currently is setup in your environmentHope this helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
settai.murugan
Starting Member
2 Posts |
Posted - 2014-10-10 : 12:13:28
|
| Thanks Shanky and Gbritton.Current DB size is 220 GB I believe. I don't find any problem in current setup. But I want to avoid manual intervention. Now one guy is doing all the things related to this. So it is person dependent. I want to avoid it. I can have one more person to have knowledge on it and handle the entire procedure in case of absence of first person.Am sure in current tech world whatever I want can be done easily. Am also new to SQL server. So need to study all solutions and find suitable one for our environment.I was going thru log shipping sometime back. Wherever I go it is detailed. If someone could help me crisply about what it is, steps to be followed and mainly does it involve any investment or recurring charges. Already I have two server. So I would like to know about cost other than server. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-10 : 13:42:18
|
| I still think you should do it with SSIS. use the slowly-changing dimension component for reference tables and insert new transaction rows based on a timestamp or id number or whatever you have available. Schedule the package to run in off hours so that it is ready for reporting the next day. |
|
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-10-11 : 06:20:58
|
| I was going thru log shipping sometime back. Wherever I go it is detailed. If someone could help me crisply about what it is, steps to be followed and mainly does it involve any investment or recurring charges. Already I have two server. So I would like to know about cost other than server.[/quote]There are lot of good articles one which explains it with Screnshots is http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/ Make sure you change frequency of back,copy and restore job as per needHope this helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
|
|
|
DB from Production to Report/Development server