Voice of the DBA

Tag: administration

  • The Cloud in Large IT Shops

    Toyota is focusing on software the enhance its business.
    Toyota is focusing on software the enhance its business.

    I’ve seen many presentations and talks from companies that are using cloud services to replace traditional IT infrastructures and lower their costs. Often these presentations are from smaller companies that don’t want to hire an IT administrator, or buy server or learn how to host and manage that hardware. Plenty of small companies would prefer that each employee manage their own laptop and nothing more.

    However many of those strategies don’t match the situation for large companies. Once you’ve hired an IT staff and made an investment in hardware and facilities, can the cloud really help you? I had my doubts, but this article about Toyota makes me rethink those doubts, or at least many of them.

    The lesson from the article, for me, is that Toyota’s IT group is learning to be a lean part of the business; they are building applications and tools that internal employees and customers can use to work better. They’re not acting as a separate business that needs to build software and also manage and administer the platforms that run those applications. By building software for Toyota and its customers, and outsourcing other functions, the Toyota IT group is more focused.

    Will this work in the long term? Will there be security issues from hosting email and other applications? Possibly, but I’m not sure if the problems and issues they encounter will be any worse than those problems that might come from managing all the hardware themselves.

    I still think the idea of container data centers, running cloud platform software, makes sense for large companies, but I suspect that even if they host an application like Salesforce.com, the administration of the software will come from Salesforce and not internal IT server administrators.

    Steve Jones

    Advertisement: If you are looking to speed up your development process and reduce mistakes with Continuous Integration, you might be interested in thesewhite papers on automated deployment and CI for databases from Red Gate Software. It talks about how you can set up a process using various tools.

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Baselines

    You come into work one day and as you sit down, your phone rings. It’s one of the business groups complaining that the database is running slow. You check the server and find CPU at 80%, 800 pages/sec, disk IOps of 230 and 124 transactions/sec. Is the database the problem?

    Baselines are important to understand how your system is performing.
    Baselines are important to understand how your system is performing.
    Good DBAs know that baselines are essential. If you don’t know what values to expect from your server, it’s often hard to determine if the system is running slower than normal. Normal is something you need to define for each system, preferably in an automated way that updates your baseline over time.

    When building a baseline, however, how do you average out the information?

    That’s the poll this Friday. Let’s assume that you are examining the CPU percentage for a SQL Server and you have data points from every 5 minutes across the last month. What’s the average? Do you take the straight average? Do you break this down to hourly segments and then create further analysis that looks at different business periods?

    It can become problematic very quickly. Many of us have slow and busy periods. Do we want an average that’s perhaps lowered by the slow periods in our workload? Do we want to break out the averages for maintenance periods separately from normal operations? If you are looking to compare today’s values, do you look at yesterday’s for the same time period? Last week? An average of all points across the last week?

    Let us know what methodology you use and if you’d like to describe it in more than a paragraph or two, we’d love to have some articles published here on the site.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • If You Need To Fix Database Filename Extensions

    In a recent post I showed how the file extension for a database doesn’t matter. It can be confusing, however, and you might wish to “fix” the filenames to conform to the proper extension. How can you do this?

    Well, to change a file name, or location, you need to take the database offline. This is noted in the Books Online Move Database procedure. Why? Well, the files need to be physically changed in the file system (either a rename or copy), so there is downtime here. Locations are one thing, but what about renames?

    The rename is simpler, and if you script this, downtime is minimal. The procedure is the same as listed in BOL:

    • set the database offline
    • rename the file
    • run the ALTER DATABASE command
    • set the database online

    This is pretty simple. We want to run this code:

    ALTER DATABASE [NameTest2] SET OFFLINE
GO
ALTER DATABASE [NameTest2]
 MODIFY FILE ( NAME = NameTest2
             , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.mdf' )
GO
ALTER DATABASE [NameTest2] SET ONLINE
GO

    However that code misses item #2 from above. I can manually perform that step, which is pretty easy, or I can script it if I allow xp_cmdshell changes. I know this is a security risk, but I can enable it and disable it all in the script:

    EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO 
ALTER DATABASE [NameTest2] SET OFFLINE
GO
EXEC xp_cmdshell 'rename C:\"Program Files"\"Microsoft SQL Server"\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.ldf nametest2.mdf'
GO
;
ALTER DATABASE [NameTest2]
 MODIFY FILE ( NAME = NameTest2
             , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.mdf' )
GO
ALTER DATABASE [NameTest2] SET ONLINE
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

    Note in here that I need some quotes in the RENAME command inside the shell so that Windows handles the spaces correctly in the path.

  • Does the SQL Server Database Filename Matter?

    Do you know the basics of how to create a database? Hopefully you do and can do so without the GUI. However do you know the extensions are for database files? As of SQL Server 2012, these are the extensions:

    • Main data file – .mdf
    • Secondary data files – .ndf
    • Transaction Log files – .ldf
    • Full backup files – .bak
    • Differential backup files – .dif
    • Transaction Log backup files – .trn

    However these are merely suggestions, and dictated by convention. In fact, in the Files and Filegroup Architecture page, BOL says that the “recommended” extensions are those I’ve listed for different types of files. For backups, these aren’t documented since you can actually include different types of backups in the same file (Don’t do this).

    Here’s a quick test:

    CREATE DATABASE [NameTest1] ON  PRIMARY 
( NAME = N'NameTest1'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest.mdf' 
, SIZE = 2 )
 LOG ON 
( NAME = N'NameTest1_log'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest_log.mdf' 
, SIZE = 1 )
GO

    If you notice, I’ve created a database with one data file and one log file, both using the extentions “.mdf”. This works fine and the database is usable.

    I can do the same thing with ldf. 

    CREATE DATABASE [NameTest2] ON  PRIMARY 
( NAME = N'NameTest2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.ldf' 
, SIZE = 2 )
,
( NAME = N'NameTest2_Data2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2_data.ldf' 
, SIZE = 2 )
 LOG ON 
( NAME = N'NameTest2_log'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2_log.ldf' 
, SIZE = 1 )
GO

    In this example I even added a secondary data file. If I check the physical file locations, I see the files I created.

    cd_a

    Note that Explorer sees these as the type of file based on the extension it has associated with that filename, but that doesn’t affect how SQL Server uses the files. If I look in the properties for the database, I see the files listed as expected.

    cd_b

    These don’t affect the operation of SQL Server or the database at all, however they can be confusing for DBAs. I recommend that you stick with the customary extensions for SQL Server files.