August 29, 2012 at 5:02 am
Good afternoon,
I have the following setup:
1) 5 SQL Server instances (within the trusted network)
DBSERVER01 – 62 DBs (58 SIMPLE, 4 FULL)
DBSERVER02 – 41 DBs (41 SIMPLE)
DBSERVER03 – 39 DBs (39 SIMPLE)
DBSERVER04 – 31 DBs (30 SIMPLE, 1 FULL)
DBSERVER05 – 28 DBs (16 SIMPLE, 12 FULL)
2) 3 SQL Server instances (in the DMZ)
DBSERVER06 – 23 DBs (13 SIMPLE, 10 FULL)
DBSERVER07 – 14 DBs (11 SIMPLE, 3 FULL)
DBSERVER08 – 11 DBs (6 SIMPLE, 5 FULL)
Current backup process:
There exists two SQL Jobs on each SQL Server instance.
1) FULL Backup – this job is manually started every fortnight by the IT Manager. This executes a stored procedure which loops through all databases (except for Master and tempdb).
2) Differential Backup – this job is scheduled to run every evening and makes a differential back of all databases except for (master and tempdb)
DBSERVER01 to DBSERVER05
These hold staging databases used to load source files and various updates and inserts. There are no applications that use these databases and it’s normally one user working on one database.
DBSERVER06 to DBSERVER08
These hold databases used by Single Customer View applications. These are accessed via a web front end by our customers. Some of the databases are used purely for reporting purposes but some customers do make some changes to the data (maybe 20-30 changes per day).
It’s a simple setup but now I have been asked to come up with a new backup plan and also automate the backup process and generate some reporting mechanism.
My approach to solving this problem:
This is what I am planning to include in my proposal to management. If anyone can comment then I’d be eternally grateful!
1) For DBSERVER01-05 we need to establish the appropriate recovery model for all databases. My guess is that the dozen databases in FULL recovery model were created that way because model is set to FULL and this option wasn’t updated after the DBs were created.
2) For DBSERVER06-DBSERVER08 we need to look at each customer database and establish the appropriate recovery model based on the below:
How much data can be lost? A day, one hour, a week, none
a) If less than a day or none then FULL
b) If more than a day then SIMPLE
What kind of processing occurs? Transaction, batch load, reporting only, a combination
a) If transaction or a combination then FULL.
b) If Reporting then SIMPLE
This data be easily recreated if there is a failure or it is not the only source of this data?
a) If No than FULL
b) If Yes then SIMPLE
The backup plan for DBSERVER01 – DBSERVER05 seems reasonable to me but just need to include masterdb in the backup list and schedule the FULL backup to run at a certain time every two weeks rather than have the IT manager trigger the job on each server every two weeks!
The backup plan for DBSERVER06 – DBSERVER08 can remain the same for databases in SIMPLE recovery model (if that’s what the appropriate recovery model is for the customer) but for databases in FULL recovery model then a transaction log backup every three times a day maybe sufficient.
The next stage is reporting on Backup across all servers.
For this I am planning to create a centralised database which will house information about the back status of all databases on all servers. I plan to have a job that runs say 7AM every day which would look for all backups completed in the last 24 hours on each server and store the information in this central database. For this I am going to create an SSIS package and schedule a job to run it.
I will then use SSRS to generate the required reports on a daily/weekly/monthly basis as needed.
Can someone let me know if my proposal sounds reasonable or am I missing something important here?
Thanks in advance.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
August 29, 2012 at 6:18 am
The one thing I see missing are log backups. You don't list those as the types of backups, but you have databases in FULL recovery. If you're not running log backups, then you're probably watching your log files fill up & grow.
The general approach, working with the business to understand their recovery needs, is the right way to go. Backup and restore are not technical issues, but business ones. Just make it very clear, if you're in Simple Recovery, you can only, ever, go back to the last full or last differential. No other options are available.
As to the reporting databases, not only would I make them Simple Recovery, but I'd make them Read Only. That's a huge performance win (you'll have to swap it from Read Only as part of the load process).
"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 29, 2012 at 6:26 am
Grant Fritchey (8/29/2012)
The one thing I see missing are log backups. You don't list those as the types of backups, but you have databases in FULL recovery. If you're not running log backups, then you're probably watching your log files fill up & grow.The general approach, working with the business to understand their recovery needs, is the right way to go. Backup and restore are not technical issues, but business ones. Just make it very clear, if you're in Simple Recovery, you can only, ever, go back to the last full or last differential. No other options are available.
As to the reporting databases, not only would I make them Simple Recovery, but I'd make them Read Only. That's a huge performance win (you'll have to swap it from Read Only as part of the load process).
Grant, firstly let me thank you for your time. I know it's a long post and it can put people off responding!
Apologies if I have left out the log backups! In fact this is what I've added to the document I will be presenting management:
1) For each database we need to determine the appropriate recovery model (see Determining Recovery model) section above.
2) A new scheduled job needs to be setup to perform transaction log back for databases in FULL recovery mode. Lack of transaction log backup will result in the transaction log file growing and growing until all disk space is consumed. By performing regular transaction log backup, SQL Server will free up the space in the log file for new transactions.
3) The existing backup stored procedures will need to be modified to include the master database.
4) The fortnightly backup job needs to be scheduled to run at 6PM on each SQL Server instance.
Thanks for pointing out the READ ONLY mode. Although I'm not sure how it helps performance? Is it because there is no over-head in maintaining transaction logging as no data is modified/inserted?!
Once again, thanks for your time. I really appreciate it!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
August 29, 2012 at 6:36 am
READ ONLY on a database means it no longer bothers maintaining locks because there is not possibility of data modification.
"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 29, 2012 at 7:49 am
Grant Fritchey (8/29/2012)
READ ONLY on a database means it no longer bothers maintaining locks because there is not possibility of data modification.
Makes perfect sense.
Thanks for the tip! 😉
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply