The series so far:
- DBA in training: So, you want to be a DBA...
- DBA in training: Preparing for interviews
- DBA in training: Know your environment(s)
- DBA in training: Security
- DBA in training: Backups, SLAs, and restore strategies
- DBA in training: Know your server’s limits
- DBA in training: SQL Server under the hood
- DBA in training: SQL Server high availability options
Ask any DBA what the first thing you need to know is, and 99.9% will say “backups”. Well, it’s almost the first thing. The first thing you need to know is your server environments. Specifically, you need to know the following:
- All of the SQL server instances that you will be managing. Some applications include an edition of Express or SQL Server under the covers in their install, which can take you by surprise. Congratulations! Now you have a database no one ever knew about to maintain! You’ll want to coordinate with Infrastructure to do a regular search for SQL Servers so that you don’t wind up with surprise instances that are unpatched and completely lacking backups.
- Whether your instances on-prem or in the cloud (or maybe a mixture of both). The management of these can be quite different, so this is something you need to know up front.
- What versions and editions of SQL Server are you going to be responsible for? Are the versions still under support?
- Who owns the databases on the instances you manage? What applications are tied to those databases? How does the business use the data? You need to know this because that will tell you who to contact if something goes wrong, and the consequences to the business if it does.
- You will naturally have a Production environment. What other environments are you responsible for? For instance, do you have a Stage or QA environment of servers, a test environment, a dev environment?
- Know who has access to do what. I will explore this in more detail in a later article. Your data’s security is paramount.
- Take a look at the SQL Agent jobs that are running. You want to know that you will be notified if one fails. (Hint: To do this, right-click on a job, go to Properties and then to the Notifications tab. You want to see that you will be notified, and that the “Write to the Windows Application event log: When the job fails” box is checked. Now, if you are notified that a job fails, you can right-click, choose “View History” and expand the tabs to find detailed messages on the failures. If what you see doesn’t make it clear to you, the Windows application event log should help.
If you have ever worked with SQL Server or SSMS in the past, you won’t be too lost when you begin processing this information I just gave you to collect. If you haven’t done this before, you might ask first and see if any documentation exists to bring you up to speed. If there isn’t, you get to be the hero who makes this documentation happen.
You want this list. Not only does it help you to keep track of what you should be monitoring, you will need it for patching (if it falls to you to do that). How do you find the servers if there is no list? You might start by looking at your server monitoring software to see what instances are listed there, and speak to Infrastructure to see what other servers should be added to the list. Don’t just go pinging servers to compile your list though, or you may be the recipient of a panicked phone call from an angry admin wondering what you were thinking!
Get your server list and run “SELECT @@VERSION” against them all. Record the results. This information is gold. You will know what version of SQL Server you have, what edition, and the patch number (SP<number>-CU<number>). From that, you can infer is a SQL Server instance is still supported by Microsoft or not, what features and syntax are (or are not) available to you, and if it is vulnerable to security risks or possibly missing features that you may expect to be there. Let’s talk about how you figure all of that out from one line of data.
SQL Server Versions
You could see versions of SQL Server on your list from as far back as 2000 (hopefully not before that) to 2019. In a perfect world, you wouldn’t see any SQL Server versions that are more than five years old. In the real world, you probably will, though.
Why do I like SQL Server versions that are less than five years old? It has to do with how well each version is supported. Each time Microsoft releases a new version of SQL Server, a life cycle begins. Usually, the version will have a mainstream support end date of five years. After that, it goes into extended support for five more years. Extended support means that you will pay extra for Microsoft support at that point. There will be fewer people to help you because Microsoft will throw the main bolus of its efforts behind its more current products and in developing future products and features.
Sometimes the vendors of products don’t keep as current with SQL Server versions as you would like and are hesitant to support a version upgrade. You need to be aware of this and communicate this information to your superiors. For instance, you might say, “I noticed that there is a SQL Server 2005 instance running and one of the databases on it is connected to X application. I realize that the vendor hasn’t wanted to support an instance upgrade, but that Microsoft no longer supports SQL Server version, so if anything happens on this instance, we’re on our own. In addition, I am concerned about the possible security risks of running something that far out of date. Can we arrange a meeting with the vendor to discuss an upgrade, or is the possible loss of the server something you are comfortable with?” Sometimes, the vendor will tell you to do it at your own risk, and other times, they will support a version up to a certain service pack, and then want you to wait while they finish their next application release. The point is that you are doing your due diligence, and both your company and vendors will realize that.
If your new company uses Redgate SQL Monitor, you can see information about both versions and editions on the Estate tab.
Service Packs (SP) and Cumulative Updates (CU)
Microsoft tries hard to test SQL Server releases for contingencies and bugs, but it cannot possibly test for every use case. Additionally, they may decide to add new features to an existing version release (such as the big level-up that Standard edition got with 2016 SP1).
Enter the service packs and cumulative updates.
Service packs are larger updates to a release. They typically include all sorts of fixes to reported bugs, and occasionally they contain security updates and new features. Each service pack can stand alone – it contains all the fixes from previous packs, plus the new updates specific to that SP. They are discontinued in versions 2017+, at which time Microsoft decided to go to just using cumulative updates.
You are not supposed to use a word in a definition, but a cumulative update is just that – a collection of hotfixes and updates. Hotfixes are solutions to specific issues that Microsoft has chosen to address.
To sum it up: hotfixes and updates are collected to make a cumulative update. A collection of CUs make a service pack (again, in versions 2016 and under). You will hear the process of applying CUs and SPs as “patching”. SQL Server patches are applied to the Windows server where they are hosted. You can opt to begin applying them to your lower (i.e., dev/test/stage/QA) environments as they come out, or you can elect to do them on a schedule. I prefer to do them quarterly. I typically patch one environment a week for my lower environments, then do production a couple of weeks after that, just to ensure that there were no issues. It seems the safest way to protect production and at the same time avoid patching on a semi full-time basis.
Microsoft recommends that your SQL Server instances be patched to the most current level for that version. This helps to ensure that the latest, best fixes and features are on your SQL Servers. Some vendors will only support their application up to a certain service pack, so you will need to know what (if any) limitations exist.
SQL Server Editions
If you haven’t had much of a chance to work too much with either SQL Server or SSMS, let’s begin by discussing the basic differences between the Express, Standard and Enterprise editions. I won’t go into too much detail here, as there are specifics that you can Google as needed, but in a nutshell:
SQL Server Express edition is free, but it has many limitations in terms of database size and feature availability, making it more suited to very small audiences or lightweight applications.
SQL Server Standard edition used to have a lot more feature restrictions on it, but in 2016 SP1, several great Enterprise-only features were included in Standard edition as well, such as Always On Availability Groups, auditing, columnstore and partitioning. It’s thousands of dollars less per logical core than Enterprise, making it a popular choice.
SQL Server Developer edition is basically the free edition of Enterprise. It’s great if you want to practice learning TSQL and learning how features work, or for your lower environments. However, you can’t bring data from a Developer instance to an Enterprise instance or use it for any production purpose such as reporting or automation. It’s a license violation, so don’t do that!
SQL Server Enterprise edition is the Cadillac edition. You get all the bells and whistles, and you will pay for that capability. How much? It depends on the deal you work out with Microsoft.
Deprecation
Remember when I said that you could determine what features and syntax you may or may not be able to access by knowing the version? This is not always easy to do when you are new; often it occurs that you type in some code, it doesn’t work, and you are wondering why. As you do this job for a while and get the news of new versions and new updates, you will find that some of this information recall is automatic because it made a big difference to you.
Finding that features or syntax doesn’t work can happen in either direction. For instance, if you are used to working with SQL Server 2016 and find yourself working on a 2012 instance, typing “DROP TABLE IF EXISTS #t;” or “CREATE OR ALTER PROCEDURE” is going to break, because it wasn’t enabled yet. On the other hand, you may run into syntax, data types or features that are deprecated or disabled entirely.
What is deprecation? It is when Microsoft is trying to phase something out. They will warn you that this is happening by saying the feature is deprecated. Think of anything you find out is deprecated as walking dead and fix it. Don’t allow anything new on your servers that is deprecated. Once Microsoft has fixed its code and cleared the deprecated feature, you will find that the day comes when your server is patched and a bunch of stuff breaks. If that happens, this is one place to look. But hopefully, you won’t need to, because you were proactive!
Conclusion
Knowing how many SQL Server instances you have is the first step. Understanding how many environments you have to manage is another. Knowledge of what databases are on your instances and the applications they are tied to those databases, who the points of contact are and what these applications do for the business will be the next step to begin to get your sea legs and gaining confidence. Finally, as you learn about the different versions and editions and what they have to offer, you will be able to help your company make the best decisions about what will work for them.
Load comments