Stuart Moore

Musings of a Data professional

Stuart Moore

Author: Stuart Moore Page 4 of 18

Nottingham based SQL Server DBA who dabbles with many other technologies. 15+ years of experience with databases and still keen to learn and explore.

31 Days of Backup and Restore with dbatools

By

On May 1, 2020

In 31 days of Backup and Restore with dbatools, dbatools, powershell

So I’m doing another 31 days of posts, hopefully consecutive days but given current pandemics and sporadic working hours it may not be.

This is the index of all the posts, I’ll update it as each one is posted.

Posts

 

Please clean down your SQL Server backup history

By

On April 21, 2020

In sql server

From looking around at some of the Issues that get logged for dbatools, it looks like people never clear out their SQL backup history from msdb. This causes slow queries and potentially misleading backup trees to be returned.

In my (not so humble) opinion you really shouldn’t let your backup history fill up with, let’s be honest, useless junk. In this post I’ll go through why I think you should be keeping way less in there than people think you should

It’s not an audit trail

A common reason people give for keeping years worth of backup history records is that they need it to show the auditors. I’ve yet to find an auditor who wants a raw SQL database of database to look at.

The options I’ve used in the past are to use:

  • Export it to Excel or CSV on a regular basis. Easier to hand off to an auditor, compresses well and get’s it out of your database
  • Monitoring system records. You are monitoring your backups? In that case, you should have record of no failed jobs.

Please check what your auditors are actually asking for and why. Unless they’re also asking for verification and restore test results pure backup history is worthless. Because as a number of people have said over the years (in various ways):

An untested backup is just a wish that may not be granted

Me, probably

Will you ever go that far back?

So you’ve got 2 years worth of SQL Server backup history tucked away in msdb. Now, be honest, are you ever going to use it? Really, you’re going to roll a database back 6 months? Why?

That would be a hell of a lot of data to throw away to /dev/null or have to import all over again. Would there ever be a business case for doing that?

And once you’ve restored it, that leads on to…..

Once it’s restored, it’s a new database

You restore a SQL Server database and you start 2 new things:

  • A new Recovery Fork
  • A new LSN chain

All databases are running along a Recovery Fork (identified by a RecoveryForkGUID), even a new one will have one. You perform a restore, and SQL Server will generate a new Recovery Fork under the covers. Much like Ghostbusters, SQL Server won’t let you cross streams

As covered elsewhere on here, SQL Server uses LSNs (Log Sequence Numbers) to track database actions, and as backups are database actions they are also linked to LSNs. You’ll be taking the database back to an LSN in the past, and restarting the count from there. This is why SQL Server uses the recovery fork. Even if you were lucky enough to have backups starting on the same LSN (and it’s the sort of luck where I’d be asking you for the lottery numbers) you still won’t be able to use the backup.

So if all the backups are still in the backup history they’re useless, and potentially very confusing when you come to do a restore

Can you even restore it?

If you’re storing 2 years of old backup history, are you also storing the old backups as well? If you don’t have the backups to restore, again the history is worthless and might as well be deleted

I’m pretty hot on backups, but even I don’t keep things that long. Currently we’ll keep everything for 2 weeks, then we’ll get rid of Transaction Log and Differential backups. After 4 weeks we’ll only keep the Full backup from the last day of the month, and then at the end of the year we’ll only keep the last Full backup

So again, why keep the history if I can’t use it to restore most of the databases it contains?

Do you need that granularity?

As mentioned in the last reason you may end up with just a few backups kicking around that may need restoring for investigations or legal reasons. Do you need the backup history to restore those?

With tools like Restore-DbaDatabase I’d say no. While generating the restore chain from a folder full of backups may take a while, for a single database backup you’re only looking at seconds to scan the file(s) and be restoring. Probably quicker than trying to find the records in a bloated backup history table 😉

So what do I suggest

I’d suggest really looking at why you’re storing all that probably useless backup history.

Personally I consider anything more that a months worth as being too much, you (and your boss) may think that’s a bit tight. But I can’t ever see us needing to roll backup to a point in time over a month ago. Most of the times we do need to, the end of the month will be good enough.

So, to clear down the backup history, schedule sp_delete_backuphistory to run once a week like so:

use msdb
go

declare @oldest DATETIME  = Getdate()-28;
exec sp_delete_backuphistory @oldest_date=@oldest;

This will get rid of ALL backup history over 4 weeks old from ALL databases. This will work on SQL Server from 2005 upwards

Or you can enable the ‘History Cleanup Task’ in your maintenance plans.

Or if you’re using Ola’s great SQL Server maintenance scripts they’ll install a sp_delete_backuphistory job for you

So, go on. Get those deletions automated and make your life easier 🙂

Feedback

Obviously, all of the above is just my opinion and thoughts on best practice I’ve picked up over the years. You may have a need to keep years of backup history, and I’d love to hear what it is.

dbaSecurityScan update

By

On April 18, 2020

In powershell, sql server

As mentioned before, I’ve started working on dbaSecurityScan, a PowerShell module for tracking changes to a databases security model and bring the database back in to line.

Making the most of not being able to leave the house for very long I’ve been cracking along on getting the basics in place. Unlike a PowerShell module like dbatools, this one doesn’t break down nicely into small lumps so it’s likely that new features will be added in big lumps until we hit a critical mass.

So, I’ve just pushed up a nice big change that shows some of the core features working properly. In the current version we have:

  • Creating a database security config from an existing database for the following security principals
    • Users
    • Objects
    • Roles
    • Schemas
  • Test the database security config against a database
  • Take the test results and generate a list of actions that need to be undertaken to bring the database back inline
  • Run a ‘WhatIf ‘ run of the fixes to generate a list of actions to be undertaken
  • Apply the fixes to bring a database back in line, with a choice of running all actions, just those that add/grant security measures or just those that drop/revoke security measures

Some code to show how those features currently work:

# This is based on the module state on 18th April 2020, it will have moved on since then!
Import-module dbaSecurityScan
# Create config and test against a new database
$config = New-DssConfig -SqlInstance server1\sql2017 -Database db1
$config | ConvertTo-Json -Depth 5 | Out-File \\file\store\db1-SecurityConfig.json
# time passes and we want to check for security drift
# hydrate the config back into an object
$config = ConvertFrom-Json -InputObject (Get-Content \\file\store\db1-SecurityConfig.json -raw)
# Test the database against the config and vice versa
# This will run all the tests and store the results of pass/fail in $testresult.
$testResults = Invoke-DssTest -SqlInstance server1\sql2017 -Database db1 -Config $config
# If you don't want to see all of the Pester output as well, then you can
$testResults = Invoke-DssTest -SqlInstance server1\sql2017 -Database db1 -Config $config -Quiet
# Now we can use the results to bring the database back to security baseline
# Let's first sanity check any fixes, -OutputOnly will just output the intended actions and not apply any.
$fixResults = Reset-DssSecurity -SqlInstance server1\sql2017 -Database db1 -TestResult $testResults -OutputOnly
# Once you've approved the changes, then can be applied like so:
$fixResults = Reset-DssSecurity -SqlInstance server1\sql2017 -Database db1 -TestResult $testResults
view raw dbaSecurityScanFixExamples.ps1 hosted with ❤ by GitHub

On the to do list for the next couple of weeks of lockdown are:

  • Add fixing functions for roles and objects
  • Add Pipeline support
  • Add better comment based help
  • Allow comparison of config documents

Please get in touch if you’ve got any ideas for features. If you want to lend a hand then I’m happy to take Pull Request over at https://dbasecurityscan.io/.

Please also report any bugs/issues over on github as well.

Data Platform Discovery Day 2020 Agenda available

By

On April 16, 2020

In Uncategorized

After sifting through a huge heap of great session submission we finally got it narrowed down to 10 sessions for the US day and the European day.

We’ve got some really top quality speakers and sessions for you. We’ve got introductions to Power BI, Azure Data Factor, Containers, SQL Queries and using Azure Notebooks for Data Science

To have a look for yourself the agendas are here:

Data Platform Discovery Day 2020 – US Agenda

Data Platform Discovery Day 2020 – European Agenda

You can attend one, or both if you don’t mind a very early morning or very late night. Registration is for the entire day, but you can drop in and out for the sessions you want to see.

To register head over to these links:

Data Platform Discovery Day – US Registration

Data Platform Discovery Day – European Registration

More details are available from the event website – Data Platform Discover Day website

Custom Teams Backgrounds on Mac OS

By

On April 15, 2020

In Uncategorized

With the current pandemic enforced usage of Teams over chatting at the water machine I wanted a way to hide the background.

My wife’s a nurse, so the home ‘office’ is hers for patient confidentiality atm, which means I’m nomading around the house. This means my background varies a lot, so having something different than an untidy shelf of books, the kitchen counter or a blinding window glare behind me would be nice

There’s plenty of windows hints on having a custom Teams background on Windows, but not so much about having them on a Mac. With a bit of digging it turn out that if you want a custom Teams background on your Mac it’s quite simple after all.

All you need to do is to drop a file into:

~/Library/Application Support/Microsoft/Teams/Backgrounds/Uploads

If you’re not used to *nix paths, the ~ translates to your home directory. So for me on my Macbook Pro with the username stuart, that ends up being

/Users/stuart/Library/Application Support/Microsoft/Teams/Backgrounds/Uploads

Just drop the images you want Teams to use as a custom background into that folder and they’ll show up in the gallery.

It seems Teams isn’t picky about which file format you use for a custom background. I’ve tried png, jpeg and gif, and they all work fine. Unfortunately animated gifs don’t work, which is both a good and a bad thing depending on your personal love of gifs

Announcing Data Platform Discovery Day

By

On April 3, 2020

In Uncategorized

With a number of the large in person Data Platform conferences and meetups having to cancel or take a hiatus due to the current Covid-19 outbreak, Matt Gordon (b | t) and myself decided to do something about it.

So we came up with Data Platform Discovery Day. A 2 day online event aimed at those people who are looking for a start in the Data Platform world or are wanting to learn some fundamentals about an aspect of it.

Each day with run alongside business hours across a different continent, and will feature ten 50 minute sessions. Each day will have different speakers, and if you want there’s nothing to stop you attending both if you fancy an early morning or late night

Day 1 is running during US daytime on 29th April. The first session will begin at 9am Eastern Time, and then a new session will being at the top of each hour

Day is running during European daytime on 30th April. The first session will begin at 9am BST (UTC+1), again with a new session starting at the top of each hour.

Registration for the events with start once we’ve finalised the speakers.

And talking of speakers, the Call for Papers for both events are open. You can submit to both events if you want. Please remember that sessions are 50 minutes, and content should ideally be Level 100 material.

US Data Platform Discovery Day call for papers

European Data Platform Discover Day call for papers

We’d love to encourage first time speakers as well, so if you’d like ask any question then please get in touch with me (twitter) or Matt (twitter)

dbaSecurityScan – A new PS module for SQL Server security

By

On March 30, 2020

In powershell, sql server

While doing some work on my materials for SqlBits training day I started thinking about a few of the problems with managing SQL Server permissions.

How easy it to audit them? If someone asks you the DBA exactly who has access to object A, can you tell them? How do people get access to that object, is it via a role, a schema or an explicit permission?

Is that information in an easy to read or manipulate manner?

How do you ensure that permissions persist between upgrades? I’ve certainly seen 3rd party upgrades that have reset database level permissions. Do you have a mechanism to check every permission and put them back as they were?

We’re all doing the devops these days. Our database schema is source controlled, and we’re deploying it incrementally in pipelines and testing it. But are we doing that with our database security?

So in the classic open source way, I decided to scratch my own itch by writing something. That something is dbaSecurityScan, a PowerShell module that aims to offer a solution for all of the above.

The core functionality atm allows you to export the current security state of your database, based on these 4 models:

  • Role based
  • User based
  • Schema based
  • Object based

You can also return all of them, or a combination of whatever you need.

At the time of writing, getting the security information and testing it is implemented, and you can try it out like this:

Import-module DssSecurityScan
–Create config and test against a new database
$config = New-DssConfig -SqlInstance server1\sql2017 -Database oldProd
Invoke-DssTest -SqlInstance server2\sql2019 -Database newProd -Config $config
–Save a config, and then use it audit after a change
New-DssConfig -SqlInstance server1\sql2017 -Database oldProd -ConfigPath ~/git/dbSourceControl/permissions.json
Start-Sleep -Seconds 10080
$config = ConvertFrom-Json -InputObject (Get-Content ~/git/dbSourceControl/permissions.json -raw)
Invoke-DssTest -SqlInstance server2\sql2019 -Database newProd -Config $config
view raw dbaSecurityScanBasics.ps1 hosted with ❤ by GitHub

If you’ve got any suggestions for features, or want to lend a hand then please head over to dbaSecurityScan and raise an issue or pull request respectively 🙂

Working around sqlcmd on Mac OS issues

By

On March 27, 2020

In Uncategorized

So I’m busy working on my new dbaSecurityScan module, busily trying to write all the pester tests for the appveyor pipeline

Part of the testing for the module involves building a number of test scenarios, each of which of which needs a database spinning up for it.

On the Windows builds, that’s nice and easy as I just use this in the test script to loop through all the scenarios and run the .sql files :

ForEach ($file in (Get-ChildItem "$PSScriptRoot\scenarios" -File -Filter "*.sql" -recurse)){
(& sqlcmd -S "$sqlInstance" -U "sa" -P "Password1!$£$" -b -i "$($file.fullname)" -d "master")
}
view raw gistfile1.txt hosted with ❤ by GitHub

However, I’m developing on my Macbook Pro as I want this module to be nicely platform agnostic. SQL Server is running nicely in docker and dbatools can connect to it happily. But I just can’t get sqlcmd to work, lots of TCP Provider: Error code 0x102 and TCP Provider: Error code 0x2AF9 messages no matter what I try. So being short on time I though I’d try something else, and hopefully this will work when I get Linux build running as well

The scripts I want to run create databases, users, schemas and whole lot more, so there’s lots of batch separators (; and GO) in them.Invoke-DbaQuery is a great function, but it doesn’t like working with batch separators (which is not through want of trying, it’s just really tricky). So time to drop back to some raw SMO to run the scripts in with ExecuteNonQuery()

if ($script:IgnoreSQLCMD) {
$srv = Connect-DbaInstance -SqlInstance $script:appvSqlInstance -SqlCredential $script:appvSqlCredential
ForEach ($file in (Get-ChildItem "$PSScriptRoot\scenarios" -File -Filter "*.sql" -recurse)) {
$c = Get-Content $file -Raw
$srv.Databases['master'].ExecuteNonQuery($c)
}
} else {
ForEach ($file in (Get-ChildItem "$PSScriptRoot\scenarios" -File -Filter "*.sql" -recurse)){
(& sqlcmd -S "$script:appvsqlInstance" -U "sa" -P "Password12!" -b -i "$($file.fullname)" -d "master")
}
}
view raw gistfile1.txt hosted with ❤ by GitHub

I’ve added a variable to my builds that lets me pick when I want to run via SQLCMD or via SMO. At the minute I’m just using it on my machine, but if I run into problems later, it’s nice to now I can just toggle by looking at which platform it’s running.

Resetting conflicting DTC CIDs with PowerShell

By

On February 26, 2020

In Uncategorized

I’m currently migrating a lot of SQL Server instances onto newer virtual machines. Quite a few of these instances talk to each other via Linked Server for various historical reasons. And a lot of that chat is done via distributed transactions, which means configuring MS Distributed Transaction Coordinator.

So one of the first things I need to do, is check that that DTC is working between the 2 boxes. This is dead simple with official PowerShell module for msdtc. It’s as simple as running Test-Dtc and then working through any errors. So, we just run this:

Test-Dtc -LocalComputerName Source -RemoteComputerName Destination

and as this is a post about fixing something, it won’t suprise you that I’m going to get an error message:

"The OleTx CID on SOURCE and DESTINATION is the same. The CID should be unique to each computer."
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\MsDtc\TestDtc.psm1:266 char:13
+             throw ([string]::Format($Strings.SameCids, "OleTx", $Loca ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: ("The OleTx CID ...each computer.":String) [], RuntimeException
    + FullyQualifiedErrorId : "The OleTx CID on SOURCE and DESTINATION is the same. The CID should be uniqu
   e to each computer."

But in much more fetching shared of red and yellow, announcing that “The OleTx CID on SOURCE and DESTINATION is the same. The CID should be unique to each computer.”

The cause is really simple to grasp. When the Distributed Transaction Coordinator is installed it registers a GUID to identify it, the theory being a GUID clash should be a vanishingly rare occurance.

That is, until someone’s cloning Virtual Machines. So I have a batch of shiny new VMs that all think they’re the same instance of DTC. That’s not so good. It used to be the accepted fix was to manually remove the the distributed transaction coordinator, clean the registry, restart and then reinstall everything. That sounds like a lot of work to me!

The msdtc module makes it very simple to do, so we’re staring off here:

PS C:\Windows\system32> Get-Dtc | Select-Object *


DtcName               : Local
KtmRmEndpointCid      : 72c409a9-9c7b-4d24-9e0c-b946a2e5aa4c
OleTxEndpointCid      : 3eb9ce34-4d2c-48cf-9ebe-d6e888f9b0ca
Status                : Started
UisEndpointCid        : c5f16d32-01c9-4b65-be57-4521fa4bb934
VirtualServerName     : SOURCE
XAEndpointCid         : fcec2fe2-eab2-4277-853a-6ea4d7736430
PSComputerName        :
CimClass              : root/MsDtc:DtcInstance
CimInstanceProperties : {DtcName, KtmRmEndpointCid, OleTxEndpointCid, Status...}
CimSystemProperties   : Microsoft.Management.Infrastructure.CimSystemProperties

While DTC normally installs with a default log path, we’ll just make sure and grab it first. Then it’s just as simple as Uninstall-DTC and Install-DTC:

$logPath = (Get-DtcLog).path
Uninstall-Dtc -Confirm:$false
Install-Dtc -LogPath $logPath -StartType AutoStart

And to check it’s worked, lets query dtc and check:

PS C:\Windows\system32> Get-Dtc | Select-Object *


DtcName               : Local
KtmRmEndpointCid      : 72c409a9-9c7b-4d24-9e0c-b946a2e5aa4c
OleTxEndpointCid      : 3eb9ce34-4d2c-48cf-9ebe-d6e888f9b0ca
Status                : Started
UisEndpointCid        : c5f16d32-01c9-4b65-be57-4521fa4bb934
VirtualServerName     : VMCLSTR-IVANTI
XAEndpointCid         : fcec2fe2-eab2-4277-853a-6ea4d7736430
PSComputerName        :
CimClass              : root/MsDtc:DtcInstance
CimInstanceProperties : {DtcName, KtmRmEndpointCid, OleTxEndpointCid, Status...}
CimSystemProperties   : Microsoft.Management.Infrastructure.CimSystemProperties

And there we have a new unique OleTxEndPointCid and we’re good to go


Using Log or Audit data responsibly

By

On February 15, 2020

In Uncategorized

A couple of days ago I walked through getting information about data deletion uing the SQL Server Transaction Log. We looked how you could find when and by whom data had been removed from a SQL Server table.

So you’ve got the when, and you’ve got the who, now how do you precede? Remember you could be holding someone’s job or professional reputation in your hands.

As a long time DBA I’m well aware that applications are not always the best written things, and instructions don’t always spell out correctly what an option is going to do.

In the case I was writing about I withheld the name from general knowledge, a single senior manager was made aware that I knew and would be investigating. Why did I do this?

In the case of this particular third party application , there were a couple.

  • Deleting data within the app should write data to an internal audit trail, this hadn’t
  • Users cannot generate their own SQL queries, so this wasn’t an ad hoc query either
  • The single row delete was in it’s own transaction
  • This particular app is badly written. We’ve seen it fail to correctly write it’s own ini files on exit
  • It has a habit of crashing out for no reason

So a bit like a lot of applications out there. Now I had some time to investigate I started by going through logs on the client Windows machine and trying to replicate on my own.

Eventually I traced it back to a coding error in the application where they processed an Update as:

  1. Delete old data
  2. Insert new data
  3. Write audit log

None of this happened in an encompassing transaction (from studying the Transaction Log). So when the app crashed just after step 1, the row was just left missing and nothing there.

To the user who was logged in at the time, they wouldn’t have been aware of the crash as the GUI doesn’t display app status. The crash also happened at 16:55, which is pretty much home time so they wouldn’t have looked at the record again

Now I had the evidence I could talk to the Senior manager and show that we needed to raise this with the supplier, and that the user was blameless, but that we should recommend to users that they should double check any updates to be sure.

Releasing the name to a less trusted source without this background information would have probably led to the user being blamed for something that wasn’t really their fault

So, just because you have the information it doesn’t mean it’s actionable with adding more context and knowledge around it.

Page 4 of 18

Previous

Next

Powered by WordPress & Theme by Anders Norén