Voice of the DBA

Author: way0utwest

  • Jobs in PoSh to Create a Load

    I saw this from Argenis Fernandez, and thought it was wonderful.

    1..128 | % { start-job -name ‘job name’ -scriptblock { & sqlcmd -S instanceName -U sa -P ‘iLovePoSh’ -i /home/username/test.sql } }

    Create 128 threads, each running a background job in PowerShell to connect to an instance and run a script file.

    Start-Job is used to begin a job, and then you can get information from Get-Job and use Stop-Job to turn things off.

    Very cool. Looking forward to trying to use this in a demo and make things happen.

  • Pause and Backtrack

    One of the main functions for anyone that manages a database is ensuring that they can recover the system in the event of any issues. My view is that restores are the most important skill and task that need to be performed on a database. Since restores require backups, I’d then rank backups as a 1a important task. They’re a dependency and necessity to ensure that we can restore data. Having a set of the data, in a transactionally consistent state just feels critically important to me, over everything else..

    I saw this new feature from Amazon Aurora for their MySQL compatible database. You can use Backtrack to rewind your database to a previous point in time. On one hand that’s an amazing feature. Make a mistake, have an error, click a few buttons and get the database restored back to the minute (or second) when you made a mistake. On the other hand, if you delete a table, do you want to roll all tables back to that point in time?

    This seems like an amazing feature. Amazon takes some of the hassles of managing some backups backups. You determine how far back you want to go, in hours, up to three days. Depending on the activity in your database, they charge differently. To me, that’s interesting. It makes sense to me as a customer. I do more, they track more, I pay more. This also seems to be a way to capture more money for Amazon by cutting some of the consumer surplus that exists with flat fee pricing, which is something many of us prefer.

    The way this works is also different than Azure. The Azure point in time feature allows you to go back, but you can’t restore on top of your existing database. You’d need to restore elsewhere, then play the rename game or move data between databases. While that seems inconvenient, if you’ve ever had someone restore a local SQL Server backup over a database you needed, you might appreciate the safeguards of not allowing a restore on top of an existing database. While the process might seem like a hassle, this does help prevent mistakes during a stressful situation.

    Which of these do I like? I prefer the Azure one, though I’d like the restores to be more granular than a minute. The reason is that I rarely want to restore in a disaster over the existing database. In most applications I’ve managed, there are updates to multiple parts of the database. A mistake in one table doesn’t necessarily mean that data changed in other tables should be discarded. Even during deployments, when things go wrong, I’ve often just broken one set of tables and rolling back the entire database in a restore is painful. Usually I’d prefer to undo what I can and get the any missing data from a restored copy of my database.

    Perhaps it’s just me, but I find the idea of allowing clients, or even many technical people, to easily roll back an entire database after a mistake to be very dangerous. By the time we recognize the mistake, verify data, notify others, we might have lots of changes in many tables. Abandoning that data for the sake of convenience is something that’s unnecessary. I also worry many people trying this feature don’t think through the implications of rolling back an entire database. If you feel differently, let me know. There are cases this is certainly helpful, but I think I’d rather have a “restore to a new db and rename both” automated task instead of AWS Backtrack.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Checking CHECKDB

    One of the recommendations for SQLServer is that you run a DBCC CHECKDB regularly on your system. Those individuals that have worked with SQL Server for some time and worked on learning more about the system know to schedule this check, and even ensure that you restore backups to check them on a regular basis. Note, running these checks on secondary systems may or may not be valid. You might want to read Paul Randal’s post on this.

    Experienced DBAs know that when corruption strikes, you can find yourself in a very problematic situation. Corruption can be captured in backups, which means that if it appears, it’s entirely possible that if this has been in your system for some time, all the backup files you have contain the same corruption. If this is inside a table, you might end up losing data, which is never what any of us want.

    Those that might not be familiar with SQL Server, or have never learned about regular maintenance might not realize that DBCC is needed. In fact, they might not know if CHECKDB has ever been run on their system. It used to be hard to find this, but things became easier over the years. When checkdb runs, it does write a note in the error log, but that’s not a great way to track this information for administrators, especially if the instance has been restarted. Without a set of tools in place, this becomes a project for anyone that starts working with a new system to track down.

    That changes in SQL Server 2016 SP2. There’s a new property for your database, a parameter for DatabasePropertyEx(). The value is LastGoodCheckDbTime, which in this case, you can get the last datetime when a checkdb was run. To me, this should be some sort of alert that your monitoring system has in place that lets you know if this value is too old.

    The problem is that for many of us, we may run DBCC CHECKDB on another machine, perhaps on a restored copy of production, so how can we track this? Is there a way that would make sense? I’d like to think that perhaps any script testing a backup should connect back to the primary database and update this value. There isn’t a specific place for this, but I certainly could see using extended properties for this. At least then we could more easily determine if we haven’t been regularly checking a particular database for corruption. If you have other ideas, I’d be interested in how one might actually track this.

    For now, use this property to ensure you’re checking those databases where you do execute CHECKDB, and if you aren’t sure if you should do this, you should. Go learn about what CHECKDB does and why it’s important for your production systems.

    Steve Jones

     

  • Update Oddness for SQL Server

    I’ve been meaning to patch some of my development instances. With a little spare time while doing other work, I started the process the other day. My first step was to patch SQL Server 2016 with SP2.

    This went as many patches have gone for me, which is fairly boring and easy. Other than having to wait to accept the R terms and then clicking next, it was quick. Afterwards I rebooted.

    Then things got interesting. I started the SQL 2017 CU6 patch. This extracted, as other patches have, but then I got a message that I wasn’t running this under enough permissions. To be clear, after extraction, the SP2 patch did give me a UAC prompt.

    I stopped and then re-ran the patch, right clicking and running as administrator. After a few Nexts, I got this screen:

    2018-05-21 14_47_47-Install a SQL Server 2017 update

    I haven’t seen a patch ask me to stop SSMS. I’m still not clear if this is necessary, but I did stop SSMS. The patch worked fine, and strangely, re-running the patch later didn’t produce the same messages.

    Odd, but things seemed to work and my system was patched, though even without SSMS, I still had a pending reboot.

    Any thoughts or explantions appreciated.