|

SQL Server and Other Topics

rss

I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver


Part 1 > Part 2 > Part 3

 

In part 1 I started building the framework for moving from SQL Server Database Mirroring (mirroring) to SQL Server AlwaysOn Availability Groups (AGs).

Inventory what you have in all environments, what are you going to need to migrate to the new server (remember this is about moving to new (rebuilt) servers. There are many blogs about how to move mail configuration, sql jobs, users, alerts, operators, database and server configuration, etc., etc.  Don't forget anything. Get the team together to brainstorm all of the things that must be moved and configured.

Complete Inventory

Create a design document. This design document should cover all aspects of what the environment will "look" like after the migration. It is critical to think through the licensing, the availability replica modes, failover  policy, etc. It gives you a chance to think deeply about what needs to be done to configure all aspects of the migration. Know the roadblocks, the requirements for an Availability Group and where AGs will not work. Here are a few:

  1. DTC - Don't do AGs if you're application uses DTC.
  2. Cross Database Transactions - Don't do it! If you have a failover and your app is doing a cross database transaction, the primary could come back in suspect mode. This arfticle will help confirm if you're using cross database transactions.
  3. Domain - A domain is required for the WSFC (that changes in Windows Server vNext).
  4. Cluster - The AG replicas must all be in the same cluster.

 There are more issues you will encounter with Ags... see here.

Determine what the business SLAs are for Recovery Time Objective (RTO) and Recovery Point Objective (RPO) and document in the design document.  You want to be able to tell management how much downtime to expect.

Use Visio to create diagrams, for your own benefit as much as everyone Else's. These diagrams will help keep your head straight with what your design is. It will allow you to plan the number of licenses for management, and it will be your "evidence" after your done when management says, you didn't tell us... oh yes I did, a a matter of fact it is right here in the design document that you approved. Ok, so there is another critical element. Make sure management approves the design document (put a spot for signatures on the cover page), it will prompt them to sign it. Don't deliver the design document until you have tested the migration in a lower environment first. It will expose gotchas that you didn't think about (hopefully you tested prior to getting buy in from management). 

Approved Design

Now that we have the design approved by the client (management), its time to work on the most important step...planning. Without a proper plan not only will you not succeed, but you will probably fail horribly. Let's put that down as a critical component.

Complete Plan

The plan should be a step by step plan with dependencies,  start time of each step (as a calculation based upon the previous step duration), estimate duration step, step status (Completed, In Progress, Cancelled, etc.), responsible party, day of week (calculated from date time column) and step number for quick reference. I usually put my plans in Excel and have a conditional format for the row based upon the status.

This plan should include all steps include server builds, network, stress test, benchmark testing, etc. If you want to be a plan Nazi include the regression testing, etc. Hopefully your company or client has an experienced  project manager for the project. It will take a little longer, but if you have a good project manager your project will have a complete project plan avoiding gotchas at a later date.

 Summary

I know this is a lot of work, but it will save you in the end. Your job is to eliminate all possibility of accidental downtime and minimize the length of downtime as well as have a highly available environment that works for your company or client. Please take your time and do the work.


Every year PASS has elections for board members to determine who the next directors will be. Being on the board requires a time commitment many are unable to commit to. There are a few who are willing to give up a large chunk of their time to serve the PASS members. We should all applaud the volunteers who take up the calling to help improve the entity us database professionals owe so much to.

 This year two of my friends have been nominated and are fully qualified for this position:

Ryan Adams

Ryan is one of my colleagues at Linchpin People who I have been following and learning from for years (prior to Linchpin People). The folloRyan Adamswing are the top reasons I am voting for Ryan for the PASS Board of Directors:

  1. Ryan is a natural leader with professional experience as a leader.
  2. Ryan is very active in the SQL Server community, being a leader in his local chapter the "North Texas SQL Server User Group", an organizer of SQLRally, president of the Performance Tuning Virtual chapter, member of the PASS Summit program committee, etc. etc. In other words, he's been deeply involved for many years.
  3.  Performance Palooza, do I need to say more?
  4. He's also an active member of his community volunteering for a great program "Watch D.O.G.S" at his kids school.

I aspire to be as involved and impactful as Ryan has been to so many people.

Argenis Fernandez

I've known Argenis for several years. He's one of the guys who will remember your name, give all #SQLFamily members hugs when he sees them and spread his infectious positiveness to all around him.

 Argenis FernandezWhen I had a question that needed answered, he didn't hesitate to give me his mobile phone number to call and talk about it.

Argenis is passionate about helping others, and doing what he can to improve the lives of others when he can. He seems to be involved in everything and has great opinions about many things including PASS.

 Summary

I know that all of  the candidates would be excellent choices, but for the reasons described above and the fact that I trust the honesty and integrity of both of these guys completely and expect that the PASS organization will be very stronger with them as PASS Directors you should seriously consider them (no, just vote for them).

 http://www.sqlpass.org/Elections.aspx

 

 


Ed Watson and I have been presenting a tag team performance tuning session for over a year now at local SQLSaturdays. We've had the pleasure of presenting the subject in multiple formats from 1 hour (rapid fire taste) to 8 hours as a precon session.

You probably already know who I am if you're reading my blog, in short I am a Linchpin People teammate and independent consultant with almost 20 years of SQL Server experience. My forte is performance tuning for large enterprise corporations with serious performance issues.

Ed Watson is a Microsoft MVP, and is a consultant in his day job. Ed has more than 15 years in IT, and has been a senior Production DBA for many years... you probably know him best as @SQLGator and can find him blogging at the SQLSwampland... yes he's a Florida Gator... don't hold it against him (I try not to).

September 25th, 2015, Ed Watson (b|t) and I will be presenting the session, "Getting the New DBA Up to Speed with Performance Tuning" as a full 8 hours class.

This session is for those guys or gals wanting to become a DBA, are an "accidental DBA" or a DBA with a little experience who wants to gain some add knowledge to their performance tuning playbook.

In this session we'll start at the beginning of the process of building the foundation following best practices for a highly tuned or tune-able environment. We will cover how to configure the OS and database server to best take advantage of features built in to the OS and SQL Server for peak performance. We'll dig into some of the database server configurations that effect performance such as "Optimize for Adhoc Workloads", MAXDOP and "Cost Threshold for Parallelism".

We'll talk about TempDb optimizations, and why they make a difference and how to find the correct number of data files. Mike Lawell

We'll cover benchmark basics and how to see that your database performance is suffering compared to historical expectations.

Once you have the foundation needed to build a stable environment we'll look at some of the top performance issues and how to identify them when you've had a complaint about performance. Using some of the free tools that come with SQL Server, such as extended events, we'll teach you how to trap what is causing the slowness in the database for many issues encountered by a DBA.

We'll spend more than an hour going over execution plans from beginning concepts to some advanced topics (don't worry, we'll make sure we don't go too far). We'll make sure you know where to get more information from some of our performance tuning heros!

But wait, there's more... we'll share with you some of our favorite scripts that we've collected over time that will give you a little bit of an edge next time someone calls and says, "everything is slow", help!!

All of this for an extremely low price of $150... that is the sum total of $18.75 per hour!!

 We can't wait to see you at "Getting the New DBA Up to Speed with Performance Tuning"

SQL Server Innovators Guild (ssig)
Friday, September 25, 2015 from 8:30 AM to 5:00 PM (EDT)
Spartanburg, SC

Register Today