Tag Archives: database

SQL Holmes: The Case of the Fist-fighting Log Readers

And the Case of the Fist-Fighting Log Readers

Every once in a blue moon, you run across a problem that no one has seen before. Sometimes you can’t find anything all. Sometimes, you can only find unanswered forum questions. Sometimes, you find the worst thing imaginable: a forum post with someone replying to their own question with “Nevermind. Fixed it.” (HOW DID YOU FIX IT YODARULES1971?!?)

We had a similar experience a while back. Allow me to take you through it.

It started with a single, innocuous alert from one of our SQL Server Replication distributors:

DESCRIPTION: Replication-Replication Transaction-Log Reader Subsystem: agent SOMESERVER-SomeDB-6 failed. The process could not execute 'sp_replcmds' on 'SOMESERVER'.

Note: If you don’t have SQL Server Alerts set up on your instances, then you really really should.

We had two databases in this instance that were replicated. One was chugging along just fine, the other was giving the old log reader chestnut:

The process could not execute 'sp_replcmds' on 'SOMESERVER'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help: http://help/MSSQL_REPL20011

Experience has taught me that the most likely issues are the following:
1. Somebody changed something (Troubleshooting 101)
2. The owners of the databases involved in replication are wrong (usually not ‘sa’)
3. A database trigger somewhere was trying to make a change cross-database.

“I saw some smoke coming from the barn publication.”  -SQL Replication Monitor

Number 1 had happened, as we had deployed schema changes just prior to everything going south. Two and three however, did not. We could see the error in the replication log. It was trying to parse a Primary Key change that we had made. There was nothing obviously wrong with the command, so we moved on.

And Then We Tried to Fix It

After a few hours trying to troubleshoot, restarting the log readers, crying for our mommies, yadda yadda, the call was made that we would just reinitialize the publication. Ye ole’ replication wreck-n-restart.

It didn’t work. The publication would not reinitialize, failing with the same log reader error as before. This make sense as the log reader is shared among all the publications on an instance. It just couldn’t get over the command that couldn’t be parsed.

With few options left, we did the no-no and reset the log reader.

EXEC sp_replrestart

This is an internal command, run on the publisher, that is used when you need to restore a transactionally replicated database. It basically resets the LSN on the distributor to the highest value on the publisher, for our purposes skipping over the LSN/command that was causing the log reader’s issues.

This is going to be my costume for Halloween next year.

And it worked. Mostly. Replication started to flow again, but we needed to re-initialize the subscribers properly. Re-init was necessary as we’d just skipped all transactions that had accumulated between the time the issue occurred and the current time.

Yes, But What Does It MEAN?!?!

So, we had “resolved” the issue, but we still had no idea what the “issue” really was. After we had righted the ship, applied all dropped indexes at the subscriber we circled back around to determine root cause and why no one else seemed to have any idea what would cause this.

We went to our Junior DBA (Google) and got a pretty narrow range of responses, including the ones I listed before. No one seemed to be having the same issue as us, even with similar errors. So, we had to start at the bottom and work to the top.

Combing through the log reader error logs, one error was different than the others:

Cannot find an object ID for the replication system table 'cdc.change_tables'. Verify that the system table exists and is accessible by querying it directly. If it does exist, stop and restart the Log Reader Agent; if it does not exist, drop and reconfigure replication. (Source: MSSQLServer, Error number: 18807)

Well, that’s different. CDC is enabled on this database and used on quite a few tables. We checked, and the table cdc.change_tables existed; we wouldn’t get very far in life without it. The next step was to look at the CDC error log which returns the last 64 errors encountered.

SELECT * FROM sys.dm_cdc_errors

And in the sys.dm_cdc_errors table, we found this error:

Log scan process failed in processing a ddl log record. Refer to previous errors in the current session to identify the cause and correct any associated problems.

That seems pretty familiar. It was preceded by the following three errors:

Invalid length parameter passed to the RIGHT function.
Log Scan process failed in processing log records. Refer to previous errors in the current session to identify the cause and correct any associated problems.
Log scan process failed in processing a ddl log record. Refer to previous errors in the current session to identify the cause and correct any associated problems.

Different CDC, but I could see why you would be confused.

This points pretty squarely to CDC as the culprit. It uses a log reader with replication to replicate commands for capturing in CDC. Apparently, if it gets hung up, replication panics and just starts punching itself in the face. It’s an interesting design choice.

Way #938,308,121 To Break Replication

A few weeks later, when deploying more schema changes the issue resurfaced. Replication dive-bombed, we got the sames alert and everything came to a halt. We simply went in and blew away CDC for the entire database. This may not be a choice for your environment, but we needed to nuke it from orbit, just be sure.

USE SomeDB
GO
EXEC sys.sp_cdc_disable_db
GO

And after executing sys.sp_cdc_disable_db, the issue went away. Success?

We haven’t re-implemented CDC on this database yet, as there are a lot more decisions to be made business-wise, so we don’t know if there’s something internally broken or what. At some point we will need to turn it back on, but when and to what degree (or fallout) isn’t clear.

What is clear is is that something in CDC goofed and it took replication down with it. Moral of the story is: If replication is having issues, make sure CDC isn’t having issues as well. Also, use CDC sparingly. Don’t just throw it on every table you have “just ’cause.”

-CJ Julius

Top 5 New Features in SQL Server 2017 (that I care about)

New Year, New Database Engine

http://icons8.com/
This is either a database or a stack of licorice pancakes.

It’s finally here! A few days ago as of this writing, SQL Server 2017 was released for Windows, Ubuntu, RedHat and Docker. There are a lot of new things in SQL Server 2017 from Python support in Machine Learning* to better CLR security. But I thought I’d narrow down the list to changes that I’m most interested in.

1. sudo Your Way To A Better SQL Server

SQL SERVER IS ON LINUX NOW! No surprise this is the first thing on my list as I keep going on and on about it. But it’s really here!

Installation is a cinch, especially if you’re a Linux or Unix person. Curl the GPG keys, add the repository, apt-get (if you’re on a real Distro) the installer and run the setup. It’s really that easy.

If you want it even easier, then check out Docker. Slap it in and go!

All the main features are there, the engine (of course), agent, Full-text search, DB Mail, AD authentication, SQL command-line tools etc. Pretty much everything you need to get going on Linux even if you’re in a Windows-dominated environment.

2. It’s Like A Car That Repairs Itself

So this is a big one. With automatic tuning on, SQL Server can detect performance problems, recommend solutions and automatically fix (some) problems. There are two flavors of this, one in SQL Server 2017 and one in Azure; I’ll be talking about the one in SQL 2017 here.

Automatic plan choice correction is the main feature and it checks whether a plan has regressed in performance. If the feature is so enabled, it reverts to the old plan. As you can see below, a new plan was chosen (4) but it didn’t do so well. SQL 2017 reverted to the old plan (3) automatically and got most of the performance back.

Image Source: Microsoft

I’m sure that Microsoft will be expanding this feature in the future and we can expect to see more from this. Azure already has automatic Index tuning in place, so we’ll probably see that in the On-Prem version eventually.

3. Indexes That Start, Stop And Then Start Again

This is a feature I didn’t know I needed. Basically, it allows an online index rebuild that has stopped or failed (say, it ran out of disk space) to be resumed. The index build fails, you fix whatever made it fail, and then resume the rebuild, picking up from where it left off. The rebuild will be in the ‘PAUSED’ state until you’re ready to RESUME or ABORT it.

Code:
-- Start a Resumable index rebuild
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable]
REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=60)

-- PAUSE the rebuild:
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable] PAUSE

/* If you'd like to resume either after a failure or because we paused it. This syntax will also cause the resume to wait 5 minutes and then kill all blockers if there are any. */
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable]
RESUME WITH (MAX_DURATION= 60 MINUTES,
WAIT_AT_LOW_PRIORITY (MAX_DURATION=5, ABORT_AFTER_WAIT=BLOCKERS))

/*Or if you just want to stop the whole thing because you hate unfragmented indexes */
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable] ABORT

This is also great if you want to pause a rebuild because it’s interfering with some process. You can PAUSE the rebuild, wait for the transaction(s) to be done and RESUME. Pretty neat.

4. Gettin’ TRIM
A cute Sheepy.
There hasn’t been a picture in a while and I was afraid you might be getting bored.

This one is kind of minor, but it excited me a lot because it was one of the first things I noticed as a DBA that made me say ‘Why don’t they have a function that does that?’ (note the single quotes). TRIM will trim a string down based on the parameters you provide.

If you provide no parameters it will just cut off all the spaces from both sides. It’s equivalent to writing RTRIM(LTRIM(‘ SomeString ‘))

Code:
SELECT TRIM( '.! ' FROM ' SomeString !!!') AS TrimmedString;

Output:
SomeString

5. Selecting Into The Right Groups

Another small important change. In previous versions of SQL Server, you could not SELECT INTO a specific Filegroup when creating a new table. Now you can, and it uses the familiar ON syntax to do it.

Code:
SELECT [SomeColumn] INTO [dbo].[SomeNewTable] ON [FileGroup] from [dbo].[SomeOriginalTable];

Also, you can now SELECT INTO to import data from Polybase. You know, if you’re into that sort of thing.

Honorable Mentions!

Here are some things that caught my eye, but didn’t really need a whole section explaining them. Still good stuff, though.

Query Store Can Now Wait Like Everybody Else

Query store was a great feature introduced in SQL Server 2016. Now they’ve added the ability to capture wait stats as well. This is going to be useful when trying to correlate badly performing queries and plans with SQL Server’s various waits.

See the sys.query_store_wait_stats system table in your preferred database for the deets. Obviously, you’ll need to turn on Query Store first.

Con The Cat With Strings

Just as minor as TRIM in some people’s books, but this is a great function for CONCAT_WS’ing (it’s not Concatenating, right? That’s a different function) strings with a common separator, ignoring NULLs.

Code:
SELECT CONCAT_WS('-','2017', '09', NULL, '22') AS SomeDate;

Output:
2017-09-22

Get To Know Your Host

sys.dm_os_host_info – This system table returns data for both Windows and Linux. Nothing else, just thought that was neat.

Get Your Model Serviced

Not something I’m jumping for joy about, but it is a good change of pace (I think). No more Service Packs, only Zuul… er… just Cumulative Updates. Check out my article on it if you want to know more about all the Service Model changes.

And Lots Of Other Things

Of course, there’s hundreds other things that are in SQL Server 2017 and related features. I didn’t even touch on the SSIS, SSAS, SSRS, MDS or ML stuff. Check out the shortened list here, broken down by category. Exciting new toys!

-CJ Julius

* Seriously, how do you release a Data Science platform and not include Python? That’s like releasing a motorcycle with only the rear tire. Yes, you can technically use it, but you you’re limiting yourself to a customer base with a very selective skill-set.

Say Goodbye to Service Packs: SQL Server 2017 Won’t Have Them

Take Your Service Pack and Get Outta Town

Service Packs (SPs) have long been an quick litmus test to determining where you are when assessing needed upgrades. You could almost ignore CUs (Cumulative Updates) and use the SP to define where you are and you needed to go. 2008R2 SP3? You’re pretty much all patched up. 2012 SP1? Got a ways to go. But that’s about to change with SQL Server 2017 as Microsoft is doing away with Service Packs, and just releasing sequential updates as CUs.

In SQL2017 and beyond, every CU will be tested like a Service Pack and contain all the updates, hotfixes and security patches of every CU before it. So we can expect to see versions like SQL Server 2017 CU12.

Well, That’s Nice. But Why?

Microsoft wants to move to a more “agile” method, allowing them to get more updates out faster. Releasing many smaller CUs is faster-to-market and means less patching of odds and ends with hotfixes.

Also, this will just simplify the whole process. Instead of saying 2017 SP3 CU2, it will simply be 2017 CU26 (I just made these up. I am not clairvoyant now, but I will be in the future).

There will be two tracks for updates, the main CU path and a GDR (General Distribution Release) path. GDR path is just security updates (maybe a system-breaking hotfix once in a while). This path will be entirely separate from the normal CU path and you will not be able to jump back and forth between them*.

When Will I Get These CUs?

2012/2014/2016 are all still on the old model. Starting with SQL Server 2017 you’ll see this new servicing model.  After RTM, SQL2017 will get a new CU every month for the first year, but will slow down after that. Microsoft’s reasoning is that most of the major fixes are in the first year, so they want to keep ’em coming during this critical phase. For the remainder of the four years of mainstream support, this pace will slow to one CU every quarter.

If you’re on Linux, it’s the same deal. You’ll be able to pull these CUs from the same repositories that you get SQL Server from. This is kind of a big ‘duh’ but I felt it need mentioning.

Anything Else?

Sure there is! Lots of odds and ends for you to know. Like:

  • CUs will accommodate localized content (they didn’t before)
  • CUs will still be released the same time every month
    • That’s the week of the 3rd Tuesday, but you knew that
  • You don’t have to be on a specific CU to be supported.
  • CUs will not contain any “net new” features.
  • CUs can be uninstalled from Windows
  • In Linux, install and run the container from a previous CU to do a rollback

And that’s it. Happy patching!

-CJ Julius

*You can go from the GDR path to the CU path, but not back again. Once you’re on the CU path, you’re there for good.

Simple Database Inventory Manager 2.3

We got a job.
We got a job.

Always moving forward, here at KnowledgeHunter Corp! (Note: not a real corporation) Just posted the github of the new SDIM 2.3. Changes in just about every corner, but the big overall change is the addition of job information in the data pull.

If you have no idea what SDIMS is or I’m yammering about, here’s the run-down. Otherwise, read on for the 2.3 update news.

Dey took er jerbs
New buttons in 2.3 include 'Jobs' and 'Jobs Extended'
New buttons in 2.3 include ‘Jobs’ and ‘Jobs Extended’

The newest feature is ability to inventory pretty thorough job information. Well, the stuff I’m always looking for anyway. Also included is some extended information, which right now basically includes just a description with a few other identifying columns. Future versions may gather more data and put it here. Powershell handles it well, even with several thousand rows/jobs.

Here is the kind of information that you can expect to see:

  • Server/Instance
  • Job Name
  • Is the Job enabled?
  • Schedule Name
  • Is the Schedule enabled?
  • Schedule Type, Occurrence, Recurrence (if applicable) and Frequency
  • Job Description

This information has been added to the ‘Full Inventory’, so be aware that has a lot of new stuff in it too.

Full disclosure: I used heavily modified version of a few queries from Dattatrey Sindol to pull jobs data.

Always Squashing

Fixed a few bugs:

isProduction column now does not display in Instances list. That was a feature that got removed from everywhere (before 1.0). Finally removed the column.

HIPAA level feature is now completely removed. Never got this one working right and I decided that in the future I’ll go with something a bit more general, like maybe just ‘priority’ or something.

Fixed a few typos. Me spel gud now.

Known Issues:

Too many varchar(max) columns. I know. I know.

Me Want

So, how do you get this? Well…

If you never have used it before, I would suggest going through the walkthrough I put out a while ago. I may in the future build a more simplified version for people who just want to install and don’t care about the behind-the-scenes. (UPDATE: I did just that, here)

Elseif you’re using prior to 2.1 already, then you should probably just drop all the SQL objects and rebuild with the new 2.3 items. This includes the PowerShell datapull and frontend pieces; they’re the only two PowerShell pieces.

Elseif you’re on 2.1 right now you can just replace the items that were changed. I have created a script that will do this for you, because that’s the kind of guy I am. You may need to run the script twice because I didn’t check for object existence. Running it multiple times won’t hurt. It doesn’t touch the Servers table, but it does drop the InstanceList, so if you have that statically assigned, then you’re going to want to back it up and then re-insert the data with the missing columns removed.

And Here’s A Count of All the Bolts

Lastly, here’s the items that were altered in the 2.1 – 2.3 release if you’re curious:

Tables:
dbo.JobList – This holds all the jobs information. Added for new feature (look! No varchar(max)!)
dbo.InstanceList – Removed a column.

Stored Procedures:
dbo.prGetInstances – Altered to fix bug
dbo.prGetInventory – Altered to fix bug
dbo.prGetJobs – New for new feature
dbo.prGetJobsExt – New for new feature
dbo.prGetServersAndInstances – Altered to fix bug
dbo.prInsertJobList – New for new feature
dbo.prUpdateInstanceList – Altered to fix bug

PowerShell:

DB_DataPull – Altered to pull Jobs (new feature) and fix bugs
DB_DataPUll_FrontEnd – Altered to display Jobs/Jobs Extended

Simple Database Inventory Manager 2.1

sdim_ver_2-1_trunc_275x400

A while back, I took on a fairly big project; build an database inventory manager that did the following:

  • Dynamically gathered information on SQL Instances/Databases
  • Dynamically gathered information on the OS underneath
  • Compiled and organized this data in a single repository
  • Provided a client GUI front-end for ease of use.
  • Was built on free and already available tools.

When I initially finished Inventory Manger 1.0 I wrote a 5-part series that took the user through the steps of how I built what I did and how everything worked. This was a good way for me to iron out details and also provide some documentation along the way. As the months have progressed I added updates to the code and altered the posts as necessary.

Then everything went silent as I moved on to other things, but I was constantly going back and adding new features and options. During this interim, I was not able to get the code updated on GitHub, and thus it fell behind. Also, it had morphed into its own beast, moving out of “project” and more into a standard “software” mode. To signify its new direction, its name is now Simple Database Inventory Manager™.

As such, I have added the new 2.1 version to its own GitHub project (previously it existed in the SQL code section there with other unrelated snippets) and will update it accordingly. You can just download the new files and overwrite the old ones to get the new version. Spiffy, right?

db_datapull_frontend_v2-1
Look! 200% more buttons!™

I will keep the old 1.x version in the original repository so it doesn’t break links for the walk-through articles.

All that said, what fabulous prizes are in the new version? Glad you asked!

CMS Integration

Now you can point to your Central Management Server and the Simple Database Inventory Manager™ will pull a list of servers and instances from there. It will then go through all of them recursively, and pull whatever data you want back.

In DB_DataPull.ps1, you can switch this on or off with the -UseCMS switch. If on, then you will need to specify the CMS Server with the -CMSServer ‘SOMESERVER’ parameter.

If you use the -UseCMS option, this will delete all data from the repository tables and repopulate them based on what you have in the CMS. This is backwardly compatible with the old system in that if you don’t use the option (off by default) then it will continue to use the Server and Instance List you provided manually.

Fresh, New Buttons

‘Services’ has been folded into the Inventory heading and Other Info is no more, replaced by the Reporting section (below).

Composite buttons have been created to give better side-by-side information from the GUI. Server\Instance and Instance\DB buttons have been added to do this. I think their names are pretty self-explanatory. Click on them. See what happens.

Reporting

Kind of. A bunch of stock reports were added to the DB_DataPull_FrontEnd.ps1 under the Reporting heading. These rely on Views in the Reporting Schema that build customizable information you want returned.

I’m going to leave this schema (Reporting) pretty much alone for now, so users can create their own views and then use those as datasets if you want for SSRS. You can use SDIM™ as the basis for reports that refresh as often as you want to run DB_DataPull.ps1

Here’s a quick list of the out-of-the-box reports given in the GUI:

Servers Grouped by OS and Service Pack – A count of all Server OS names and versions.

Instances Grouped By SQL Version – A count of all Instances at a specific server version (ignores Service Pack)

Instances Grouped by SQL Version, Edition – A count of all instances by SQL Version and Edition (ignores Service Pack)

Instances Grouped By SQL Version, Edition and SP – A count of Instances based on SQL Server Version, Edition and Service Pack

Bug Fixes/Minor Enhancements

  • Fixed an incorrectly spelled column.
  • Fixed incorrect calculation of Server number.
  • Added extended properties to all tables.
  • Added header at the top of all Views and Procedures

And All the Rest

This (Simple Database Inventory Manager™) is of course provided free of charge, use-at-your-own-risk. There is no warranty either expressed or implied. If SDIM™ burns down your data center, uninstalls all your favorite toolbars and ruins your best pair of dress socks, I’m not at fault. Remember to back up your databases!

And if you’ve skipped over everything just to get to the link, here it is: SDIMS v2.1

-CJ Julius

(Almost) Everything is Going Open Source Now… and I LOVE it.

Why can't we be friends?
Why can’t we be friends?

While I’m putting together my big update on Inventory Manager, I thought I’d take some time to throw confetti into the air. There may be some excited clapping as well. I warned you.

I largely see myself as platform-agnostic. While I think that certain companies do individual products well, I also believe it’s fair to say that none of them do everything well. I use Android phones and Apple tablets, Linux for home (mostly) and Windows at work. Heck, I’ve got a Roku and a Chromecast because they both do things that the other doesn’t.  I’m all over the map, but all over the map is a great place to be, especially in the tech industry now.

Despite all of this, I have to admit I am partial to Free Open-Source Software (FOSS). Give me a choice between Ubuntu and Windows, and all other things being equal, I’ll choose the Debian-based option. I’ll admit my biases.

So, when MS started moving in this direction I was happy. I wanted to see this trend continue, and boy has it. First of all…

1. .NET Core is now running on Redhat.

When Microsoft announced that .Net was going open-source, I was cautiously optimistic. I’m not a big .Net coder, but I could see the benefit and was hopeful that MS would continue down this path.  This lead to some cool things that I thought I’d never see in a million years, like .Net running on Redhat.

There’s understandably some cynicism about Microsoft’s true intentions, as well as their long term goals, but this is the cross-over that I’ve been wanting to happen for a while. Blending the strengths of RHEL with .NET on top is a great start. If the .NET development platform can be ported, why not parts of the Windows Management Framework? We could even one day see…

2. Powershell on OSX and Linux.

I didn’t always like Powershell, in fact prior to Powershell 3, I just referred to it as PowerHell. Since 4.0, however, it’s no secret that I’m a fan; one look at my github will tell you that. I like its logical approach to (most) things and that it works for simple scripts quite easily, while being a powerhouse (no pun intended) behind the scenes.

Sorry, THIS is the coolest thing ever.
This is the coolest thing ever.

This shell coming to OSX and Linux will be a boon for both systems. While I am, and will probably always be, a bash scripting guy, Powershell in Windows just makes everything so gosh-darn easy. If I could whip up a PS1 script with a few imported modules and attach it to a cron job with ease, then I think everybody wins,  mostly me. But, if I decide that I want to use bash instead, that’s okay because…

3. Bash is running on Windows.

This isn’t a one way transition. Microsoft is making a trade, bringing one of the most widely used shells to Windows. This not only makes scripts more portable, but also knowledge.

Have some ultra-fast Linux bash script that works wonders? Super, you now have it Windows, too. Wrote a script to do some directory work in Powershell? Great, you now know how to do it in Linux.

You can't tell me that isn't the coolest thing ever.
I’m sorry, THIS is the coolest thing ever.

There are very few downsides to this, other than the obvious security issues and that it isn’t truly a stand-alone shell (it’s part of Ubuntu on Windows). In any case, it allows interoperability  between software from different systems. This is great now that…

4. SQL Server is on Linux.

This isn’t technically going open source, as it will run inside a container, but the idea that this will now be possible and supported is like something out of my greatest dreams.

I have a maybe-controversial opinion that SQL Server is the best relational database system out there. For all its faults, I’d rather use SQL Server 2005 SP1 than Oracle 12c. Just the way I feel, and for reasons I won’t go into here. I hope the things I like about SQL Server translate to the Linux environment.

The fact that Ubuntu is supporting this with Microsoft is great. I can’t wait to use my favorite OS with my favorite database engine on the same system.

Last thoughts

There are other items I’ve glossed over, but these are the big ones to me. Soon, we will be able to run SQL Server on Ubuntu Linux with cron jobs executing Powershell for a .Net application that resides on an RHEL box. *excited clapping* (I warned you.)

It’s a great time to be in the tech industry.

-CJ Julius

 

A Funny Thing Happened on my way to enable Management Data Warehouse

Three Job Schedules walk into a bar…
uh-oh
uh-oh.

Management Data Warehouse is a neat tool for collecting data on your instances with regards to performance, disk usage, and anything else related to SQL Server you might want to know. I’ve recently been rolling it out to various servers in test environments to get a good handle on all that it can do, and maybe more importantly, how it’s going to affect performance.

When I was satisfied that it was working the way I wanted in these environments, I started to push it to what you could call QA. Immediately I was hit with litany of errors, all of which said basically the same thing:

Caught error#: 14684, Level: 16, State: 1, in Procedure: sp_syscollector_create_collection_set, Line: 203, with Message: Caught error#: 14262, Level: 16, State: 1, in Procedure: sp_syscollector_create_collection_set, Line: 80, with Message: The specified @schedule_name ('CollectorSchedule_Every_30min') does not exist. (Microsoft SQL Server, Error: 14684)

Um, what? Of course it doesn’t exist, I’m installing it now. Just to verify, I did some poking around, and I found that it was indeed attempting to add a schedule that didn’t exist (as you would expect) to the collection set. After a while my Jr DBA (read: Google) turned up this article on removing MDW which says, in no uncertain terms:

The fact is that, while these schedules may look like they were created by MDW, they are created by default in a default installation of SQL Server 2008 or above. You can delete these easily, if you really want to, and if you are sure you will never enable MDW again. I would still suggest scripting the schedules and saving those scripts to your file system or source control so that you can recover them in the future.

I took a look back on the TEST instances where I was previously successful and found these schedules existed both on instances where MDW had been installed, and in places where it had not. These were part of the defaut SQL Server install and had been manually removed. Whoops.

As far as I know, there isn’t any direct way to script out schedules, so I could port them to the boxes where they had been removed. To get around this, I added them to the collector set jobs and then scripted that. Then I was able to change a few variables (and remove the schedule_uid) so that this:

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'CollectorSchedule_Every_15min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'SOME-GUID-HERE'

became this:

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_15min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

I ran these add_schedules on the QA boxes, went through the Configure Management Data Warehouse Wizard again, and tada!

Green means good.
Green is good. Green is life.

For your convenience, I’ve scripted out the four schedules I needed to get it going below. These are not from the most current version of SQL Server (2016 as of this writing), and are not all of the schedules included out of the box, but these let me get MDW running so I could add my own custom schedules for the remainder.

The moral of this story: Don’t remove MDW schedules unless you really mean it, for all time, forever and ever, pinky swear.

The four ‘core’ schedules I needed, for your convenience:

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_6h',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=6,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_30min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_15min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'RunAsSQLAgentServiceStartSchedule',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

If one of these schedules gets created twice you can look up the job id or use SSMS to ‘Pick’ the schedule for the duplicated schedule (the id is in the first column). And run this to remove it:

--EXEC msdb.dbo.sp_delete_schedule @schedule_id = 9999

-CJ Julius

Creating a Simple Database Inventory Manager with Powershell – Part IV: GUI Front-End

Last Time, on Inventory Manager…
Now we make things pretty.
Let’s make things pretty.

Our data pull script has run, the database contains all our server  \ instance .database information and flowers are blooming; things are good. If this doesn’t sound like anything you have done, head back to the Introduction to see if you missed something.

Now it’s time to get everything connected so we can just fire up a GUI and press some buttons, to get the data we need fast.

How the Sausage is Made

If you’ve been following along with this series, and you’ve set up everything as instructed, then you should be able to download the pre-made GUI script and run it out of the box. If you’re pointing to a custom instance or database just change the $RepositoryInstance and/or $RepositoryDB before firing it up. If you want to learn more about how this was put together, keep reading. If you don’t care how the whazits work, you’re done.

At the top of our list is to create a form with buttons and give them names so we can call them in our Powershell. You can either build the form manually with this guide here, or use Visual Studio*. I’m going to be using the latter method because it’s the most versatile, and frankly the easiest. If you use the former, then you’re kind of on your own. Sorry.

In the  Visual Studio method (I’ll be using 2013 Ultimate) you’ll be utilizing Windows Forms and then running them through a “cleaner” to make them Powershell ready. This guide at FoxDeploy explains the whole thing spectacularly and shows you how to create some very complicated UI’s that are Powershell-friendly. I’d recommend going through Parts I and II as they’ll be the things you’ll need to create what we’re going to use here and then come back. Don’t worry, I’ll wait.

Then We Build

Got the GUI code? Cool. The first part of Stephen’s code uses a -replace to filter the Windows Form code and make it work in Powershell. I took that piece and made it a second script so I could just have the clean version of the XAML in my final code. You can find that code here.

Just copy/paste your <Window>…</Window> code over the commented area and run the script. It will spit out the final code and tell you all of the objects you can tie actions to (Name, Value). Then drop in Stephen’s XAML reader code to the main script with the cleaned code and you should have a GUI… that does nothing.

Whee.
Whee.

As I mentioned before, when you pushed the XAML code through WPF_to_PSForm.ps1 it will tell you what the objects are on your form. For our purposes, this is simply a few buttons that need to be tied to stored procedures. We do this though .Add_Click() as in the example below:

$WPFBt_All_Data.Add_Click(
{
$sqlCommand = "
EXEC dbo.prGetInventory;
"
$dataset = Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand $sqlCommand
Write-Host $dataset
$dataset | Out-GridView -Title "Database Inventory"
}
)

Nothing crazy that we haven’t been doing other than using Out-GridView. This cool little cmdlet pushes datasets out to a customizable table with filtering, sorting the ability to remove columns etc. -Title “SomeTitle” is the window title.

Sample sorted Database List with a few columns removed.
Sample sorted Database List with a few columns removed.

Once you’ve coded all of the buttons, then add the form display at the bottom, using out-null to suppress messages:

$Form.ShowDialog() | out-null

And that’s done. A Winner is You!

What now?

Using these scripts, you can go out and grab any information from the servers\instances you specify, pull it back into a centralized location and then use a GUI front-end to make fine tuning and retrieval easy. As I stated previously, this is a bare-bones system to centralize your database information. You can gather any piece of information from the Server, Instance or Database level by using the same tools that are currently collecting and retrieving this information.

It’s been a long journey, but thanks for sticking with it! If you want to make any alterations to the code or tighten it up (Odin knows that it needs it), feel free to make the changes and shoot them back to me. I’ll definitely give you credit for significant changes in this blog or the code itself.

Also, and I think this goes without saying, but if you want to use this in your personal or business environment: have at it! Just please make sure you give me proper credit, with maybe a link back to my blog/Twitter/Linkedin? That’d be super cool of you.

Thanks again and happy Inventorying!

–CJ Julius

*Full disclosure: I have not tried this with the Community version of Visual Studio, so all the features may not be there.

Creating a Simple Database Inventory Manager with Powershell – Part III: Data Pull

Powershell time; no really.
DB_inven_Mgmt_PS1_sm_III
I come bearing scripts.

Now it’s time to get to get this thing moving. We’re going to go out to each of our server\instances and pull back the information for our tables, updating them with the stored procedures from the last section.

We’re going to be looking at this script [DB-DataPull.ps1]. It’s about as simple as I could get it for our needs. There’s not a lot of frills, but it’s a good cop and it. gets. results.

If you think you missed something you can go back to Part II: Stored Procedures or check out the Introduction.

Get this Jalopy on the Road

The only thing you need to do is specify where the repository is. If the repository is on your local machine in the DBAdmin database then you need to change nothing.

$RepositoryInstance = '(local)'
$RepositoryDB = 'DBAdmin'

After that you’re done. Seriously. The rest of this post is going to be about the nuts and bolts of the script and what does what and why. If you’re looking to just get it fired up then you’re done. Be gone with you.

What’s in the box?

The first few functions (Get-Type and Out-DataTable) are required to turn multi-line WMI-Object output into DataTables so we can insert them into the Repository. These have been cleaned up and/or modified to fit our needs but are based on the code in the two links I provided.

The Invoke-SQL function is a pared-down version of a pretty popular script for sending dynamic SQL directly to a SQL server. There’s not much to be said about this one other than it opens a connection, sends the command and returns the results as a datatable.

Time to get into the meat of the process. First up, let’s grab all the Instance information using the stored procedure we built in the last post.

$ConnectionString = Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand "
EXEC dbo.prGetConnectionInformation;
"

Using a foreach loop to cycle through the rows and thus connecting to each instance to pull the information. We’ll remove the ‘\\MSSQLSERVER’ part since that will actually break our connection, even though it’s the name of the instance (For more information on why this is, see every other Microsoft product ever created).

foreach ($Row in $ConnectionString.Rows)
{
Try
{
$SubConnection = $($Row[0]) -replace '\\MSSQLSERVER',''
$InstanceID = $($Row[2])
Write-Debug $InstanceID
Write-Debug $SubConnection
$Version = Invoke-SQL -datasource $SubConnection -database master -sqlCommand "
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition'), @@VERSION
"
}
...

And then with another loop we use dbo.UpdateInstanceList to push all that into our database.

Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand "
EXEC dbo.prUpdateInstanceList
@MSSQLVersionLong = '$MSSQLVersionLong'
,@MSSQLVersion = '$MSSQLVersion'
,@MSSQLEdition = '$MSSQLEdition'
,@MSSQLServicePack = '$MSSQLServicePack'
,@InstanceId = $InstanceID
"

That’s it for the Instance information, let’s get the database information. We use the same process to generate the connections as we did before, so I’m going to skip that. The only change you should note is the inclusion of the statement TRUNCATE TABLE dbo.DatabaseList since we are going to completely repopulate it. This way no matter if databases are added or removed, we’re starting each pull with a clean slate.

We get our data via a cte…

$DataPull = Invoke-SQL -datasource $SubConnection -database master -sqlCommand "
with fs
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
$InstanceID AS 'InstanceId',
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) AS DataFileSizeMB
from sys.databases db
ORDER BY DataFileSizeMB
"

…and push it into the Repository via our stored procedure.

Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand "
EXEC dbo.prInsertDatabaseList
@DatabaseName = '$DatabaseName'
,@InstanceListId = '$InstanceListId'
,@Size = $Size"

Lastly, we’ll get Service and Server information with the same rinse-and-repeat method, with one notable exception. If you try to return the results of a WMI-Object and parse it into a SQL table, then you’re going to have a bad time.

This is where our two functions from the beginning come in to play. Out-DataTable and its sidekick Get-Type return the results into the proper type for our foreach loop.

$ServerInfo = Get-WmiObject win32_Service -Computer $Row[0] |
where {$_.DisplayName -match "SQL Server"} |
select SystemName, DisplayName, Name, State, StartMode, StartName | Out-DataTable

Now, if you run EXEC dbo.prGetInventory on your Repository database, you should see all of the information you could ever want right there. Magic.

But Wait, There’s More!

Now we’ve got all the data in one place, which is nice and all, but what if we want to get this information quickly? Sure we can jump into SSMS and run the procedures that have the data we want. However, I propose we make a GUI front-end so we can win friends and get free drinks.

DB_DataPull_FrontEnd_GUI_2
Something like this?

We’ll do that in Part IV: The Voyage Home GUI Front-End.

–CJ Julius

Creating a Simple Database Inventory Manager with Powershell – Part II: Stored Procedures

Dynamic SQL? Them's fightin' words.
Dynamic SQL? Them’s fightin’ words.

Now that we’ve got the tables built and populated them with data (you did that right?) we can define how our Powershell script is going to access that data. If you don’t know what database or tables I’m talking about, check out the Introduction and Part I.

You can send the dynamic SQL to your database via Powershell but you really want all interactions to go through stored procedures. Why? Lots of reasons, but here’s 2:

  1. Standardization – Every time you call a stored procedure the code will be exactly the same every time. If you change a hard-coded query in one part of your script, but not another, you can get inconsistent results.
  2. Performance – Unless you’re accessing thousands of instances with this script then you may not care too much about performance, but SQL Server has a hard time building query plans off of dynamic SQL; it does so wonderfully when the code is in a compiled stored procedure.

So, that’s all well and good, but what do we need for our Powershell project to do? I’ve created the procedures that will allow you to do the basic tasks that you might be interested in. The sky is the limit if you want to do more.

The Procedure List
prGetConnectionInformation

[prGetConnectionInformation] – Setup script for 2012

Parameters:

  • Not a gosh-darn thing.

Returns:

  • Server\Instance string as ‘Connection’
  • ServerList.Id as ‘Server ID’
  • InstanceList.Id as ‘Instance ID’

Spits out a connection-string friendly version of all the servers and instances in the database.

prGetDatabasesAndSize

[prGetDatabasesAndSize] – Setup script for 2012

Parameters:

  • Not a gosh-darn thing.

Returns:

  • InstanceList.InstanceName
  • DatabaseList.DatabaseName
  • Size of the Database in Gigabytes as ‘SizeinGB’

Returns the database and the instance it belongs to along with the sizes of said DBs in Gigabytes for all databases in the database (Yo dawg, heard you like databases).

prGetInstances

[prGetInstances] – Setup script for 2012

Parameters:

  • Not a gosh-darn thing.

Returns:

  • InstanceList.InstanceName
  • InstanceList.MSSQLVersion
  • InstanceList.MSSQLEdition
  • InstanceList.MSSQLVersionLong

Basically returns everything in the dbo.InstanceList table that someone might be interested in.

prGetInventory

[prGetInventory] – Setup script for 2012

Parameters:

  • Not a gosh-darn thing.

Returns:

  • ServerList.ServerName
  • ServerList.IPAddress
  • ServerList.OSName
  • ServerList.OSServicePack
  • InstanceList.InstanceName
  • InstanceList.MSSQLVersion
  • InstanceList.MSSQLEdition
  • InstanceList.MSSQLVersionLong
  • Size of the Database in Gigabytes as ‘SizeinGB’

Leaves for a while and then comes back with everything in your Inventory. Does not bring back anything from dbo.ServiceList for visual reasons (we’ll see this later in the Powershell).

prGetServerNames

[prGetServerNames] – Setup script for 2012

Parameters:

  • Not a gosh-darn thing.

Returns:

  • ServerList.ServerName

Just brings back all the servers. Nothing else. Stop asking.

prGetServers

[prGetServers] – Setup script for 2012

Parameters:

  • Not a gosh-darn thing.

Returns:

  • ServerList.ServerName
  • ServerList.OSName
  • ServerList.OSServicePack

Returns all the information at the Server Level excluding IP Address.

prGetServerServices

[prGetServerServices] – Setup script for 2012

Parameters:

  • Not a gosh-darn thing.

Returns:

  • ServerList.ServerName
  • ServiceList.ServiceName
  • ServiceList.ServiceDisplayName
  • ServiceList.ServiceStartMode
  • ServiceList.ServiceStartName as ‘Service_Logon’

Spits out everything you and your friends ever wanted to know about Services in the dbo.ServiceList table and the name of the Server they are on.

prInsertDatabaseList

[prInsertDatabaseList] – Setup script for 2012

Parameters:

  • DatabaseName VarChar(MAX) (surprise! parameters!)
  • InstanceListId BigInt
  • Size Float

Returns:

  • Error Code if applicable

Inserts new Database entries as provided by the Powershell script.

prInsertServiceList

[prInsertServiceList] – Setup script for 2012

Parameters:

  • ServerName VarChar(MAX)
  • ServiceDisplayName VarChar(MAX)
  • ServiceName VarChar(MAX)
  • ServiceState VarChar(MAX)
  • ServiceStartMode VarChar(MAX)
  • ServiceStartName VarChar(MAX)

Returns:

  • Error Code if applicable

Wow. Look at all the VarChar(MAX)’s. These could probably be turned into other datatypes that are smaller, especially ServiceStartMode. In the interest of brevity I just made these as versatile as I could.  This may change in the future if I get a chance to tighten this up.

Oh, and this stored procedure inserts new services into the dbo.ServiceList table by finding the ServerName in dbo.ServerList via text search.

prUpdateInstanceList

[prUpdateInstanceList] – Setup script for 2012

Parameters:

  • MSSQLVersionLong VarChar(MAX)
  • MSSQLVersion VarChar(MAX)
  • MSSQLEdition VarChar(MAX)
  • MSSQLServicePack VarChar(20)
  • InstanceId BigInt

Returns:

  • Error Code if applicable

Updates dbo.InstanceList with values passed from the script. If things have changed, this will overwrite the old data in the column.

prUpdateServerList

[prUpdateServerList] – Setup script for 2012

Parameters:

  • IPAddress VarChar(20)
  • OSName VarChar(100)
  • OSServicePack VarChar(100)
  • ServerName VarChar(MAX)

Returns:

  • Error Code if applicable

Adds Server information to dbo.ServerList by matching on the ServerName. This too will overwrite old data with new values passed to it.

Utility.prInsertNewServerAndInstance

[prInsertNewServerAndInstance] – Setup script for 2012

Parameters:

  • ServerName VarChar(MAX)
  • InstanceName VarChar(MAX)

Returns:

  • Error Code if applicable

Utility that Creates New Server and Instance if they don’t exist already.

I Was Told There’d Be PowerShell

And that’s the end of our Database setup. We now have a DB somewhere with tables and procedures, ready to be populated with our Server\Instance.Database information.

With the frame now in place, seats and tires (nice choice of body color maybe), we are prepared to drop the engine in. We’ll get started on that in our next section.

–CJ Julius