Voice of the DBA

Tag: sql server

  • Continuous Learning

    It was a simultaneously busy, and also relaxing Tuesday for me recently. I attended Allan Hirt’s A to Z of Availability Groups at the PASS Summit. I’ve set up an Availability Groups before, but I was never sure that I completely understood everything happening in my lab, so this was a good chance to add some depth and color to my skills in this area. I met a few people surprised that I was spending time in a pre-con learning. They seemed to expect that I’d know most of what Allan was talking about.

    I know quite a bit about AlwaysOn and the related technologies, but I wouldn’t consider myself anywhere near an expert like Allan. I’ve fumbled through settings, but it’s a complex topic, and more importantly, it’s easy to misunderstand or confuse the subtleties of the technology. I went because a good, solid grounding in the technology, being led about in an organized fashion by an expert, is a good way to expand and solidify your knowledge. I saw other “experts”, MCMs and talented speakers in different pre-cons, each trying to continue to learn more about SQL Server.

    SQL Server is a big platform, one that’s wide in the number of features, and deep in complexity. No one knows everything about SQL Server, and most of the people I know that are extremely talented in areas of the platform, continue to grow their knowledge on a regular basis in a variety of areas.

    It’s the mission of SQLServerCentral to help you do the same thing, with our daily newsletter that brings you educational information about SQL Server. Hopefully you look forward to regularly growing your knowledge, just as I do.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn. 

  • Quick Schema Auditing

    I was working on a demo recently and needed to show that a little monitoring can help you catch schema changes. At first I looked at SQL Audit and DDL Triggers, but then I ran across a short custom metric on the SQL Monitor Metrics site that my company, Red Gate Software, put up to help people share their custom monitoring metrics and alerts.

    The metric is called Schema Modified, and it uses a really simple query. This is all is does:

    SELECT DATEDIFF(ss, '1970', MAX([modify_date]))
 FROM [sys].[objects];

    It calls this query every minute for each database on which you have it enabled. This gives you a count of the number of seconds between 1970 and the latest schema modification in your database.

    Now that’s not terribly useful, but if you look for changes in this metric, then it becomes interesting. For example, in one of my tests, I got this value

    1413904788

    If I continued to run the query,  the same value was returned if nothing changed in the database. However once I added a new object, then the value changed to

    1413905295

    That’s an increase, and my alerting was looking for changes in the value, so when this new count of seconds appeared, an alert was raised.

    Using the Information

    What good does it do you to know that something changed? Admittedly, this may or may not be useful. This doesn’t tell you what changed, and certainly help you determine who changed things.

    However, in more than a few of my development jobs, we knew people would change things. That wasn’t the issue. Really we wanted to know that something changed, and if so, we would investigate further. Often we could easily determine who made the change, based on what it was.

    This is really a trigger more for something like production, where I don’t expect changes, except when I deploy things. Any other change is cause for concern, and I might have alerts set to ping people when there’s a change. If we’re making the change, then we ignore the alert, because we’re aware of it.

    If we aren’t deploying changes, then we start investigating immediately.

  • Why Would You Move?

    I was reading a thread on Quora about why SQL Server is chosen by companies, and another on StackOverflow about why Oracle is a good choice. As much as I sometimes joke about the Oracle RDBMS, I think it’s a solid platform, and certainly wouldn’t resist working with Oracle databases if the opportunity presented itself. I like working with technology and enjoy learning about how different platforms work. However I also know that if I started working with Oracle, I’d be much less efficient, and certainly quite slower, in producing work than with SQL Server.

    I’d also probably make a lot of relatively poor decisions about how to run the database instance.

    I’m sure I’d get better writing PL/SQL, but that would take time. And in any setting, that also means that I’d be costing my company money while I learned the tricks, best practices, and skills needed to produce a well tuned, efficient Oracle-based application. It’s really no different than the way I have seen many highly skilled Oracle developers and DBAs come to work with SQL Server and try to treat SQL Server as if it ran the same way as Oracle (<shudder>cursors</shudder>).

    That’s why when I hear about companies making a quick switch to a new platform or language, I question the move. Certainly there are domains of problems that Oracle might solve better than SQL Server. There are situations where MongoDB is better than an RDBMS or Java makes more sense than C++, but there should be strong, solid technical reason why it’s worth trying something new. Not because a manager wants to save some licensing fees or a developer wants to try something new.

    By all means, experiment, but do so in small ways. Try new technologies with limited investments, and if they work, increase the investment. However for large projects, stick to what your staff knows best. Their skills are often the limiting factor in producing well written software, and in almost every situation, their salaries will far outweigh any cost of software that you license.

    Steve Jones

     

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn. 

  • Document, then Install

    I saw someone post a note that they had installed a new SQL Server and wanted to document the install. Did anyone have a good script or process for doing this?

    I’ve done this myself, but lately it seems to be the wrong way of actually building systems. One of the things I have started to feel is important is that I should strive for consistency and known states for systems rather than works of art.  However if I’m installing systems and then documenting them, I think that I’m doing things backwards.

    There has been a trend towards declarative actions in technology, whereby we tell the system what we want and it configures itself to arrive in that state. An example of how some this can be done is with Puppet. This is a case of the administrator essentially documenting what they want to be done first, and then letting the system put itself in that state. It’s almost like programming the installation and configuration of software, but with tools to make the process much smoother.

    In this model, administrators don’t need to document the installs. They’ve already declared what needs to be done. If vendors change defaults in the future it doesn’t matter, as the installations will configure themselves to the same state, ignoring defaults and human expectations. This also results in much more consistently configured systems, something that’s critical for building a smooth software delivery pipeline.

    The closest thing I’ve seen to this for SQL Server is the Finebuild project that Ed Vassie set up. That’s much better than unattended installs and I’ve seen Finebuild demo’d, and it looks good. I need to set it up so that I can ensure that every instance I set up is in a known configuration. I can have multiple configurations ready, but I’d rather choose one of my choices and then let the system apply the settings than remember to click a particular check box or change some item manually. I’d also appreciate this for DR and auditing, with some list of settings for each instance or host.

    That just seems more like a 21st century way of working to me.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn.