The goal of the application is for non-SQL Server experts to be able to see quickly if SQL Server is having a problem. It also works for my clients to send screen shots so I can see what is happening.

The new features fell into a broad range of categories.

Waits

Prior to 2.0, waits were captured every minute from sys.dm_os_wait_stats which means we only saw the wait when it ended.  Starting in 2.0, waits are polled every second from running processes and the page is updated every minute.

This means we only see waits for user sessions and don't include waits for background processes.  This shows fewer waits, but they are timely and actionable. There is a server wait page at /server/:server_key/w2 that compares the two waits.

The Waits on the left are the old-style waits for all processes – including background sessions. The Waits on the right are polled every second and only show the Waits from user sessions.

Blocking

The application displays better blocking information. The sessions are listed by how many other sessions they are blocking. For each session, it shows the root blocker and the immediate blocker (greyed out). This refreshes in real-time as you refresh the page. It also displays orphaned sessions that are blocking.

Memory Reporting

• There is a memory page at /memory that lists all the SQL Servers, OS memory, SQL Server memory maximum, used, and OS memory free. I find I have problems if I let OS Available Memory get below 5%.

File Configuration

Many of my clients run multiple parallel data centers. Keeping multiple instances up to date is annoying and tedious. This version allows the list of monitored servers to be stored in multiple HCL files. Those files look like this:

server "D40\SQL2016" {
  tags = ["newer", "prod", "dashboard"]
  credential = "sqlmonitor"
  key = "d40-sql2016"
}

Servers can be grouped however you like. We use Puppet to push these files to all the servers that run IsItSQL. We have files for development and then production for each business unit. This gives you cool URLs like isitsql.corp.com/servers/d40-sql2016 if you set the key.

You can also use this feature to assign friendly names to Availability Groups. There are more details in an optional folder in the ZIP file as well as a linter to help with editing the files before pushing. 

Other

• The Availability Group page now has a link to JSON output. We use this for simple alerting for heath state – for example, online but not healthy. It also displays the first Listener name instead of the AG name. It displays the send and redo queue sizes. Warning and Alert thresholds can be configured for those values.
• On the database page, if a database is in an Availability Group, it displays the role, health, and send and redo queue sizes.
• Improved support for SQL Server on Linux throughout the application.
• Added a Server Connection Detail page at /server/:server_key/conn that shows if you are getting a Kerberos connection.
• Improved reporting of tempdb size.
• The OS version is shown in more places. In the screen shot for Waits above, The Windows version and architecture are shown. This should also work for Linux and containers. There is a SQL Server Versions page that lists the SQL Server Version, Edition, OS Version, installed date, cores, and memory. That can be downloaded as a CSV.
• SQL Servers running in containers ignore the "Other" CPU category.
• The application now uses the native GO SQL Server driver supplied by Microsoft instead of the ODBC driver.
• The waits and metrics persist between restarts. They also display per minute which helps if servers don't poll for a time.
• The settings page can limit the settings changes to members of a domain group.

I spent time testing Puppet, Chef, Ansible, Microsoft's own DSC tools. In the end I wasn't happy with any of them. And so I did what I often do in situations like this .... I wrote my own. I mean, how hard can it be? Right? 😎

My goal is to help you to define the state of your servers in version-controlled files and push that to your SQL Servers. That starts with a configuration files that looks something like this:

This example will:

• Set the model database to SIMPLE recovery mode
• Disable the sa login
• Set the cost threshold for parallelism to 50

Applying these change to my local DEV box produces these results:

Running it a second time doesn't produce any changes.

You can download this from my corporate site at SQL Server Desired State Configuration utility page. I've already written extensive documentation to get you started. If you have any questions, please use my contact page to send me an email.

I wrote about naming linked servers in 2011.  After living with various schemes, I'm happy with something like what's below.  This would probably work well into the low hundreds of servers.

• Create a zone for static DNS entries.  This should be separate from any other domains you have.  Something simple like static.loc is fine.
• You can use either "A" records or CNAMEs depending on what you're pointing to.  CNAMEs can point to availability group listeners.  "A" records can point to IP addresses.  We still use "A" records for the AGs and have tooling around the cross-data center fail over process to push changes to all DNS servers.
• All the hosts in this zone are named something like db-txn or db-dw or db-apps.  Something short, simple, and descriptive.  So the full name would be db-txn.static.loc.  
• We use prefixes for different types of "things".  So all the DNS entries for databases get a db prefix.
• All linked servers, applications, and users should point to these static DNS entries and NEVER the actual server name or IP address.
• All linked servers should be named for these DNS entries.  For example, the linked server that points to db-txn.static.loc is named db-txn.  I've also considered naming them with a prefix like ls-db-txn but haven't needed that level of indirection so far.
• The hyphen is the key part of this name.  That forces  the name to always be enclosed with brackets.  So you have to run SELECT [name] FROM [db-txn].master.sys.databases.  That makes it MUCH easier to find TSQL code using the linked servers.

Most of these static DNS entries are named for a server (one DNS entry per server).  Some are named for specific databases on a server.  We try to decide what group of databases could logically migrate together.  Many of our databases are on their third generation of server with the same static DNS name.

For example, packaged software from ACME Corporation that requires a database might get a DNS named db-acme.static.loc.

Hopefully this helps your management of linked servers.  

• It's a very early release.  Everything seems to work for me and it passes all the tests.  Please report any issues in the ClearTrace support forums.
• This version is hard-coded to require SQL Server 2017 SMO.  There's a link for it on the download page.
• The previous version supported choosing files via a wilcard or selecting the first trace file and allowing it to process all the files.  Right now, this version only allows a multi-file selection through the dialog box.  That has worked much better for me given how trace files are named.
• The database format is the same as ClearTrace for Trace.  However the SQL batches reported by XE can be formatted differently than trace.  Which means my "normalized" SQL may be different.  I'd enourage you to start a new database – or at least new trace groups.
• Right now it only suports rpc_completed and sql_batch_completed events.  
• The command-line version isn't included in this download.  It really needs the wildcard bit to work again before it's very useful.

Download and it take it for a spin and give me any feedback in the forums.  

Lately I've been spending time writing GO (or GOLANG) applications. I'm a big fan of the language. Often what I write needs to run as a Windows service. I've found a few things you can do to make your life easier.

Use a Shell

There are three "shells" I've found that you can wrap your code in. That means you plug your code into these "shell" applications.

• The standard GO Windows service shell. This provides basic functionality to install, debug, remove, start, and stop the service. It also includes a package to write to the Windows Event Log.
• My modified version of the GO Windows service shell at https://github.com/billgraziano/go-windows-svc. It includes all the features of the official version and adds a few things I've found helpful.
  • The code is reorganized to make it more explicit where to make your changes and add your code. All your work can be done in main.go.
  • It has a setup() routine that will exit the service if it returns an error. I use that to configure basic logging and configuration and anything else I can't continue without.
  • I added the excellent https://github.com/pkg/errors package for better errors.
• The Kardianos "service" package. This works on Windows, Linux, OSX, etc. I haven't worked with it but it seems well used. If I'd known this was available when I was starting I might be using it instead.

Fix the Directory

Windows services run in C:\windows\system32 by default. So that's where your logging will probably go. And that's where it will look for configuration files. I use code like this to look for these next to my executable.

var s string
var err error 
s, err = os.Executable()
if err != nil {
  return "", errors.Wrap(err, "os.executable")
}
ext := filepath.Ext(s)
outfile := s[0:len(s)-len(ext)] + ".toml"
return outfile, nil

Change the Startup Account

After installing the service, I usually change it to run as a domain or managed service account if there's database access involved. You'll also need to set it to start Automatically.

Grant Permissions on the Directory

And of course, that service account probably won't have permissions to read your configuration file or write to a log file. So make sure to grant those on the directory where your EXE lives.

Check the Event Log

If you're using my shell or the GO version, any errors preventing the service from starting should be logged to the Windows Application Log. I'm not sure about the Kardionos version.

ELK (or Elastic Search, Logstash, Kibana) is taking over one of my clients. And I have to admit it's a very handy tool. They are pushing all their logs into it and it's ver easy to search them.

Fair warning: My ELK skills and knowledge are very rudimentary at this point. Elastic Search is the search engine built on top of Apache's Lucene. Logstash is one of many ways to take in log files and push them into Elastic Search. Kibana is the visualization tool that sits on top of Elastic Search. It's all open source as well as available for purchase through the Elastic Search company. There are also numerous online services providing the ELK stack.

Getting data from SQL Server tables into the ELK stack is straightforward IF the tables have an IDENTITY column -- or any single column, increasing, numeric primary key. You can use the JDBC input plugin for Logstash and import your data. We use this for SSRS execution logs and a few application specific logs.

But I wanted to import SQL Server Extended Events and they use the file name and file offset to keep track of where you are in the file. So I decided to write a little utility to read extended event files and write them to Logstash. Because really, how hard could it be?

Six weeks and 160 commits later, XELOGSTASH is mostly, finally, sort of ready to see the light of day. It's a command-line application and reads extended events, converts them to JSON, and writes them to a Logstash installation.

I run it every minute from a SQL Server Agent job and process roughly 30 servers. They generate 1,000 events per minute. Once you get the data in ELK, you get a very basic visualization (this is from my development box).

xelogstash converts extended event data and actions to fields in Elastic Search.

Here are some use cases I've found for this data:

• Find all the servers a domain account uses. Or find all the clients that have connected to any of the nodes of an AG over the last two weeks.
• Show SQL Server exceptions across multiple servers while new applications are being deployed. This is especially helpful for permission errors when applications are deployed using new service accounts.
• Show me the login failures across all my servers
• Show me the SQL Server Error Log and AlwaysOn_health events across all AG nodes in one integrated timeline.
• Show me the timeline of all failed SQL Server Agent jobs for the last week across all servers.

My "little" application ended up doing quite a bit more than just reading extended event files and writing them to ELK.

• It's multi-threaded and can process multiple servers at once. I usually run it at 8 workers to process 30 servers and it never uses more than 10% of a 2 core VM. The initial loads can be much larger but once you get caught up it's very light.
• You have LOTS of control of the JSON. You can rename fields, define common fields, and control the overall structure of the JSON. For example, at one client, all my XE specific fields get nested under an "mssql" field. When testing with some ELK as a Service providers, they wanted all the fields at the root level. That's one line of configuration.
• It enriches the extended event with data such as domain, server name, version, computer, database name from database ID, etc. It also builds a description field where I try to build a single helpful message field. For a SQL statement, that includes the resource usage and part of the SQL statement.
• It can import SQL Server Agent job history for all jobs or just failed jobs. You can do that with a basic query but this also enriches the data as described above.
• It can write it's own log messages to Logstash (of course). As well as standard out and a local log file. Or all three at once.

It's written using the GO language but you can download compiled executables. If you happen to be one of the people using SQL Server and ELK, please give it a try and give me some feedback.

If you just want to test, I used logz.io for testing because they have a free tier. The works best with the following settings:

• Enable strip_crlf
• Copy xe_description to message in the "copies" setting
• Set timestamp_field_name to @timestamp

I have finally jumped on the Extended Events bandwagon. It was tough to pry Trace and Profiler from my cold, legacy hands! But I'm mostly there. I'm still working on a good way to hook up ClearTrace to these things. It might be as simple as requiring you to pull down the XE files. I'd like to make it simpler though.

I've read lots of article about Extended Events that describe how to create and query them but very few about which ones are helpful. Here are the events I've been capturing on our production servers that have been helpful.

Fields

We try to capture a consistent set of fields in addition to the event specific fields. Here's what we capture:

• client_app_name
• client_hostname
• client_pid. This is the Process ID on the client that is connecting. If needed you can consult logs on the client to learn more about what this was.
• database_name. Sometimes I capture database_id also. Especially in older versions where database_name isn't available.
• event_sequence
• server_instance_name. Yes, this is redundant since you're already connecting to the server and you can query the server name. I suggest you capture it anyway. It's very helpful when you start to centralize these.
• server_principal_name
• session_id
• sql_text

There are a few fields I've thought about capturing or have captured but found less valuable.

• Query and Plan hashes. I had high hopes for these but haven't ever used them.
• Session Server Principal Name. We don't do any impersonation that I know of so this hasn't been useful so far. I'm still mulling over collecting this instead of just the server principal name. Ideally I would collect them both and flag any differences. But I'm not there yet.

Events

I typically run two Extended Event sessions: one for logins and one for everything else. We keep logins going back for as long as can on the server. Right now I'm running ten 50 MB files for these on most servers. A few high traffic servers have twenty 50 MB files. It's also important to filter for is_cached is false.

Many of these events overlap with the system_health and AlwaysOn_health sessions. I don't mind the duplicates. They don't occur all that often.

Error Reported

I was initially filtering for just severity 14 (security related) and severity >= 16 (general errors). I'm starting to experiment with capturing 11 and higher but I don't know enough to write anything meaningful yet. I also filter out 17830. For some reason, we get LOTS of them and I've never tracked down why. YMMV.

This event has proven very, very helpful. I capture our applications doing all kinds of interesting things. When we do new deployments, I watch this closely. I often notice problems before the application teams because I can see the application generating errors. I see permission errors, primary key violations, syntax errors, and all kinds of crazy app stuff. We're just starting a project to give our developers and others in the organization easy access to these events.

You will also see pesky things like user generated errors. You can filter those out in your reporting. But I'm not a fan of using RAISERROR with a severity higher than 10 unless it's an actual exception that you plan to fix.

Blocked Process Report

This requires you to set the blocked process threshold on your server. I typically set it at 15 seconds or 60 seconds for servers that run big long ugly queries and 5 or 10 seconds for more transactional system. The trick is to set a value that generates enough but not too many. And don't set it below 5. But it may take some experimentation.

I use this to answer the question, "What was blocking our transactions at 5:30 AM this morning?"

Deadlocks

There are two deadlock events I capture: lock_deadlock_chain and xml_deadlock_report. Between the two of them I get enough information to figure out what happened.

High Resource SQL

This is four events, rpc_completed, sp_statement_completed, sql_batch_completed, sql_statement_completed and I capture all four. The batch level and statement level captures give me slightly different views into what was running. My default filter for these is CPU > 1 second or logical reads > 500,000. You'll need to tweak these based on your environment and how much data you want to review. But these have proven to be a good starting point.

Error Log Written

I typically don't refer to this one as often but it's nice to have in place with the others.

Object Created, Altered, or Deleted

I'm just starting to experiment with these. We currently capture all these through an Event Notification on DDL_EVENT. And that works really well so I haven't had the need for this yet. I do know that you should filter out events on database_id = 2 or you'll get all the tempdb objects. And I'm seeing lots of CEIP extended event work which may be related to the fact that these servers can't see the Internet.

Summary

So those are the events and fields I capture through Extended Events on production servers. I've found this data to be very helpful troubleshooting production problems.

It's finally time to push another release of Is It SQL out the door. This has a few big features I've been working on.

Backups

First, it handles remote Availability Group backups properly. In an AG, you can run a backup on a secondary node. IsItSQL now finds backups on any node and shows them on all nodes.

My backup of AdventureWorks2014 from December 2016 now shows when it was backed up, the backup file name and the instance that completed the backup. And it will show that on any AG node that hosts that database.

Dashboards

The dashboard page was the one feature I was really excited about when I first built the software. But it's become a page I rarely use. This latest build allows you to choose which servers appear on the dashboard by assigning the "dashboard" tag. It will display the first three servers with that tag on the dashboard sorted by the Friendly Name.

Availability Group Failovers

The last big feature handles aliases that change where they point. This is often a listener for an availability group. At one client, every production server has a static DNS that is used by applications. In some cases, individual databases also have a static DNS entry. This allows us to easily move to new servers or domains.

In SQL Server, waits and disk I/O are tracked in aggregate. The value I show in the graph is the value reported less what it was a minute ago. Which works fine until an AG fails over or we repoint our static DNS to the other replication subscriber. I always handled negative changes but not super huge positive changes.

Now it checks for a change in the underlying server for for a server restart. If either one of those changes, we put a message in the log and reset the counters.

You can download Is It SQL from the scaleSQL site.

Today, through various snafus and situations, I found myself waiting a LONG time for a lock to clear up. Here's how I got an alert to pop up on my phone (an iPhone) when that stupid SPID finally rolled back.

You'll need to have database mail configured and ready to go.

First, configure your iPhone to display an alert when you receive VIP mail. Open mail and click on the little "i" in the circle by the VIP section in the mailboxes list. Click "VIP Alerts" and configure as desired. I have all of them selected and a sound configured.

Second, send yourself an email from the server using sp_send_dbmail.

Third, on your iPhone, open the email and click on the From field. That will open a contact form for that email address (aka the server). There should be an option to add to VIP on the screen.

Fourth, write a query to email yourself. That should look something like this (typed from memory and subject to typos):

USE msdb;

SELECT TOP 1 * FROM TheDB.dbo.TheLockedTable;

EXEC sp_send_dbmail
    @Subject = 'It''s Done!',
    @Recipients = 'YourEmail';

Make locking work FOR you for a change. The SELECT will block until the lock clears. It sends you an email and you'll get an alert moments later.

Fifth, remove that server from your VIP list. The last thing I want is alerts on every email from my database servers.

I'd like to tell you this release has some great unifying theme. It doesn't. It's just a bunch of things I felt like fixing.

But let's start with tags. That's what I originally wanted to focus on for this release. The first change is to separate out the user-tags from the system generated tags. (And you can enable the tagging feature by signing up for the newsletter). Most times I'm using I want to see how I've tagged servers. But every now and then I'd like to see by SQL Server version, or edition, or domain.

There's also a tag summary page that lists one row per tag. It counts the number of instances, disk I/O, RAM, cores, etc. It's a great way to get an overview of your environment. And it works for system generated tags so you can quickly how much data and activity is taking place in each version for example.

I also made some changes to the detail for tags or servers. First, I now show the total server memory as well as the amount used by the SQL Server engine. The total allocated to the server is what I want to see most of the time. If you hover over the number of cores, it will also show the number of cores actually used. This is just a simple percent used multiplied by total cores. I find this especially interesting when I tag groups of VMs. Then I can see how many cores they are really using. I may try to make this more prominent in future releases. I'm still trying to figure out what I want to see.

Many of the wait types around SQL Server 2016 are gradually getting hidden away. The latest was related to the Query Store. And if you aren't using this you really should be. It's a handy little feature. If you find other wait types showing but you don't think they should please use the contact page and let me know.

There are lots of little cosmetic fixes. The version doesn't display on the list servers any more but does appear on the server detail page. If you don't have the right permissions I display a better error message. The server summary page groups a little better into sizes (core count, databases, etc.) vs. usage (CPU percent, disk I/O, etc.).

You can download 1.0.27 on the Is It SQL page.

It's finally time to push Is It SQL 1.0.25 out the door. I sent it to the newsletter subscribers last week and we haven't discovered any problems yet.

Before upgrading, please read the README in the zip file! Especially if you have custom connection strings and this is running as a service. I rewrote it just for this release. And there are some security and encryption implications in this release that you need to understand.

The first big change is that we now have a simple GUI for servers and settings. You can access this via the little gears icon in the header menu.

This replaces the servers.txt file. You can add, edit and delete servers through this.

If you're upgrading, it will import your servers.txt and rename it as servers_imported_YYYYMMDD.txt or something like that. I'd also encourage you to delete this file -- especially if it has custom connection strings with passwords.

All the server connections are stored in connections.json in the config directory. Any password or custom connection string is encrypted in the file. It's encrypted using the Windows DPAPI based on the account that is running the application. This makes changing the service account or machine hosting this tricky so I encourage you to get that right.

New Server URLs

Previously every server was assigned a URL in the format http://localhost:8143/server/S1. And the number part was assigned based on the order of the server in servers.txt. Putting the servers in a JSON configuration file and controlling the edit process means I can finally have consistent URLs. And of course they're GUIDs!

So the URL for a server is now something like this:

http://localhost:8143/server/f8e451b9-d2a2-472a-be8a-7ec90df23347

And that GUID won't change. That should make it easier to bookmark servers.

Settings

There are a few basic settings you can control. These include:

• The port the service uses to serve the web site. I'd love to use port 80 but IIS usually takes that. This lets you run on any port you want.

• The number of servers that will be polled concurrently. This defaults to 4 times the number of cores but will always be at least 8. That's been a reasonable default up to a hundred servers or so.

• Backup alert thresholds. Signing up for the newsletter gets you access to some of the advanced features. One of those is alerts on missing backups. You can now set the site-wide threshold for missing backups. It defaults to 36 hours for full or differential backups and 90 minutes for transaction log backups.

• Admin page security. There's a setting that controls security on these settings and on editing servers. Right now the two options are anyone who can reach the web site can change this or limiting it to people that are running on the console of the server itself. These were simple to implement and seemed like a reasonable compromise. I've been testing Activity Directory authentication but that's a pretty big leap.

Every setting except the port takes effect immediately.

Other Changes

There are lots of smaller changes in this release.

• If you've subscribed to the newsletter, you can monitor Availability Groups. This release shows failures for both the server and the AG if there are issues. Previously it would show the server as failed and would not update the AG.

• We now have a favicon. And of course it's a bright orange question mark.

• The application handles backups for servers in time zones east of you. SQL Server reports backup times in the local time zone so it was seeing future backups and not reporting missing transaction log backups properly. This now handles those correctly.

• The menu is simplified. Anything that reports across servers now falls under a "Global" menu. This includes Availability Groups, Backup Issues, Database Mirroring Status, and the Summary Page.

Give it a try and let me know what you think. My email is in the README or you can use the scaleSQL contact page to reach me. I also added an Is It SQL support forum to the SQLTeam Forums if you'd like to chat there.

The latest release of Is It SQL now shows missing backups.

Backups

The top section gives you a summary of all servers with missing backups and the bottom section lists each individual database that doesn't have a good backup. Right now a "good" full backup is defined as being a full or differential within the last thirty-six hours. Further, a database in FULL recovery model expects a log backup within the last ninety minutes.

The last backup query can be a little expensive so it only polls backups every five minutes. If you have more than a million rows of backup history it doesn't poll that server's backups at all. It will include a message indicating this.

There's a simple text file you can edit to exclude servers and databases from expecting to be backed up. If you have multiple connections to a server (availability group name and node name) it will still only appear once.

And if you're reading this, welcome to the new blog. That means the RSS feed updated properly. Or at least you found it some other way.

More Concurrent Polling

The number of concurrent servers it will poll has been increased to eight. This seems to work well so far. I originally increased this to handle slow backup queries but finally gave in and just fixed the backup queries. But eight concurrent pollers seems to work well so far.

The "Infographic"

I really want to add a cool infographic showing a summary of your environment. I've got the numbers but not the graphic part. So for now I'm calling it a summary.

I'm surprised at just how many databases there are in some the environments I monitor with this. It's also been interesting to watch the total IOPS across all servers in near-real time. As you can see, my little development box isn't very interesting.

Miscellaneous

• Various wait group names have been cleaned up. Mostly with regard to SQL Server 2016 and availability groups.

• If sys.dm_exec_requests shows a percent complete then so does Is It SQL. If you hover over the duration on the Active Sessions page for a server it will show the start time and percent complete. I mostly use this to obsess over backups and restores.

One of my clients has applications that use MSDTC.  We’re spinning up a SQL Server 2016 Availability Group to take advantage of the MSDTC support it introduced.  Most of these distributed transactions take place across linked servers.  And of course the new servers are in a new domain with a very limited trust with the old domain. 

I found lots of articles and checklists for configuring MSDTC but none had everything I needed.  This is the checklist I came up with to configure cross-domain MSDTC with a limited trust in place.  (I don’t know what kind of trust it is.  I just know I have to put accounts into Domain Local groups in order to use them across the trust.  And it’s one way.)

Resources

I found LOTS of articles on configuring MSDTC.  These are the ones I found to be the most comprehensive.

1. Troubleshooting MSDTC Communication Checklist – This is one of the better checklists I found.  Has a good list of required ports.
2. Troubleshooting Problems with MSDTC – Another good resource
3. DTCPing – A VERY useful utility to confirm basic connectivity.
4. Troubleshooting MSDTC issues with the DTCPing tool - Great list of errors that DTCPing can return
5. DTCTester – Utility to perform a distributed transaction.

Configuration

There are the steps that future Bill will need to get this configured next time.

Name Resolution

We were crossing a domain boundary and weren’t getting good NetBIOS name resolution.  I had to put a host file entry on both servers pointing to each other with just the machine name.  I eventually had to put a second name in for the Availability Group Listener.  You need to be able to have a ping –a resolve the names on both sides using only the name and not a FQDN.  And if you’re editing the hosts file don’t forget to run NOTEPAD as an Administrator.

Fix the Matching CIDs

I’m not sure exactly how this came to happen but both these machines had the same CID values for MSDTC.  (Note: I still don’t really know what a CID is but I know they can’t have the same one – see the second document above.)  The only way to change the CID value is reinstall MSDTC.  If you’re building machines from images you’ll probably have this problem.  You reinstall by running:

msdtc –uninstall

msdtc –install

I suggest a reboot after each step.  Until I rebooted I didn’t see the service installed.

Open the Ports

I had the following ports open in the firewall:

1. Port 135 both ways (for RPC)
2. The dynamic ports 49152-65535

MSDTC starts talking on 135 and then jumps to a dynamic port.  I think the firewall people may have done something fancier but that’s what I told them.

I also had to:

1. Enable the three inbound Windows Firewall rules for Distributed Transaction Coordinator
2. Enable the one outbound Windows Firewall rule for Distributed Transaction Coordinator

Those rules exist but they were disabled by default in my environment.

Configure MSDTC Permissions

Using DCOMCNFG.EXE I had to enable the following permissions in MSDTC.

1. Network DTC Access
2. Allow Inbound
3. Allow Outbound
4. No Authentication Required – This one was a little frustrating.  I’d prefer to have them authenticate but I haven’t worked on that enough yet.

There are screenshots of this all over the web that you’ve probably already found.

DTCPing

At this point you should be able to get DTCPing to work.  You run it on both sides and they talk to each other simulating a transaction.  It give good error messages in the application.  It also writes a text file with more detail diagnostic logging.  It’s a very handy utility.  If you get any errors you’ll need to work through those.

DTCTester

This little command-line utility actually runs a distributed transaction.  It creates a temporary table and and inserts a row into it.  You’ll need to configure a 32-bit ODBC entry.  Look for C:\Windows\SysWOW64\odbcad32.exe to make that entry.   The 64-bit utility is in C:\Windows\System32 and has the same name.  That’s some confusing naming right there.  You’ll also need a SQL Server login and password.  If you’re going to test against multiple machines your ODBC source should point to a hosts file entry.  It makes testing much easier.

The latest release of Is It SQL is out and includes a variety of features.  I’d like to start with some screen shots of previous features.  First up is database mirroring.  You can see the mirroring status of a database when looking at the databases for a particular server.

For each database it will show the role, whether it’s synchronized or not, and whether the safety is on or off (synchronous vs. asynchronous).  It will also show the send and redo queue for the database. 

If you sign up for the newsletter (on the Is It SQL page) it will send instructions on enabling the Enterprise features.  That includes a page showing all mirrored databases across all servers you’re monitoring.  That page includes a “Priority” column that brings any database with issues to the top.  If it’s disconnected or has a send or redo backlog it will bring it to the top of the list.  If you have instances under multiple names, for example static DNS entries pointing to mirroring partners, it only shows each database once.  That gives you a quick way to see all mirrored databases across your enterprise.

All tables are sortable by clicking on their heading.  So you can sort these pages by the send queue or redo queue or the priority value I calculate or the database size or server or any other column.  The size of the log also moved to its own column so it’s sortable now.

All the data is polled every minute from the servers and then displayed back on the web page.  The pages auto-refresh every minute.  Some data is polled in real-time when you refresh the page.  This is indicated with a cool little lightning bolt by the section.

The availability group monitoring is still pretty basic at this point.  After you add the nodes to be monitored it discovers any availability groups and displays them all together on a page.  The data comes from the AG DMV’s.  It is nice to have them all in one place and as easy to access as a web page.

The list of servers was just showing the bytes per second read and written for disk I/O.  I’ve expanded this to include the IOPS, average I/O size, and the average latency.  It includes that for both reads and writes.  318ms reads.  Yuck.  I hope yours are better!

At the bottom of each list of servers there’s a summary.  It shows the total disk I/O, batches per second, RAM used, data file size and log files size.  If instances are included multiple times it only includes it once.  This also works for various tags.  For example, I’ve tagged servers based on their data center so it’s easy to see how much traffic each data center is taking.

And that’s where we are so far.  It’s a handy little utility all in a single 12MB executable.  It’s easy to run as a service so it’s always available.  I encourage you to sign up for the newsletter.  It includes information on enabling the Enterprise features, tips and tricks, and new releases.  You can download it from the Is It SQL page. 

Release 1.0.22 is out and available at the Is It SQL home page.  The biggest feature here is database mirroring. 

There are two ways to see database mirroring.  The first is on a server page it will show the databases that are mirrored.  There is also a global database mirroring page that shows all mirrored databases.  There’s an added column named “priority” that highlights databases that aren’t connected and synchronized.  It also shows shows databases that have send or receive queues.  Since the column is sortable it should always show databases with “stuff” going on at the top.

Please note that you’ll need to sign up for the newsletter for instructions on how to enable the beta features.  You can find the link on Is It SQL page.

Data that is pulled from servers when the page is refreshed is now identified with a little lightning bolt.  Most data is cached every minute but some data refreshes with each page load.  This helps identify which is which.  The basic rule of thumb is that everything is cached except what has the lightning bolt.