Voice of the DBA

Tag: sql server

  • Windows Server 2012 and Hyper-V

    hyper-v
    Hyper-V looks like a great candidate for almost any SQL Server with the enhancements in Windows Server 2012.

    I recently went to a Microsoft event in Denver on Windows Server 2012 and Hyper-V improvements. A bunch of the information was presented by Harold Wong (b | t) and there’s a number of demos and notes from the talks on his blog.

    I haven’t looked much at the Windows server OS’s in years and not much at Hyper-V. I have preferred VMWare for my demo/research environments, especially as I move between Windows and OSX regularly. However I’ve thought Hyper-V was rapidly improving and on the right track. I was surprised to find the new limits in Hyper-V under Windows Server 2012 to be quite high for both the host OS and the guests. You can have up to

    • 64 virtual processors
    • 1TB RAM
    • 64TB (vhdx format)
    • 4 virtual Fibre Channel adapters
    • much more

    With 320 logical processors and 4TB of ram on the host, it seems as though Hyper-V is on par with VmWare ESX 5. There’s a lot more to look at than software cost, but at this time, it appears all new virtualization projects using Windows ought to consider Hyper-V.

    There were interesting demos on replicas, live migration, improvements in file transfers and more. They were designed to make things look good, and there’s a good marketing presentation on the capabilities. I’m sure the actual implementation isn’t as easy or smooth as in the talks, but it did make me think there’s no reason virtualization shouldn’t be considered for SQL Servers, especially as you move to newer hardware.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • On Source Control

    A mention today from Jamie Thomson in his blog on source control. I left a comment, but it was long, so I thought I’d repost it here.

    My comment

    Thanks for the note, Jamie, and it’s interesting, but I don’t see any respondents that aren’t using VSC. I’m guessing many people don’t want to admit that.

    I use source control, sort of. I don’t have a lot of production code, mostly demo stuff, which doesn’t change so much as gets scrapped and restarted. However I am using Subversion (Turtle interface) with Red Gate’s SQL Source Control (of course).

    Why? Well, since I have a 1:1 with my boss today, I should mention Red Gate makes a fantastic product that I couldn’t do without. Actually I think I’m bound to use it since I work for Red gate, but even if I didn’t, it makes things easier.

    I built this habit at a startup, with Visual SourceSafe, and 6 months of browbeating developers to actually check out code from VSS, do a File | Open in Enterprise Manager (SQL 2k at the time), make changes, save the code, and check it back in. It can be done manually, and should, but there are tools like Red Gate’s products that make it easier.

    I know lots of people just edit code in SSMS/VS without controlling it, and that’s a mistake if you are trying to work in a team environment, and product quality software. I haven’t had to roll back often, but there are times we have needed to, and not having control was an issue.

    As an anecdote, I had a job once, waaaayyy back in the last century, using SQL 6.5. The developers had done a great job of using source control for all ASP, VB6, and SQL code. The problem was that they had checked out the code, saved it locally, copied it to another folder, and then checked it in. Then they might check back in the original script that was checked out. Or they might check the original back in and forget the modified version. Or they might not check anything in.

    We found 3, 4, even 7 copies of some stored procedures on various desktops and shared drives, and they had decided to encrypt all the code on the SQL Server. We were never quite sure what version was deployed where, and deployments of the "latest" versions from VSC broke functionality.

    We ended up decrypting all code, and reloading it into VCS, while removing all copies of local storage. It isn’t enough just to use source control or implement something; you need to understand what you are doing and do it consistently. Tools help here, so please use something.

    A Couple More Thoughts

    Source control is insurance. It’s risk reduction, but something that most people don’t understand or think they need. They may not. If you never have a server crash, or a hack of your data, then you don’t need backups. However most people wouldn’t want to run their systems, especially the ones they care about, without backups.

    It’s the same with source control. Use something to provide yourself with a fallback if you have issues.

    It’s not hard, there are lots of tools out there, and they make it easy to conform to some process. You can use SQL Source Control or SQL Connect from my company, or use something else, but integrate your database code with source control.

  • Important Alerts for SQL Server

    One of the things that I think is extremely important for DBAs and really anyone that has to administer a SQL Server instance is a set of alerts on various items that might occur.

    I ran across Glenn Berry’s article on Provisioning SQL Server and there’s a section in there that is titled “Setting Up SQL Server Agent Alerts”. It assumes you have Agent running (and you should), and includes a series of alerts that you can set to inform you when certain things occur.

    Glenn has alerts for severity errors 19-25, which are important, and then includes an alert for Error 825.  This error is indicative of possible corruption and at least some I/O issue with your storage. It’s important to have this error trapped, but in addition, I’d also recommend setting alerts for 823 and 824 errors.

    I would recommend modifying Glenn’s code to include this T-SQL as well.

    -- Error 823: Read Write Request Failure
DECLARE @Error823AlertName SYSNAME = N'I/O Alert - Error 823: Read or Write request failure';

EXEC msdb.dbo.sp_add_alert @name = @Error823AlertName,
              @message_id=823,
              @Severity=0,
              @enabled=1,
              @delay_between_responses=900,
              @include_event_description_in=1,
              @category_name=N'[Uncategorized]',
              @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name = @Error823AlertName,
@operator_name=@OperatorName, @notification_method = 1;
GO
-- Error 824: Read Write Request Failure
DECLARE @Error824AlertName SYSNAME = N'I/O Alert - Error 824: Logical Consistency I/O Error';

EXEC msdb.dbo.sp_add_alert @name = @Error824AlertName,
              @message_id=824,
              @Severity=0,
              @enabled=1,
              @delay_between_responses=900,
              @include_event_description_in=1,
              @category_name=N'[Uncategorized]',
              @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name = @Error824AlertName,
@operator_name=@OperatorName, @notification_method = 1;
GO

  • Manage By Delegation

    powershell
    Powershell might be a great skill if you need to manage lots of instances.

    More and more SQL Server instances are being deployed all the time. In fact, with the ease with which we can build a new virtual machine (VM) through snapshotting and cloning, it seems that many administrators are finding that the number of servers for which they are responsible might be doubling or tripling.

    Even moving to the cloud doesn’t completely remove the need for some administration of your data and databases, though it does require you to rework the type of administration that you perform. I foresee more hybrid solutions over time, which will require DBAs to not only manage data, but help analyze the financial impacts of moving data (and analysis) to, or back from, the cloud.

    In SQL Server 2008 we had the chance to begin managing our servers through a set of declared rules with  Policy Based Management (PBM). I haven’t seen that feature take off, and it seems relatively few people are using PBM to manage their servers. I think it’s a great platform for ensuring that your instances are conforming to certain rules, though I think there is a bit of creativity needed to ensure that this system works well for you.

    Powershell is becoming integrated into all Microsoft products. Virtually everything in SQL Server, perhaps even every thing by now, can be managed through Powershell scripts that access the SMO objects. I hear various people say that Powershell is a critical skill for DBAs of the future. I’m not sure of that, but I do think it will be used more and more if you have the need to perform repeated actions on multiple servers. Whether you use it now or not, it doesn’t hurt to learn how it works and what it can do for you.

    It just might be the tool to make your job easier as you get more and more instances to manage, something that seems to happen more and more.

    Steve Jones

    The Voice of the DBA Podcasts

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