SQL Server World

Friday 04/10/2015
SQL Saturday Madison Pre-Con: Planning a SQL Server High Availability and Disaster Recovery Plan with Ryan Adams

I have read and researched and tested and have implemented in production many high availability (HA) solutions. But things keep on changing as new SQL Server features become available. Not forgetting that hardware side of the things (VMs and Azure clouds) also keep changing. SQL Server offers many options, but how do you choose the right one to meet your business requirements? In this one day class, Ryan took us from basic of Backup and Restore to Windows clustering and Availability groups. In between we touched pros and cons and gotchas of log shipping, mirroring and replication etc. Every feature has pros and cons. It will be hard to list all here.

He gave us a scenario in the beginning of the class and we were to design our solution as class progresses and our knowledge increases. The scenario was to configure a HA\DR plan to ensure a company’s systems are always up and running and can withstand a technical or natural disaster. There are 3 data centers Maine, California and Arizona. Network connection between Maine and California is excellent and between Maine and Arizona is okay. There are 3 critical databases in Main that need HA\DR and Reports can be 2 hours behind. Only one solution was asked and I was able to design 2 solutions and instructor was impressed and asked me to present my thought process behind it.

You will ask why backup and restore discussion in HA class? Answer is proper backup and restore strategies are foundation for successful implementation of any HA\DR solution. I learned and saw firsthand in demos that using backup options such as MaxTransfersize and BlockSize (which are not available in Maintenance Plan utility) can cut your backup and restore time in half. This is not important to us now but I learned something new. I can use them now to plan backups if and when our DB Sizes reaches into 100s of Gigs.

I also learned that in my recent availability group setup, I had omitted one important step and that is having 2 NICs connected to each Node of the configuration. I have to talk to our IT guys now.

Saturday 04/11/2015 (FREE TRAINING)

Now I will briefly describe the sessions that I attended on Saturday. There were 7 tracks and 7 x 5 = 35 sessions. But unfortunately, one person can only be in one session at a time. Each session of 75 minutes. I chose to attend following 5 sessions.

Session 1: Practical Powershell for DBA
This session was 90 percent demo of the scripts that the presenter was using himself at his work. It not only showed the actual work but also the possibilities in my work where I can extend the functionality. It was mostly geared towards people who have 30 to 300 sql server instances to manage. I don’t see why this cannot be used in our smaller shop. I have used Powershell scripts in the past employment and can use it at present job for Database maintenance work.

Session 2: SSMS Showdown
This was my favorite session of all. 2 presenters acted as they were competing against each other to show us the coolest tips and tricks of SQL Server Management studio (lovingly called SSMS). There were 5 rounds so we should be learning 10 coolest tips but in order to outsmart the opponent each presenter tried to show us more in one round. We, audience, voted after each round. I am planning to show what I learned here to my development team. It can be really useful in our day to day dealing with TSQL scripting and coding using SSMS. It will improve efficiency and overall quality of work.

Session 3: Collect baseline for all your servers
Base-lining is important because before we answer why the system is slow or what is wrong with SQL Server, we need to know how it reacts under normal circumstances. Again everyone has their own tool of collecting server level and database level information for trending and troubleshooting purposes and this presenter showed his own. He used SQL Server Integration Services (SSIS) to collect his baseline metrics. I saw the benefits of using it and how flexible it becomes when you have to monitor several servers. You only need to make a change in the environment variable in the configuration file and you can use this same solution across multiple servers from Dev to production. Since connection managers uses the standard connection strings, no linked servers were required.

Session 4: Administering SQL Servers for Development Teams
There were 2 presenters. One was a DBA and another was a developer. Since this is my first job here where I am actually the part of the sprint meeting every day, I thought I would attend this session. I did not as much learned anything new in this session as it validated and confirmed what we are doing is right with our sprint meetings, agile methods and TFS to track bugs and source control to save codes etc. I wish I could bring some new information but glad to know that what we are doing at work is exactly as the best industry practice suggests. It also talked about how DBA can help developers and again we are following that method here.

Session 5: Automated SQL Installs
I chose this session because it was going to be all demos while other sessions can be followed through the PowerPoint slides and script that each presenter post on SQL Saturday site. I followed through 3 quarter of the session than I lost it because it was too technical for me. Also at that point I realized that although this is nice to automate base SQL server install, Service pack upgrades, Cumulative upgrades and patches etc., we are not going to use it here due to security concerns etc. But again the point of choosing the sessions is to go little bit out of your comfort zone to learn something new. I think I know where to turn to if in my future career time comes to use the Automated Install of SQL Server. Presenter used CodePlex project “FineBuild” (open source) to show us step by step process of building install scripts and not every but most of the possible options to select.

Final thoughts:
I know many of these things that I mentioned above can be learned from the books or attending online webinars etc. But the enthusiasm of the speakers, volunteers and participants cannot be bought or experienced without being there in person. As a SQL Server professional you need that face time with MVPs and see firsthand their confidence, humility and spirit of giving back to the community. This inspires us to go extra mile at our own work place and feel joy from applying the new concept, trick or shortcut just learned from the industry experts. It also makes us humble and never feel ashamed of our shortcomings or learning from others. It is like a dose of medicine that you need to take periodically throughout the year. Therefore I am planning to attend at least 4 SQL Saturday events each year. Another benefit of attending these community events is that you build new and maintain existing relationships with presenters and vendors.