Ever heard of the faster/cheaper/better paradox? It says that anything can be broken down into three groups: get it faster, get it cheaper, or get it better… but you can only pick two. So something must always be sacrificed: faster and cheaper means it won’t be better, and cheaper and better means it won’t be faster. Does this strike anyone as a bit unrealistic? Why can’t something be all three?
Proper planning can isolate almost any failure. Correctly aligning resources can diminish nearly any risk. At the heart of database administration is the ever-present challenge to provide these three principles: faster performance, cheaper overhead, and better quality data. The failure to provide these for the organization employing you will definitely result in your lack of employment. Most of the time, it’s not really as serious as this, but the point can be made that you are the person responsible for the safe handling of the company’s most important asset—its data. Whether proprietary or trade data, or government classifications, or even just a simple database holding names and phone numbers, the data that you are responsible for is important to the people that need to access it. For that reason, we as DBAs are the last line of defense to make sure that we are maintaining our databases correctly in order to provide a higher level of database integrity for our end users, whether they are our grandmothers, or stockbrokers, or any other level of user.
The most important part to database administration, beyond installation and actual development, is the maintenance of the database. This ongoing practice should be a part of the daily life of any seasoned database administrator (DBA) . Since the DBA is the primary focal point for the database, if it goes poof in the dark, you had better hope it’s not your fault. Luckily, SQL Server provides a wealth of tools specifically geared toward giving a lot of power to the DBA in as small a package as possible, with the hope that the DBA uses these tools to mitigate any possible risk to the data they are responsible for.
After all, the primary responsibility of the DBA is to ensure protection, integrity, consistency, and availability of their data. Following the instructions in this book and implementing a complete maintenance plan will get you started accomplishing those goals.
Before We Start
I have a very specific way that I have my files structured. You might or might not, but I wanted to explain it because I frequently reference it throughout this book.
I have a logical E: drive that I use for all of my SQL Server files. Not the installation files, just the database files. The root of this drive is E: and there is exactly one folder named SQL Server. So, the main directory for all of my database files is E:SQL Server. Inside of this folder, I have the following directories.
Backups: The .bak files for each backup, stored in folders per database
Data: The .mdf and .ldf files for each database, stored in folders per database
Logs: The .trn files for each database, stored in folders per database
Everything that I need for SQL Server development is found in these folders. This might not work for your setup, but I hope it does. If it doesn’t, just adjust your own particular folder structure to the examples in the chapters. I am going to be adding folders to this structure in later chapters, so read on to find out what they will contain.
You will also need a Windows login with sysadmin rights to the database engine, and a SQL Server login with sysadmin rights as well (usually, your sa account works just fine). These accounts are very common in newer SQL Server installations, so it shouldn’t be an issue. The large majority of the book will use the Windows login, as that login typically either owns the database, or has sysadmin permission to modify the database as needed. If you don’t have an account that has these permissions, you may not be able to create and execute the plans against the database you need to maintain. In this case, you are really not a DBA and are more like a “data facilitator.” As a DBA, you should have one account that gives you complete and total control of the database. Use that account to set up the things in this book.
What Is a Maintenance Plan?
When you create a maintenance plan , SQL Server creates an Integration Services package that is executed by the SQL Server Agent. A maintenance plan exists for one reason: to make the life of a DBA easier by automating administrative tasks. That’s it! With a well-thought-out maintenance plan, you can do all sorts of things automatically on a set schedule.
So, exactly what things can be done in a maintenance plan, you ask? First of all, fire up SQL Server Management Studio and expand the Management section. Expand the Maintenance Plans section next. On a clean install, there won’t be anything in here, which is fine. That’s why you’re reading this, hopefully. You might see what is in Figure 1-1.
Figure 1-1. Nothing in the Maintenance Plans folder!
There are two ways to set up a maintenance plan from here. You can either go with the wizard or create it from scratch yourself by using what is referred to as the Maintenance Plan Design Surface. The differences between the two choices are slight, but there is no real power difference. Let’s examine the differences.
Right-click Maintenance Plans and choose Maintenance Plan Wizard. An introduction screen pops up with general information about the wizard, so just click Next to continue.
You are then presented with the first “real” screen of the wizard. Just click Next here, because we’re going to cancel it after the next step. This is just to get us familiar with the maintenance plan task choices.
Maintenance Plan Wizard Task Options
After you hit Next, you are presented with the following options, which can be set up from the wizard interface.
Check Database Integrity: The Check Database Integrity task performs internal consistency checks of the data and index pages within the database.
Shrink Database: The Shrink Database task reduces the disk space consumed by the database and log files by removing empty data and log pages.
Reorganize Index: The Reorganize Index task defragments and compacts clustered and nonclustered indexes on tables and views. This improves index-scanning performance.
Rebuild Index: The Rebuild Index task reorganizes data on the data and index pages by rebuilding indexes. This improves performance of index scans and seeks. This task also optimizes the distribution of data and free space on the index pages, allowing faster future growth.
Update Statistics: The Update Statistics task ensures the query optimizer has up-to-date information about the distribution of data values in the tables. This allows the optimizer to make better judgments about data access strategies.
Clean Up History: The History Cleanup task deletes historical data about Backup and Restore, SQL Server Agent, and Maintenance Plan operations. This wizard allows you to specify the type and age of the data to be deleted.
Execute SQL Server Agent Job: The Execute SQL Server Agent Job task allows you to select SQL Server Agent jobs to run as part of the maintenance plan.
Back Up Database (Full): The Back Up Database (Full) task allows you to specify the source databases, destination files or tapes, and overwrite options for a Full backup of a database or transaction log.
Back Up Database (Differential): The Back Up Database task allows you to specify the source databases, destination files or tapes, and overwrite options for a Differential backup of a database or transaction log.
Back Up Database (Transaction Log): The Back Up Database task allows you to specify the source databases, destination files or tapes, and overwrite options for a Transaction Log backup of a database or transaction log.
Maintenance Cleanup Task: The Maintenance Cleanup task removes files left over from executing a maintenance plan.
Go ahead and cancel that screen now. Go back and right-click Maintenance Plans again, but this time, choose New Maintenance Plan….
The first thing you need to do is give it a name, but you can just click OK here. Again, we’re just looking at the options for now. A whole new interface opens with a toolbox on the left Subplan information across the top. This is the Maintenance Plan Design Surface. Figure 1-2 should be what you see now.
Figure 1-2. The Maintenance Plan Design Surface
Tip
If you don’t see the Toolbox, just press Ctrl+Alt+X and it will show.
Maintenance Plan Design Surface Options
If you look in the toolbox on the left, you have some different choices here. I wonder why that is? I will explain why in just a second. Let’s look at the options first.
Back Up Database: The Back Up Database task allows you to specify the source databases, destination files or tapes, and overwrite options for a full backup of a database or transaction log.
Check Database Integrity: The Check Database Integrity task performs internal consistency checks of the data and index pages within the database.
Execute SQL Server Agent Job: The Execute SQL Server Agent Job task allows you to select SQL Server Agent jobs to run as part of the maintenance plan.
Execute T-SQL Statement: The Execute T-SQL task allows you to run SQL queries as part of the maintenance plan.
History Cleanup: The History Cleanup task deletes historical data about Backup and Restore, SQL Server Agent, and Maintenance Plan operations. This wizard allows you to specify the type and age of the data to be deleted.
Maintenance Cleanup: The Maintenance Cleanup task removes files left over from executing a maintenance plan.
Notify Operator: The Notify Operator task allows for email to be sent from SQL Server after the execution of a maintenance plan. This is for both success and failure.
Rebuild Index: The Rebuild Index task reorganizes data on the data and index pages by rebuilding indexes. This improves performance of index scans and seeks. This task also optimizes the distribution of data and free space on the index pages, allowing faster future growth.
Reorganize Index: The Reorganize Index task defragments and compacts clustered and non-clustered indexes on tables and views. This improves index-scanning performance.
Shrink Database: The Shrink Database task reduces the disk space consumed by the database and log files by removing empty data and log pages.
Update Statistics: The Update Statistics task ensures the query optimizer has up-to-date information about the distribution of data values in the tables. This allows the optimizer to make better judgments about data access strategies.
Most of those look familiar, because they are mostly shared between the interfaces. What are the differences, you ask? Let’s take a look and see. Figure 1-3 compares the two types of tasks.
Figure 1-3. Differences between the wizard and Design Surface Task
There are 11 tasks in each area… but they are not the same. The difference is, basically, how you want to define the requirements for the workflow that you are creating as part of the maintenance plan. There really isn’t a major difference between the two sections, as you can see in Figure 1-3. Or is there?
The first difference is the lack of the Execute T-SQL Statement task in the wizard, but if there is SQL that needs to be executed, it can easily be worked into the Execute SQL Server Agent Job task. Other than that, they are pretty much the same.
The second difference is the lack of the Notify Operator task in the wizard. You can always set the notifications in the tasks themselves though, so this really isn’t an issue either.
The 11 tasks shown in Figure 1-3 (in either iteration, really) are at the very core of database administration. Please note that all of these tasks individually can be done manually within the SQL Server Management Studio interface. It’s not like this is the only place you can find this functionality. Quite the opposite; true to Microsoft standard operating procedure, there are always at least two ways to do any one task.
It is also worth pointing out that any number of these tasks can be joined in the same maintenance plan, so you really are free to completely customize the maintenance plan to your exact need and purpose. The process of joining the tasks together to make a cohesive maintenance plan is what I call the “workflow” of the plan. Without this, the first thing would just run and then stop. Nothing else would execute unless explicitly told. So, to get around this, what we do is define constraints on the success or failure of a task; that way, we can at least track the error if something goes wrong.
Summary
Let’s dive into this adventure! You can use any installation of SQL Server you would like, provided that you have SSIS installed. That implies any version after and including SQL Server 2005, and it must be Standard, Enterprise, or Business Intelligence versions. Although this book was specifically written with SQL Server 2012 in mind, I also ran everything through SQL Server 2014 and had absolutely no issues beyond slight interface differences.