Voice of the DBA

Tag: sql server

  • High Availability Upgrades

     

    Is SQL Server 2012 worth the upgrade for you?

    SQL Server 2012 will be released soon. I have no idea of the exact date, though I suspect a peek at the SQL Server developers’ vacation schedule in Redmond might provide some clue. The announcement last year was a first half of 2012, so I do expect to see the product RTM sometime between now and July 1.

    One of the highly anticipated features in SQL Server 2012 is the Always Onfeature, which will dramatically increase the ease with which we can build databases that can not only tolerate hardware failures, but be deployed (physically) further apart, and with more flexibility in how backups can be taken. If you haven’t read about Always On, I’d urge you to start looking at this feature and see if it’s something that can help your company.

    Unlike Database Mirroring in SQL Server 2008 R2, which is limited to one secondary database, Always On will allow us to have multiple secondaries. The current mirror databases are unreadable, unless you count the kludgy database snapshot feature. In SQL Server 2012, however, we can actually use the secondary databases to query, providing some limited scale out capabilities. I think that’s fantastic, and it’s a large step forward for the SQL Server platform. I’m wondering if your company feels the same way.

    Will the read only secondaries available in SQL Server 2012 convince your company to upgrade?

    There are lots of other features in SQL Server 2012, some great advances in other parts of the platform that might be worth upgrading for, but I regularly see people struggling with scale issues. With the changes in licensing as well, these new scalability features are worth examining, and I’m wondering how important they are for your business. Important enough to justify the price of an upgrade? Let us know today.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • AdventureWorks in SQL Server 2012 RC0

    The sample databases for SQL Server 2012 haven’t been completely finalized, but I was able to find the latest version here for AdventureWorks. This download gives you an mdf, and that’s it. You can’t directly attach that in SSMS, but you can use T-SQL to do it.

    CREATE DATABASE ADVENTUREWORKS

    ON (FILENAME = ‘c:\SQLdata\ADventureWorks2008R2_data.mdf’)

    FOR ATTACH_REBUILD_LOG

    You get a note that the log file is invalid from the MDF, but a new log is created and you have your sample databases attached.

  • Backing up a Certificate

    If you create your own certificate in SQL Server, you need to make sure that you back it up immediately. Once you start to encrypt anything with a certificate, you increase the risk that you’ll lose your data if an catastrophic event occurs. In this case, if you lose the certificate, you can’t access the data.

    I talked about creating a certificate in another post, and there is corresponding DDL for backing up a certificate. BACKUP CERTIFICATE is the command you want to use, and it works like most of the other backup commands.

    Let’s assume I have the certificate named MySalaryCert from the previous post. To create the backup of this certificate, I’d issue:

    BACKUP CERTIFICATE MySalaryCert
 TO FILE = N'c:\SQLBackup\MySalaryCert.cer'
 WITH PRIVATE KEY
  ( FILE = N'c:\SQLBackup\MySalaryCert.pvk'
  , ENCRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You'
  , DECRYPTION BY PASSWORD = N'R3allyToughP@ssword4You'
  )
;

    This will generate two files for me in c:\sqlbackup as shown below.

    backupcert1

    The certificate was created with a password, so the backup must include the DECRYPTION BY option with that password. The password you use for the backup can be different, as shown, but you need to be sure that you manage this password properly. You will need it to restore the certificate, which I’ll show you next time.

  • Checking Your Service Account with T-SQL

    Somehow this slipped by me, but there were some new DMVs added in SQL Server 2008 R2 SP1. I suspect my test machines were mostly SQL Server 2008 or SQL Server 2012, and I hadn’t been paying attention to the changes in SP1.

    You can now use T-SQL to check for services information, as well as registry information, without using extended stored procedures or any hacks of xp_cmdshell. There are two new DMVs:

    These were not present in the RTM of SQL Server 2008 R2, but after installing SP1, they appear. The KB article for SQL Server 2008 R2 SP1 includes a note that new trace templates for Profiler are included, but I did not see a note about these two DMVs.

    So much for not adding features in Service Packs.

    In any case, you can query the sys.dm_server_services for service account information. You will get the service name, the startup type, the account, and more.

    If you aren’t a Windows administrator on your SQL Server boxes, you should still be able to get information regarding the services from this DMV as long as you have VIEW SERVER STATE permission.