Voice of the DBA

Tag: sql server

  • SQL University – Capacity Planning Week

    I am honored to be a guest lecturer this week for SQL University. There have been some amazing professors helping guide you through SQL Server this semester, and I hope that this week meets your expectations.

    The topic for this week is capacity planning. While not a critical task for DBAs on a regular basis, the failure to properly plan for the resources needed can easily become a critical problem at the worst possible time: when your systems are busiest.

    What is Capacity Planning?

    Capacity planning involves making estimates of the resources that will be needed in some area or system and then ensuring that you can acquire those resources to meet demand. The resources can be time, money, hardware, people, or just about anything that is used in your environment.

    The traditional method of capacity planning deals with the resource requirements that your systems need. For SQL Server this has primarily been CPU, memory, and disk resources for each instance. While this seems to be a fairly benign and perhaps boring topic, it has enough moving parts that it quickly can overwhelm a DBA tasked with planning the resources needed and budgeting for them appropriately. There are other potential issues, and some of those will be discussed on Friday.

    As our systems become more complex, include more interconnections between them, and become more important, the ability to properly plan for, and meet, the demand for your services is an important part of the data professional’s job. Even if you have server administrators, SAN administrators and other professionals responsible for physical hardware, if your database is not available or does not scale, you will be blamed.

    Server Sizing

    People new to managing database servers constantly ask the question “how big a server should I buy?” as if there is a standard size server for a particular number of users or databases. There isn’t one, and these factors don’t really apply to proper server sizing:

    • number of users
    • number of databases
    • number of transactions
    • number of records in your largest table

    While these numbers do seem to increase as the load on your server increases, resulting in the need for more resources, these aren’t the way that you can size a particular server.

    Ultimately for new systems you can only take a guess at what level of CPU, memory, and I/O you will need. Likely it will be a bad guess, and so most DBAs and server administrators try to buy the largest server hardware just in case database use is far beyond what they expected. Or they economize and wind up with a severely underpowered server and a large percentage of unhappy clients.

    For existing systems, you should have some idea of the load being placed on your systems. From this load, you can extrapolate to make predictions about future loads and plan accordingly to meet the demand.

    Baselining

    The only really good way to properly size a system is to extrapolate future needs based on the past usage of the system. For existing databases, it helps to have a process set up that determines a baseline of not only the performance of your server, but also the capacity peaks and averages over time. These will help you extrapolate for future demand on the database.

    For a brand new database, you can only take a guess. As good as your guess might be, you will still likely be wrong, but having a baseline process set up at the beginning can help you quickly decide by how much your estimates are wrong and adjust the hardware accordingly.

    There are a number of articles written, and any number of product to help with the setting up of a baseline for your servers. The important thing to remember is that this is unique to each server, each company, and each database. The way that Microsoft Dynamics runs on your server instance will be different from mine, even if we have the same number of users. Don’t examine the scores from hardware manufacturers or software vendors as a way of determining what capacities you need to plan for. Those are useful for comparing systems in a theoretical way, but you need something more concrete for your instances.

    You need an affordable baseline system that you can deploy to all your server instances. I have had good luck with home grown systems at most of the places I have worked. I have also used commercial off the shelf packages (COTS) with as BMC’s Patrol and HP’s Openview to record data, but often had to extract that data to run my own extrapolation calculations. In SQL Server 2008, Microsoft introduced the Management Data Warehouse, which does a lot of this monitoring for you as well, if you have Enterprise Edition. Technet has a performance baseline white paper that gives some good basic guidance on what to monitor in order to measure the performance of your system.

    The specifics of how you gather and store this information are not that important. Use whatever method is convenient for you. The important thing is that you keep this data over time. You do not need extremely detailed information over time, but rather broad averages that allow you to determine if your resource requirements are increasing.

    I used to track the performance at 5 minute intervals for all server instances across a month, and average that down to hour increments for the past year. This easily allowed me to extrapolate for future planning by loading the data into Excel and performing a linear regression analysis. This will produce a line graph that best fits your data points.

    Third Party Tools

    As mentioned, there are a number of products that can help you with baselining and measuring the performance of your systems. Any tool that measures the various Performance Monitoring counters can be used if you can store those measurements over time. What’s Up Gold, Unicenter, even SQL Response (from my employer, Red Gate Software) can help you determine a baseline for your system.

    While it can be a relatively simple task to build a monitoring system on SQL Server, there are a lot of factors to consider, and building a new piece of software might not be the best use of your time. There are often other servers in your environment that also need capacity planning. File server, mail (Exchange) servers, and more. Ask your system administrators if they have a system in place, or software available that can measure the load of your servers over time. It might make sense to use a system already available or purchase one rather than building your own.

    Moving Forward

    That’s all we have for this lecture. Please feel free to chime in on the topic capacity planning and specifically any systems or software that you have preferred for tracking a baseline over time.

    In the next installment of SQL University, we will examine disk capacity specifically as this is an area that most often administrators struggle with managing.

    If you have other links, your own blog, or comments to add to this topic, please feel free to leave a note/link below.

  • Core DBA Skill – Backing up the tail log

    I’ve never had to do this in production, and I’ve only practiced it a few times, but I think this is a core DBA skill. Along with being able to backup and restore your databases, you should be able to recover to a point in time. That can mean a tail log backup.

    I read this in Paul Randal’s blog recently and decided to practice it. So I created my own new database and added a few transactions:

    create database db5
    go
    use db5
    go
    create table MyLog
    ( Txt varchar(max)
    , LogDate datetime default (getdate())
    )
    go
    insert MyLog select 'No backup', GETDATE()
    go
    backup database db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_full.bak' with init
    go
    insert MyLog select 'Full backup complete', GETDATE()
    insert MyLog select 'Misc Transaction', GETDATE()
    go
    backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn' with init
    go
    insert MyLog select 'Log Backup Complete', GETDATE()
    go

    I’ve added a couple backups here. If I were to restore this full, I ought to have 1 row in this table. If I add the log restore, I’ll have 3, but I’ll be missing the last line that says “Log Backup Complete”. Now I’ll wreck the database, as per Paul Randal.

    use master
    go
    alter database db5 set offline
    go

    I then rename the mdf file from db5.mdf to db5xxx.mdf, essentially “deleting” it from view by the SQL Server service. When I set this db online

    alter database db5 set online
    go

    I get

    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db5.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
    Msg 945, Level 14, State 2, Line 1
    Database 'db5' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.

    As expected, we have a problem. How do I backup the tail of the log? Remember that file is still visible. We’ll use Paul’s trick to add NO_TRUNCATE to the command:

    backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn' with init, no_truncate

    I get a successful backup, so let’s test. Here’s my restore script, restoring this db as a new database on this instance.

    RESTORE DATABASE [db7]
    FROM
    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\db5_full.bak'
    WITH
        MOVE N'db5' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7.mdf'
    ,  MOVE N'db5_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7_1.LDF'
    ,  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7.BAK'
    ,  NOUNLOAD,  STATS = 10
    GO
    select * From db7.dbo.mylog
    go
    RESTORE log [db7]
    FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn'
    with standby = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK'
    go
    select * From db7.dbo.mylog
    go
    RESTORE log [db7]
    FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn'
    with standby = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK'
    go
    select * From db7.dbo.mylog

    When I go through this, I get result sets of 1, 3, and 4 rows respectively. I see all my inserts, so despite having a corrupted, destroyed, or renamed (in my case) MDF, I can get all my data back with the log.

    Learn and practice a tail log restore. It’s worth it.

    And don’t forget to set the db active:

    restore database db7 with recovery

  • Loss of Files Does Not a Suspect Database Make

    I saw a post recently by someone asking how to get a database to be suspect. They said they had added a data file and then remove it, but the database wasn’t suspect. This was true in the past (v6.x), but it seems that as the product has advanced, there are more database states, and things like a missing file do not force a suspect state.

    Instead the database on 2008 goes into an Offline status. Gail Shaw, who pointed this out to me in a blog, actually wrote her own blog on this topic, so I won’t duplicate the code.

    I stopped my local SQL Server, then renamed one of the db files as shown:

    suspect_a

    In essence, this file is “lost” to SQL Server, just like it had been deleted, moved, etc. When I restart SQL Server, I see this:

    suspect_b

    No way to expand out db2 here. If I look at properties, I see it in “Shutdown” status

    suspect_c

    Strangely, if I right click, I have the “Take offline” available, not not “Take Online”. If I click that, I get the database offline, but I cannot then bring it back online. It gives me an error, as it should since the files are missing. The error log shows the issues with

    suspect_d

    I can rename the file without taking SQL Server down, and then bring the db online. It’s the same as recovering this disk drive or fixing the file somehow.

    I’m glad this changed, since we should have more details about what happened, and a “suspect” database should be reserved for more serious issues. I think having this “shutdown” or “offline” is a better status.

    The databases status values are here: http://msdn.microsoft.com/en-us/library/ms190442.aspx

  • Truncating the Log and Database Mirroring

    I saw someone post recently that they wanted to run this code on their principal server in database mirroring to clean out a large transaction log and shrink it.

    alter database <mydb> set recovery simple
    go
    checkpoint
    go
    alter database <mydb> set recovery full
    go
    backup database pubs to disk = 'c:\mydb.bak' with init
    go
    dbcc shrinkfile (N'mydb_log' , 1)
    go

    I responded, and a few others as well, that this will break your mirroring session. However I wanted to do some research, and I started googling around to see what I could find.

    While I haven’t found an explicit reference, I did note that Paul Randal has a note that you can switch to Bulk Logged mode without breaking the log chain, but I knew that.

    I don’t have a definitive reference, though I’m hoping one appears soon. The documentation for Books Online does say that full recovery mode is required.

    If you have a log file that’s too large, you can run shrinkfile on it, even if it is mirrored. That will work, but don’t set the database to Simple mode. Manage the log backups, and make sure you have them running regularly. And if you need help shrinking the log, try this script.