Scale-out SSRS (a brain dump)

So, I just got finished with a SQL Server Reporting Services (SSRS) scale-out deployment, based largely on this step-by-step guide. Things mostly went well, but there were a couple of hiccups I’d like to record before they fly out of my head again.

Note, too, that I am NOT an SSRS guru. Feel free to ping me in the comments with additions and corrections as you see fit, but be kind, or I will beat you with my T-SQL Bat of Vengeance.

First, the scenario

So with SSRS scale-out, you can do a number of different scenarios. Here’s what mine looked like:

  • Data source databases (you know, regular databases) on an Availability Group on Server1 and Server2. This, mind you, doesn’t actually matter, except to note that all the remaining servers are dedicated to SSRS matters only…not data.
  • SSRS services on four servers – SSRS01 through SSRS04 – sitting behind a network load balancer. Note that these servers JUST had SSRS installed, not the actual SQL engine.
  • SSRS databases, in an Availability Group (AG), on two additional instances. Let’s call these servers SSRS_DB01 nd SSRS_DB02*. Note that these servers had the SQL engine installed as well as SSRS.

The point of this is to make sure there’s lots of firepower dedicated to actual report processing, and even more dedicated to the writing and retrieval of report-specific information.  AND, that the report databases themselves have (manual) failover ability, for high availability purposes.

I’m not going to go over the blow-by-blow of putting all of this together. The step-by-step guide does a pretty good job of that.

*Footnote 1:  The actual naming scheme was way worse than this, but what’re you gonna do when you’re not in charge of naming?

Limitations

Note that, while you CAN stick ReportServer and ReportServerTempDB into an AG, and while you could configure that for automatic failover, I DO BELIEVE that you’ll still have to do some manual work – pointing the SSRS services at the right replica – after the failover. So it might as well be manual.

“Reporting Services offers limited support for using Always On availability groups with report server databases. The report server databases can be configured in AG to be part of a replica; however Reporting Services will not automatically use a different replica for the report server databases when a failover occurs.” – Docs.Microsoft.com

Having said that, I have no idea why you can’t point the SSRS services at the AG listener, and have done with it. I’ll look into it, or some helpful soul will contribute in the comments.

The Hang-ups

Here are the bits that I either missed in the guide, or wasn’t made clear at all:

  • All of the SSRS services must run under the same service accounts.  That’s the SSRS services on the SSRS servers (SSRS01 through SSRS04), and the SSRS services on the SSRS database servers (SSRSS_DB01 and SSRSS_DB02).
  • You have to back up the encryption key on the SSRS database server primary – in this case, SSRSS_DB01 – and restore that encryption key to all of the SSRS services.
  • Did you get that? All of them need the same service account, and all of them need the same encryption key. It makes sense after the fact, but I’m not at all used to splitting out SSRS services from their underlying databases.

Absolute computer fundamentals: the command line!

So I’ve spoken to more than one DBA who is not familiar with using the command prompt (aka command line) – or as we old-schoolers still accidentally call it, “DOS”. It’s not really DOS any  more, of course, but it still uses the old commands and has the same basic look:

An empty command prompt window

From here, you can move around, change things, and explore. The command prompt is context dependent, meaning – just like in a file explorer window – you can see and affect things (mostly) in the folder (aka context) that you’re IN. In the image above, we’re on the drive root: C:\.

Why should it matter to use the command prompt? Well for one thing, certain things are just easier and/or faster via the command prompt, than it is when you use the Windows GUI.

For another thing, PowerShell accepts all the basic command prompt commands. I definitely think you need to learn PowerShell, so you may as well double up and learn your basic command prompt commands!

Launch your command prompt window: Of course you can find “command prompt” in the Windows menu. But you can also use Windows-R to get the Run dialogue, and type cmd. That’s how Jen does it, kids!

Basic Commands

You’re in the command prompt. Just like being in a File Explorer window, the command prompt has a context. You’ll likely start out in your own user folder. For example, C:\Users\jmccown. Let’s move around a little, shall we?

  • cd .. – Go up one directory level. (“..” always means “up one directory level. So if we wanted we could type ..\.. to go up TWO directory levels, and so on.)
  • cd \ – Go to the root directory. In this case, we’re in C:\, so wherever we are in the folder structure, “cd \” will take us back to the root of C:\.
  • cd <folder name> – Change directory to <folder name>. This is like double clicking on a visible folder in a file explorer window.
  • dir – Get a listing of what’s in this directory.
  • dir p* – Get a listing of everything in the current context (the folder we’re in) that starts with the letter P.
  • dir P* /A:D* – Get a listing of just the directories in the current context that starts with the letter P.  How did I know about that /A flag? See “Help”, below.
  • cd “c:\Program Files\Microsoft Office” – Move to the Microsoft Office folder, no matter where I’m starting from. See, I’ve given the command prompt a full path, so it’s not context dependent! Also note that I enclose the path in quotes, because the command prompt depends on spaces to tell one element from another. If you tried this without quotes, you’d get an error.
  • L: – change to the L drive. Note we do not cd (change directory), because L isn’t a directory!
  • Help <command> – Get help on the <command> named. This can be very, well, helpful!

Getting Help

Remember that /A attribute from above? We can find out what options we have, and what other flags are available for each command. Just use Help dir, or whatever command you’re interested in. For example:

command prompt with "help dir" results

You can also look up or study command prompt commands on TechNet and elsewhere.

More commands, and batch files

Well, we can rename (or ren) files and folders, move, copy, clear the screen (cls), direct output to a file (>, which creates or overwrites a file, and >>, which creates or appends to a file), and exit the command prompt.

You can also run batch files, which are just saved lists of commands. If I save the following list of commands as file.bat

cd \
cd temp
dir *.txt >> output.txt

…then I can run it from the command prompt, using file.bat, to get list of TXT files in c:\temp written to a new file called output.txt (or appended, if the file already exists).

This really is a bare bones introduction to the command prompt. But I really think you should familiarize yourself with these basics – play around with them. Just be careful not to rename, delete, or overwrite anything important!

RegEx in SSMS: A gift for quick code

I had yet another example of how beautiful Regular Expressions can be in SQL coding today, and so I thought I’d share.

What I HAD was a set of DELETE statements that might or might not have been executed recently:

DELETE FROM dbo.Table1 WHERE val = 1
DELETE FROM dbo.Table2 WHERE val = 1
DELETE FROM dbo.Table3 WHERE val = 1
DELETE FROM dbo.Table4 WHERE val = 1
DELETE FROM dbo.Table5 WHERE val = 1
DELETE FROM dbo.Table6 WHERE val = 1 and x = y;
DELETE FROM dbo.Table7 WHERE val = 1
DELETE FROM dbo.Table8 WHERE val = 1 and val2 in (select val from Table8)
DELETE FROM dbo.Table9 WHERE val = 1
DELETE FROM dbo.Table10 WHERE val = 1
DELETE FROM dbo.Table11 WHERE val = 1
DELETE FROM dbo.Table12 WHERE val = 1
DELETE FROM dbo.Table13 WHERE val = 1
DELETE FROM dbo.Table14 WHERE val = 1

What I needed was to be able to check each one of those tables, and see if any of the should-be-deleted rows still exist. Of course, I can certainly type this all out by hand…

IF EXISTS (SELECT * FROM dbo.Table1 WHERE val = 1) SELECT 'Table1 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table2 WHERE val = 1) SELECT 'Table2 has undeleted rows!' as TableName;

*Sigh*. I’m already  bored. And in reality, I had 45 of these statements, not 15. I’m not a big fan of repetitive, monkey-button work.

RegEx to the Rescue!

We can turn those statements into a formula, and do a replace-all! That’s what RegEx is great at. We can look at a string like that DELETE statement, and formulize it as pseudocode:

Start of line + 'DELETE FROM ' + table name + ' WHERE ' + where clause + end of line.

With just a little bit of know-how, turn this into a proper (SSMS flavor of) regular expression:

^DELETE FROM {.+} WHERE {.+}$

Here’s a quick key to these expressions:

  • ^ – start of line
  • {} – tagged expression
  • . – any character
  • * – one or more (in this case, one or more of “any character”)
  • $ – end of line

See? Direct translation! I’m strictly including start of line and end of line to be sure there’s no confusion whatever…I want this formula to apply to a single line of text.

Now, what I want to change each of these to is that IF EXISTS statement, which we can formulize like this:

'IF EXISTS (SELECT * FROM ' + table name + ' WHERE ' + where clause + ') SELECT ''' + table name + ' has undeleted rows!'' as TableName;'

Just a little more know-how, and we get this regular expression:

IF EXISTS (SELECT * FROM \1 WHERE \2) SELECT '\1 has undeleted rows!' as TableName;

Here’s a quick key to these expressions:

  • \1 – First tagged expression (in this case, we’d tagged the characters between FROM and WHERE, which means we tagged the table name)
  • \2 – Second tagged expression (the letters between WHERE and end of line, the where clause)

Note that you don’t have to put the beginning of line/end of line markers as part of the Replace With text.

Here’s what all this looks like in SSMS. Remember to select “Use Regular Expressions” in the find and replace dialogue!

SSMS Find and Replace window with regular expressions

And here are the results of the replace all, which require just a touch of manual editing (notice the semicolon in the middle of the Table6 statement). A touch of manual editing is WAY better than typing out 45 of these statements by hand!

IF EXISTS (SELECT * FROM dbo.Table1 WHERE val = 1) SELECT 'dbo.Table1 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table2 WHERE val = 1) SELECT 'dbo.Table2 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table3 WHERE val = 1) SELECT 'dbo.Table3 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table4 WHERE val = 1) SELECT 'dbo.Table4 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table5 WHERE val = 1) SELECT 'dbo.Table5 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table6 WHERE val = 1 and x = y;) SELECT 'dbo.Table6 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table7 WHERE val = 1) SELECT 'dbo.Table7 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table8 WHERE val = 1 and val2 in (select val from Table8)) SELECT 'dbo.Table8 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table9 WHERE val = 1) SELECT 'dbo.Table9 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table10 WHERE val = 1) SELECT 'dbo.Table10 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table11 WHERE val = 1) SELECT 'dbo.Table11 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table12 WHERE val = 1) SELECT 'dbo.Table12 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table13 WHERE val = 1) SELECT 'dbo.Table13 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table14 WHERE val = 1) SELECT 'dbo.Table14 has undeleted rows!' as TableName;

See there? If we know just a few symbols, and take it step-by-step, we can save ourselves a ton of time! And this is but one example of an infinite number of uses for RegEx in SSMS.

Watch our RegEx videos on MidnightDBA.com, especially the session given at CACTUSS 2017!

Real news, real tech.