| Author |
Topic |
|
lkerznowski
Starting Member
22 Posts |
 Posted - 2014-07-11 : 14:19:34
|
| Hello. I have a small amount of SQL knowledge, and my current employment needs help as we wish to set up transaction log shipping and for a live backup of our database. The business that I work for has five branches across the state, with the main servers and information contained on the servers here. Our main software uses an SQL database to store the information. Currently, we do have a backup already in place which replicates the information in case of data loss. However, we wish to set up a second somewhat-live database with the same information. Reasons being:1) In the event our main server goes down for any reason, we want to ability to shift the software to continue using this backup so that business can continue.2) One of our IT members runs "reports" based on the information, and they tend to bog down the system and lock things up when running them. He can run these reports on the second database which should keep the main on functional.From what I looked up, transaction log shipping is what I think that we are looking for. We need a somewhat up-to-date secondary database for quick transitions in emergencies, and for the report running.Right now, this is what I have set up (these are all test-servers at the moment; we're waiting until we can get the test things working before moving to the live server):The original database has been set to Full Backup, and an initial "Full" backup was created and moved to the second server's local files. The folders and permissions are all set, so that the first SQL server can communicate with the second with no problems transferring files. The second database has been restored with the initial backup, so a full copy is complete.The schedule we wish to do would be:- Monday through Friday; run transaction logs from 7am to 9/10pm every 15 or 30 minutes- Every weeknight and Sunday night; create a "Differential" backup- Every Saturday a 9pm; perform a "Full" backupI think this is how it's somewhat supposed to be to make them work.I set up transaction log shipping on the main database already [I think]. It is set to occur every M/T/W/R/F from 7am to 10pm every 30 minutes. They are set to go to the second server's local drive. Copy files has been set with the same settings; Restore Logs has the same settings as well, along with NORECOVERY restoring options. Is this correct?I guess my main questions fall down to these:1) Is transaction log shipping what I'm looking for to keep a relativity up-to-date copy of a data base that is "functioning" and can be set to our live server in an emergency situation?2) Is the transaction log shipping settings correct? I feel like having them all say the same time frame values when they are saving/copying/restoring separately is not correct.3) How do I go about doing the Diff / Full restores? I know how to create the management plan to DO the backup on schedule, but how can I have the second database restore itself in a somewhat live manner against these. We are open from 7am-8pm so the backup things happen after hours. My concern is that the actual backup has to finish before it can restore on the second; how does that timing work?Please let me know if I am on the right track with this all. Again, I have tinker around very little with SQl so far. I'm aware that it is something I will be learning in this field of work, I have just not gotten there yet. Thank you! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-11 : 15:08:37
|
| For the IT member that runs reports, will he mind be disconnected every 15/30 minutes when the tlog restore occurs?What edition of SQL Server are you using? I don't like the backup schedule being proposed. I would do tlogs every 15/30 minutes, regardless of time of day. I would also do diffs and fulls more frequently. If you only want to do a full once a week, then perform daily or every other day diffs. A diff one day after the full and then not doing another diff seems like you are rolling the dice as you are relying on the tlog backup chain.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
lkerznowski
Starting Member
22 Posts |
Posted - 2014-07-11 : 15:24:05
|
| The reports are not constant and only need to be run at certain points in the month, and I believe* they run under 30 minutes, so that should not be an issue.Windows Server 2008 R2 EnterpriseMicrosoft SQL Server 2012I was only proposing the Diff's due to time they take vs open hours. After 8pm/9pm there will almost never be anything changing in the table, so I assumed that would a good time to start a little after to be sure. Running a full backup & having it transferred to the second server's local files takes ~5/6 hours, and then the restoration takes about an hour if I recall correctly. I assumed this all had to be before hours resumed again and changes would start happening? I was only doing diff's since they would take less time to create and restore with. If a Full can be completed in that time I should just do those then I'm guessing? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-11 : 15:41:48
|
| I need to know what edition of SQL Server you are using. Show me the output of SELECT @ @VERSION (remove the extra space, run in SSMS).Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-11 : 15:45:47
|
| Sorry I missed that you were proposing doing diffs every weeknight and on Sunday. I read too fast and skipped over the weeknight part.Backups should be able to run during peak times as long as you don't have severe hardware issues (CPU/IO). But I do recommend fulls to run during non-peak. My preferred schedule is daily fulls, diffs 12 hours later and then tlog backups every 15 minutes. That diff runs during peak times and is not noticed on my critical system that has 3000-8000 batch requests per second during peak times.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
lkerznowski
Starting Member
22 Posts |
Posted - 2014-07-11 : 16:01:22
|
| My apologies:Microsoft SQL Server 2012 - 11.0.2218.0 (x64)Yes, we were planning on SOME form of backup every night. I assumed the time would be an issue, which I now realize it should not. I would have no problem performing Full's each night after hours. I can run a Diff during the day as well. I'm guessing that is to cut down on the log size which I heard can be an issue.I guess my next question would be how to set up the restoration of the backups. From what I understand, the transaction logs are taken care of when I was setting up them already on the create/move/restore options. What do I do for the backups?I know how to create a plan which can run the backups when needed and move them to the other server, but what do I need to do to get the restoration to actually happen on the second server? The first time I had to manually load it as the database was being created, but I'm guess there's a way to have the backup restore after being transferred.Again, I'm really not strong with actual SQL commands and such yet. I've been working with the interface and SSMS a lot recently, which from what I've searched so far, this part requires some scripting and such to work like I would like? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-11 : 18:03:13
|
quote:
Microsoft SQL Server 2012 - 11.0.2218.0 (x64)
That doesn't tell me the edition, that's just the version. I need to know which edition, such as Standard, Enterprise, and specifically for SQL Server (not Windows).Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
lkerznowski
Starting Member
22 Posts |
Posted - 2014-07-14 : 08:18:39
|
| Microsoft SQL Server 2012 - 11.0.2218.0 (X64) " Jun 12 2012 13:05:25 "" Copyright (c) Microsoft Corporation"" Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)"My apologies, I did not fully understand what you needed to know. That should be it. I just copied the whole thing. |
|
|
|
|
Need help with "Log Shipping" and backups