Voice of the DBA

Tag: administration

  • Getting SQL Agent going for SQL Server on Linux–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    It’s actually a simple procedure, but I thought I’d write a short note to help me remember. The procedure is documented in Books Online.

    When I first started playing with this version, I noticed that SQL Agent was disabled. That’s not great, since SQL Agent is a great tool for various tasks in SQL Server. I can’t start the agent from here, as the underlying implementation is different, and I’m not really a host OS admin when connecting in SSMS.

    2018-05-23 15_19_26-SQLQuery4.sql - ubuntu.master (sa (56))_ - Microsoft SQL Server Management Studi

    After checking which patch level I was at (CU6), I changed to my Linux console, and ran the configuration utility. For Linux, this is mssql-conf.

    2018-05-23 15_20_34-Ubuntu 64-bit SQL Server .210 - VMware Workstation

    The next step is to run the command listed to restart the system. I actually just ran

    sudo sysmctl restart mssql-server

    Once this was done, I refreshed SSMS.

    2018-05-23 15_22_52-SQLQuery4.sql - ubuntu.master (sa (56))_ - Microsoft SQL Server Management Studi

    This appears to work, but let’s test it. I’ll create a new job that does a backup of a database. This should be simple, and I’ll use defaults, just a filename for a full backup. I’ll use this command:

    backup database dbaadmin to disk = ‘dbaadmin.bak’ with init

    I save the job and run it. Sure enough, I have a backup.

    2018-05-23 15_28_37-Ubuntu 64-bit SQL Server .210 - VMware Workstation

    Tada, now I can move forward with work.

    SQLNewBlogger

    This is a fairly simple thing to do, but the writing helps me remember, but more importantly, I can document that I’ve done some learning here and experimenting.

    The next person thinking about interviewing you wants to know that you can learn and solve problems.

  • 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.

  • Checking Tempdb with dbatools

    I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

    In SQL Server 2016, the setup program was altered to better configure tempdb at installation time. This was in response to the observation that few people actually make any changes to the default configuration, which was suboptimal in SQL Server 2014-.

    Going through and checking all of the configurations you have isn’t easy, and isn’t necessarily the type of work that anyone wants to do. dbatools makes this really easy and quick with Test-DbaTempDbConfiguration.

    Using this cmdlet is easy. I’ll call this with an instance and get results of a number of checks that are useful for your tempdb configuration:

    2018-04-20 09_25_07-cmd - powershell

    This isn’t necessarily easy to read, so let’s add a Format-Table.

    2018-04-20 09_24_57-cmd - powershell

    That’s not great, as I’m missing the CurrentSetting field. I’ll add a SELECT and include the fields I want. I can even add multiple instances in here:

    2018-04-20 09_30_56-cmd - powershell

    Now I can scan through here, looking to see if my settings have deviated from the recommendations and best practices. This could easily be used to filter the results for items that don’t match, save the results as a CSV, and you now have a picklist of items to work on as you find time.

    dbatools is an essential tool for me. I’d urge you to download the module and experiment with the cmdlets.