August 14, 2014 at 10:08 am
We all back up the databases, but what about all the other stuff? Like what would happen if the server itself crashed and had to be rebuilt - you'd lose all the jobs, settings, etc...
How do realworld DBA's handle this? Do they "ghost" the whole server? What are the options?
Thanks!
August 15, 2014 at 2:23 am
backup your system dbs
backup your server
August 15, 2014 at 2:24 am
Backup the system databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2014 at 5:55 am
After you backup the system databases, get everything into source control. All the jobs can be scripted out, put that into source control. Your databases and their code & structures should be in source control too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 15, 2014 at 7:16 am
Our business has 2 data centers. We replicate every single backup (including the system DBs) to the other data center. If we lose the server, there are two places we can find our data. If we lose the data center, there is another location we can find our data.
EDIT: I believe the server admin also replicate the OS backups to the other data center as well, but I'm not 100% on this. The OS backups contain all the other non-SQL stuff that is just as important as the databases.
August 15, 2014 at 7:31 am
In addition to backing up user databases, transactionally replicating user databases (my least favorite DR approach), and backing up system databases, I also periodically script out server configuration, database schema, jobs, logins, users, roles, permissions, and I backup certificates and errorlogs. I rely upon the VM administrator to backup the OS (also not a favorite approach). Finally, I regularly practice (in DR) putting it all back together again. On the DR server, I generate restore statements directly from the backup files (I do not rely on production's msdb) and I run checkdb upon the restored backups.
August 15, 2014 at 8:52 am
Most importantly, coordinate your DR plan with the application side. Maybe your database server crashes and you want to fail over to the DR site, but the application server is fine and performance is terrible across the WAN. Similarly, the application servers may have to move, and now you have to do the same thing on the DB side. I've had both happen to me.
Also, if you fail over to a DR server, external jobs won't work. At that point, you may/will have to have the DNS entries for your sever changed to pint to the DR server. One shop I worked at did this as the standard for DR. At least in SQL Server 2014, SSIS packages all won't have to be modified
In a large shop, there will likely be a DR team that takes care of all of this. In a small to medium size shop, the DBA could be a critical player in DR situations.
August 15, 2014 at 8:59 am
lptech (8/15/2014)
At least in SQL Server 2014, SSIS packages all won't have to be modified
YIKES! I forgot about mentioning this. NAS shares, SSIS packages saved on the File level (well, heck, even backups of those saved in SQL Server), and XML config files / custom DLLs should also be backed up and made available to any DR location. Don't forget anything related to SSRS or SSAS either that might be "outside" the databases.
It says something about our mindsets that most of us forgot this "minor" bit of backup necessity.
August 18, 2014 at 5:57 am
Thanks everyone - unfortunately, we don't have a DR site, or even a DR server...we have a 4 hour response time support to get the server fixed if it crashes - I know, I know, this is not a great solution. But it's what I'm stuck with.
We do backup all the databases, including the master, both to disk and then copied to another backup server.
August 18, 2014 at 6:03 am
dstoltz 35443 (8/18/2014)
Thanks everyone - unfortunately, we don't have a DR site, or even a DR server...we have a 4 hour response time support to get the server fixed if it crashes - I know, I know, this is not a great solution. But it's what I'm stuck with.We do backup all the databases, including the master, both to disk and then copied to another backup server.
I think this would be the time to advocate for a DR server or a DR site. Remind them that in the case of a building fire, tornado, or server theft (it does happen) that 4 hours is an unreasonable response time without access to a DR server / Site.
Let me ask you more questions. Where are all these backups being kept? On the server drives? Onsite or offsite? Is there any possibility that a building fire or flood or other disaster would wipe out even your backups?
August 18, 2014 at 6:18 am
Hi Brandie -
The initial backups are done with Red-Gate SQL Backup, and are on the server drive, but copied off to a different server...I'll have to ask the network team where it actually is. In any case, I will be advocating for a more robust backup solution.
August 18, 2014 at 6:23 am
Copied is good. Let us know if you need any additional fodder to help with your case for a more robust solution.
August 18, 2014 at 6:31 am
dstoltz 35443 (8/18/2014)
Thanks everyone - unfortunately, we don't have a DR site, or even a DR server...we have a 4 hour response time support to get the server fixed if it crashes - I know, I know, this is not a great solution. But it's what I'm stuck with.We do backup all the databases, including the master, both to disk and then copied to another backup server.
Take a look at the backup time for your databases. Assume at least that much time to restore (probably longer). If the server went down, could you meet the four hour window? If not, you need to talk to the business and explain the laws of physics to them. "It takes us 8 hours to backup the databases. We can't possibly restore them faster than 8 hours because physics. So, want to talk about that secondary server now?" If cost is a major factor, I'd suggest looking at cloud services as a possibility. Azure or AWS are great alternatives to setting up your own secondary site.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 18, 2014 at 6:46 am
The 4 hour response time I mentioned was actually for the hardware support to respond with new hardware if needed, to get the server operational. Restore times/recovery would be on top of that.
Thanks!
August 18, 2014 at 7:01 am
dstoltz 35443 (8/18/2014)
The 4 hour response time I mentioned was actually for the hardware support to respond with new hardware if needed, to get the server operational. Restore times/recovery would be on top of that.Thanks!
To quote the illustrious philosopher, Emily Litella, "Never mind."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply