SQL Saturday

Howzit folks? So another SQLSaturday has come and gone.SQLSat123 was fantastic ,more people and sponsors,a great speaker line up that included Brian Knight Twitter|Blog, Mark Stacey Twitter|Blog, Gary Hope Twitter, Juan Thomas Twitter, Hannes Burger and Twitter . The event was held at Torque-IT in Tyger Valley, Cape Town.

We had 102 registered, 40 more than last year,we changed the set up by extending the sessions to 01h30 and included 3 hour deep dive session on the BI and DBA tracks. The event would not have been possible without our great sponsors Microsoft, Torque-It, KRS,Saratoga Software, Red-Gate, Idera, Morgan Kaufmann Publishers, MyWebGrocer, Osmosis Consulting and Pragmatic Works.

Based on the feedback received from attendees, the event was a great success which satisfied everyone, I think. I also want to thank the unsung hero's of the day, the folks behind the scenes: Tracy Gander from Saratoga Software, Alistair Pugin from Pragmatic Works, Chantelle, Nadia, Sally and the support staff at Torque-IT.

For more free SQL Server training PASS is presenting 24 hour PASS on 21 March 2012. It is an on-line event with 24 sessions presented by industry experts.You can register at 24HoursofPASS

From the SQLCape user group|Twitter organizing committee, we thank everyone who attended as well as all our sponsors .We look forward to seeing you at the next SQL event.

Regards
Jody

ME

Sally,Pieter and Jody at the registration tabl

Sally

Speaker Juan Thomas

Juan and Hannes in action

Brian Knight etting ready for his session on SSIS

Team Torque-IT Nadia ,Chantelle and Sally

Jody and Alistair Pugin

Jody,Chantelle and Alistair

back of SQLSAT T-Shirt

Juan in action

Hannes in action

Hannes showing off SharePoint in a BI 

environment

The legendary Gary Hope from Microsoft presenting on SQL2012  high 

availability

Team Saratoga Hannes Burger and Yun-Kuang Cheng

Jody and Toufiq Abrahams

Juan Thomas and Hannes Burger

Alain King

iPad 2 winner Ghoezayma and Satish Byrow

SQL Disaster Recovery Considerations

In this post I hope to  provide better insight  into what you need to consider when planning your DR strategy.

I often find that organizations never have a sufficient DR plan especially around their databases. I have seen organizations take a week or more to restore their primary database due to insufficient planning. What made it worse was that their SLA stated that their vendor could restore any database within a 3 month window.

This annoyed the crap out of me to put it mildly. Luckily it wasn't a SQL database but I felt bad for the support guys . I recently presented at SQLSaturday88 in Cape Town ,South Africa on the importance of planning your DR strategy from a DBA point of view . This is what I recommend :

Considerations

• Define your systems e.g. Tier1,Tier2 ,Tier3. The reason for this is so that you know which databases you will restore first. 
• Decide early on which method you going to use for your restores , are you going to restore directly from tape,disk or will you be using third party software
• Consider if you need point-in-time recovery. Most hardware vendors have replication software that allows you to copy the mdf and ldf files to a point in time

Restoring Directly from Tape

• This part you will require your backup administrators help,the reason for this is so he can ensure that the backups are loaded in the order you want to restore them.
• Depending on which backup software you using e.g. Netbackup,Backup Exec,Tivoli TSM, you will be using their respective UI's,remember the same SQL restore principles apply

Restoring Using 3rd Party Software

• I love these,3rd party replication software,they rock and cut your restore times down to minutes instead of hours (Last time I used it to restore a system that normally  takes 8 hours to restore,with the RM software it took 15 minutes) e.g EMC replication manager,HP DRM
• Consider if you need point in time recovery.Most Hardware vendors have replication software that allows you to copy the MDF and ldf files to a point in time.

The Power of Documentation

• Keep your database documentation updated.Your documentation can help you recover your database structures in the event that you have no backups
• Once again make your life easy 3rd party software, RED-GATE's SQLDOC or PragmaticWorks BI Documenter,The database edition is free.
• They usually document all DB schemas,objects ,permissions and include samples of your data
• Do NOT under estimate the power of your documentation in worse case scenario when you have no backups,no tapes this will be your only tool
• Update it at least once a week depending on your database structure changes 

When Crap Hits the fan and you have to restore the master database

Restoring Master

• This should be your last resort and should only be done when you 
• ONLY if you don't know have any idea of which databases you need to restore.
• To do this you need to put SQL in single user mode to do this go start --> programs-->Microsoft SQL Server 2008 R2-->Configuration tools-->SQL Server Configuration Manager

                                        

•          Once in the SQL config manager right click on properties

                          

Now click on the advanced tab and go down to start-up parameters , go to the end of the line and add a semicolon (;) , then add -m

     

• Click on apply and then ok and in SQL Config manager restart the server

                                                       

• Now open the command line and type in the following,Before you do this make sure that the SQL Agent on the server is stopped.

                                        
         

• Once the database is restored,go back into SQL Config manager  ,right click,properties ,Advanced,start-up parameters and remove -m,click apply then ok and restart the SQL service
• Open SQL Management studio and you should see the following

                                                   

before you restore you user databases have a look under security, logins,you will see that all your logins are there(saves you from having to scripting your logins)

                                                            

• You can now restore your model(if you have customized it) and MSDB database
• After restoring the MSDB database(This will restore all your jobs)

                                               

Conclusion

• With restore methods 1&2 you will need to script out you logins,make your life easy,There is a tool call SQLPermissions from Idera Software and run the script(s) after the restores,best of all it's free
• If you have any questions  feel free to ask or even better send me feedback  Email Jody or on Twitter

Follow @Jody_WP