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 |
|
Abu-Dina
Posting Yak Master
206 Posts |
 Posted - 2012-08-29 : 07:12:00
|
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 DBSERVER05These 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 DBSERVER08These 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, nonea) If less than a day or none then FULL b) If more than a day then SIMPLEWhat kind of processing occurs? Transaction, batch load, reporting only, a combinationa) If transaction or a combination then FULL.b) If Reporting then SIMPLEThis data be easily recreated if there is a failure or it is not the only source of this data?a) If No than FULLb) If Yes then SIMPLEThe 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.p.sI have also posted the same post on a different SQL Server forum so apologies if you frequent other forums but I'm trying to get feedback from as many database professionals as possible. I just want to make sure I'm on the right path!  |
|
|
|
|
|
|
|
Backup process - Your feedback required!