Voice of the DBA

Tag: sql server

  • You Can’t Disable the CLR in SQL Server – MCM Prep

    At least not completely. There are some functions in SQL Server that are built on the SQL CLR, and even if you have disabled the CLR for user written code, it still loads app domains and functions for system code.

    An example of this is the spatial data type. It’s a SQL CLR written user defined data type, but written by Microsoft. If you create a spatial variable, the SQL CLR loads an app domain and code for the UDT using spatial data.

    Not a big problem, and not a security risk from my standpoint, but it is worth knowing if you have to debug into memory or some detailed traces/events and see the SQL CLR loading. Don’t panic, and understand that you can’t prevent this from loading for these system objects.

  • Backups after a DR Restore – MCM

    If you have a disaster, and you need to restore to some point in time prior to the last log backup you have, what’s the first thing you should do at this point in time?

    Make a full backup, of course.

    Actually anytime you complete a restore sequence, you want to do a full backup immediately before you turn users loose to do work. Why is that?

    Think about what happens if you complete the restore and then have another issue? You would have to go through the entire restore sequence again from before the first disaster. If that were a full backup, a differential, and a dozen log backups, would you want to explain that delay to your boss again?

    Would you even want to go through it?

    I wouldn’t, and it’s easy to prevent. Once you get done with your restore, start a full backup right away. You might never need it, but then again, you probably said that about the full backup you just restored.

  • A Variety of Interpretations

    What have you automated?

    Each month I look forward to T-SQL Tuesday week when there is a blog party. This is the brainchild of Adam Machanic (Blog|Twitter), and is usually hosted by a different person each month. I hosted December 2010, and that was probably the first time I wrote a post more than a day before the post date. It seems more people

    This month Pat Wright chose the topic of Automation, leaving it simply as that word to inspire people. His summary showed that 34 people participated and left trackbacks on his blog. I read through a number of them, and was struck that this month is seemed that people interpreted the topic very differently.

    You might expect that many people talked about SQL Agent, and they did, but there were some fascinating thoughts on how automation impacts an environment. Jason Bacani talked about data driven subcriptions in SSRS, which many people might not be aware of. These solve the problem of deliverying reports automatically to people, but in this case, the reports were not working. A little automation solves the problem and keeps the business running. Ted Krueger helps the DBA out by giving us a script to remove NOLOCK from code. An interesting way to enforce the rules that you might want to have in your environment. Grant Fritchey talks about keeping server side traces running all the time with a little automation to manage the process.

    There are a variety of other techniques, including getting a testing environment setup with Powershell. These ideas are inspiring, and they are the reason that I think events like T-SQL Tuesday are a great idea. They make me think widely, and re-examine the tools that I have with SQL Server, and how I might deploy those tools in a new way.

    Check out T-SQL Tuesday next month, and contact Adam if you’re interested in hosting one in the future.

    Steve Jones

  • OldSQL

    Support is expiring for SQL Server 2005. Does it matter to you?

    Support for SQL Server 2005 is due to expire in a few months, which means that no new patch work will be performed on that version for the general public. Some people might sign extended support agreements, but most won’t. If you are unsure of whether you should, this blog post might help you understand the implications of not having an extended support agreement.

    There have been quite a few blogs, tweerts, etc. about this date, asking people to  consider upgrades to SQL Server 2008 or SQL Server 2008 R2. It is good to see some awareness being brought to the situation, since many DBAs do not think about their version falling out of support, and assume they can get patches from Microsoft at any time.

    They can’t, but does it matter? I know there are still plenty of people running SQL Server 2000 right now and it performs well. They have stable applications, and stable hardware, and with virtualization, they could potentially run stand-alone systems on old versions of Windows or SQL Server forever. I even know of people still running SQL Server 7, or SQL

    Server v6.5 today in support of some applications.

    If you have a stable codebase, and your application has been running on SQL Server 2005, or even SQL Server 2000, I don’t think that support timelines necessarily mean you should upgrade. At some point you should upgrade, to ensure you can take advantage of new connection technologies, or better security, or some other feature, but not just because support is ending.

    Evaluate your environment, and upgrade when you think it benefits your business and you get value from the new version. Otherwise you are allowing your budget to be managed by someone else, on their timetable, not yours.

    Steve Jones

    (published at http://www.sqlservercentral.com/articles/Editorial/72436/)

    The Voice of the DBA Podcasts