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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 DB from Production to Report/Development server

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 backup
The 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
Go to Top of Page

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.aspx

Replication 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 environment

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 need

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
   

- Advertisement -