Voice of the DBA

Author: way0utwest

  • The Copy Cat Poll

    copy cat shirt
    How many copies of data do you need in our organization?

    One of the interesting facts I saw a few years ago talked about storage in enterprise environments. There was research that showed many enterprise applications had 6 or 7 copies of their large databases inside the organization. In addition to the production copy, there were many other copies in use, resulting in an explosion of growth. That wasn’t surprising, and it was one of the drivers for implementing compression in many databases.

    While the cost of storage is constantly coming down, it’s still expensive for enterprise class hardware, especially in a large SAN device. Today I wanted to ask those of you that work on real world systems to make a quick count of your own system, and let us know. I can’t decide if 6 copies of a production database is high, or low.

    How many copies, on average, of your production databases are in your company?

    I suppose you could count backups as a copy, since it’s disk space usage and you have to pay for it. If you count backups, let us know, but I’m thinking just about the test systems, development systems, HA or DR systems that might receive copies of the data. Some of those secondary systems might be in use for other purposes, such as reporting from readable secondaries in an AlwaysOn scenario. Whether they are or not, they are still copies of your database.

    I used to think that four or five copies would be a lot, but with the advances in technology that allow different DR options, and the cheap local storage available on today’s desktops and laptops, I wonder if seven or eight copies might be more accurate.

    Take a count today; you might surprise yourself with the results.

    Steve Jones

    If you are looking to reduce the cost of storing all those copies of your data, take a look at SQL Storage Compress, Virtual Restore, or SQL Backup Pro from Red Gate Software.

    The Voice of the DBA Podcasts

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

  • Who’s Responsible

    This is really scary. Someone being sued for downloading a porn movie illegally. I have nothing against porn, and I certainly think that downloading stuff that’s a copyright violation shouldn’t be mainstream as in Amazon giving away movies for free without compensating owners of the copyrights.

    Setting that aside, there’s something fundamentally disturbing about our computing devices, which are assumed to be under our control, but may not be, even when they are.

    I need to write more, but as a quick analogy, if I’m using my car, no one else can use it. At least not without me being somewhat aware of the actions. Someone could hide contraband in my trunk, where I rarely look, but they’re not going to get it transported anywhere I’m not going.

    Contrast that with your computer. You could be infected with a virus (as an all-encompassing term for rouge processes) that could potentially be downloading (or uploading) something you don’t expect, including copyrighted content, or even attacking other computers (for example, as part of a DDOS botnet). It could do this while you were working on a flyer for your kid’s birthday party in Word, or checking your email.

    Is that your fault? If you use an A/V program? If your kids hit a site returned in a Google search that installed malware? If you bought a piece of software that had it embedded? I’m not avoiding responsibility here, but it’s a thorny issue as to who’s at fault, and more importantly, to what extent do we expect people to be aware of what’s happening?

    Tough times ahead if we don’t get a handle on security and digital laws.

  • No Limits

    Google Compute Engine
    The Google Compute engine isn’t unlimited, but it’s got 770,000 cores and growing.

    We’ve had Amazon Web Services (AWS) available for some time, and used for some interesting projects that wouldn’t be possible if groups had to purchase their own equipment. From password cracking to cancer research, there are some amazing possibilities. Windows Azure works as well, and there are customers such as banks, using the cloud to perform complex analysis of data. Not to be left out, Google announced recently they were provide their Google Compute Engine to anyone, without any limits on scale. You have to pay for it, but if you want 770,000 cores to solve a problem, they’ll provide them if you can pay for the resources. It’s US$2m/day, but it’s there.

    I don’t know what that means for us as data professionals, but I suspect there will be any number of companies that will consider using these types of resources to work on complex analysis of problems. Running a simulation might be something that costs a few hundred dollars a day, using resources for minutes at a time. Looking over some of the different projects people have used cloud computing engines for, it seems that many businesses might find this to be a cost effective way to perform data mining or BI type workloads.

    If you can get the data to the engine. I still wonder how challenging this is, apart from all the security concerns. Just moving that much data around, refreshing it, removing the stale data, could be very complex. It concerns me that as more people attempt this type of work, we will have more and more poor decisions made because of data quality, age, or accuracy. After all, garbage in, garbage out has been a tenet of data professionals for decades.

    If you’re using cloud resources for computation, I’d love to know about it. I know many of the data professionals out there have reservations, and that’s healthy. It shouldn’t blind you to the possibilities that this is a most cost effective way of actually getting analysis and answers for your users.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Startup Jobs

    Are there things you need to make sure are running when your SQL Agent starts? Did you know that you can run a job when the Agent starts?

    I haven’t often used this, but there are cases where it was handy. In one system I administered, we wanted very fast inventory lookups for one of our groups. I know that we could have easily let the first few people just wait for their queries, but it constantly generated complaints from workers, and the VP in that area didn’t technically understand why. Explaining buffer pools, and caches weren’t an option.

    Our solution was a quick fix, which “hid” the problem. When the instance started, we had a startup procedure that ran, running a few queries that would load lots of data into memory. By the time people could connect to the server, it was usually warm, and complaints went away.

    A similar feature exists for SQL Agent. When you build a job, you can select a schedule that runs when the Agent starts. Imagine that I have a procedure that logs when my agent starts. I want this to log the datetime when the agent starts. I know it’s in the errorlogs, but if I have a lot of restarts in a short time, I could lose that information.

    I can create a table and procedure to log this:

    CREATE TABLE Startups
(
  StartDate DATETIME
)
GO
CREATE PROCEDURE spStartAgent
AS
INSERT Startups SELECT GETDATE()
RETURN
GO

    If I create a new job, I can enter a step that runs my job.

    I do that, and then click the scheduling tab (in SQL Server 2008 R2). Once I do that, I can drop down the Schedule type box, which defaults to “Recurring”.

    startup

    In the drop down, the first selection is “Start automatically when SQL Server Agent starts”. If I select this, all the other fields on the form get disabled.

    startup2

    I named this Agent Startup, so I can re-use this schedule if I need to. Now this schedule will run my job every time the Agent starts.

    If I restart my Agent (not my instance), and check my log, sure enough I see a note in my table that logs startups. However the execution of the job isn’t logged in the Agent error log.

    startup3

    Perhaps another good job would be to copy over the most recent error log into an archive folder, one where a hacker might not think to look. I could even set the rights for the agent to only allow files to be created, not altered or deleted. That way I could retain the file for auditing purposes.