When I started this blog a friend of mine suggested I write a really complicated query as a header. Now I’m not sure how complicated it really is, but I find it fairly amusing, and the whole point of it is to manipulate some text to generate a different set of text. So this seems like a good time to go through it and explain what’s going on.
There’s a bit to unpack, but it’s a fun experiment.
At 1am the time jumps straight to 2am. Got any jobs scheduled to run at 01:30? They ain’t going to happen. I hope they weren’t important.
He also has the start of a script which helps fix timing issues, either from losing an hour in spring or gaining an hour in fall. This is a timely reminder (no pun intended) that Daylight Savings Time begins on Sunday, March 13th this year in the US and March 27th in most of Europe.
What is a distributed availability group? Distributed availability groups allows you to create two different AGs on different Windows Server failover clusters (WSFCs) but join them together – if you will, an AG of AGs. This is great for disaster recovery scenarios where you do not want to worry (more than you should) about things like voters and quorum in a single WSFC which could make the configuration more complex. This also allows for different versions of Windows Server (one WSFC is Windows Server 2012 R2 and another Windows Server 2016). Heck, it could even facilitate migrations to new hardware/the public cloud/virtualization assuming the same major version of SQL Server. It’s a cool feature. You can only manually fail over the AG between the WSFCs, but that’s OK, and you do need a listener for each AG. If you do not plan on using a listener, you cannot create a distributed AG.
Side note: when I read DAG, I think directed acyclical graph. Maybe I’m just weird that way…
Main note: the idea of “seeding” an Availability Group sounds wonderful.
Each of these is part of a striped backup, a piece of a backup file. To restore the backup, I need all the files to reassemble the backup. This is fairly simple, but you should be aware of how this works and how to perform a restore.
In my case, you can see I have 7 files for each database. They are the same name with an _0x at the end, with x being the number of the file.
Take Steve’s advice and script out the restore process. That way you know how to do it next time, can start building automation scripts, and can make your life easier.
Mockaroo is a really impressive service with a wide spread of different data types. They also have simple ways of adding things like within group differences to data so that you can mock realistic class differences. They use the freemium model so you can get a thousand rows per download, which is pretty sweet. The big BUT you can feel coming on is this – it’s a GUI! I don’t want to have spend time hand cranking a data extract.
Thankfully, they have a GUI for getting data too and it’s pretty simply to use so I’ve started making a package for it.
Steph is working on an R package, so this is pretty exciting.
SQL Server is implicitly converting my date value to DATETIME2(7). That is a larger, more precise value than the data type I have in the table–FakeBirthDateStamp is DATETIME2(0).
That data type mismatch is preventing partition elimination!
This is a nasty issue to catch in production, especially after you spend a bunch of time arguing with devs that DATETIME2 is the way of the future, that it’s better because of the variable precision, etc.
RTVS is an IDE and as such you can use it with any recent version of R such as 3.2.x. If you install the free Microsoft R Open, you automatically get some turbo options such as threading support on multi-processor machines, providing significant speedup for a variety of analytical functions, as well as package collections check-pointed to a particular date/version. Microsoft R Server provides Big Data support and additional advanced features that can be used with SQL Server.
This is an early release, so expect a few bugs and some missing functionality. It also isn’t RStudio—it’s RStudio several years ago. But what it does nicely is integrate with the rest of your stack: you can tie together the R code, the C#/F# code which helps clean data, the SQL Server project which holds your data, etc. etc.
Gianluca Sartori (@spaghettidba) blogged about the issues you may have noticed with older versions of Management Studio on HiDPI, UQHD, or 4K/5K screens in his post, “SSMS in High-DPI Displays: How to Stop the Madness.” Essentially, Windows tries to scale things for your DPI settings and, depending on the technology used to render your fonts and dialogs, this can end up with ugly and sometimes unusable screens. Gianluca’s fix is to make a registry change and add a manifest file to the SSMS directory so that SSMS no longer tries to render Windows’ scaling changes. In the latest builds of SQL Server 2016, SSMS now acts as if the manifest file were in place. Take a look at the following image:
That’s music to my ears; I use a high-resolution laptop and before Gianluca’s solution, it was impossible to use SSMS. I’m looking forward to SSMS 2016, but probably won’t move until the add-ons I use are supported; I’ve grown to like them too much to make the jump, even on a trial basis.
The broader lesson here is to make sure you update your script libraries regularly – even if a script still runs and provides output (that is, you think it “works”) it doesn’t mean you are receiving valid data.
Although this example is about wait stats and wait types, it is applicable to a wide array of configurations and settings. Changes like this are often version-related, but even within a version it can be decided that a particular wait type/trace flag/sp_configure setting/etc. is no longer important and can be ignored – or even worse, that some item is now important but wasn’t included in your original scripts!
This is an important note. Things change over time, so our administrative scripts need to change with them.
Yeah, it’s a marketing event, but there might be something interesting and exciting to come out of it.