IsItSQL 2.0

I've been making small improvements in IsItSQL and finally released a new version. 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.

Posted on: 20 January 2024 | 12:08 pm

SQL Server Desired State Configuration

At one of my clients, I've seen the benefits of using tools like Puppet to push configurations to servers. I've long tried to write idempotent configuration scripts for my servers. Those are scripts that can be run multiple times without changing the end result. They try

Posted on: 1 July 2020 | 7:48 am

Naming Linked Servers v2

I've always had a love-hate relationship with linked servers.  On one hand, they are very easy to use and query.  On the other they can be a pain to configure, secure, and performance tune.I wrote about naming linked servers in 2011.  After living with

Posted on: 28 April 2020 | 7:38 am

ClearTrace for Extended Events

I found the time to update ClearTrace to support Extended Events.  You can download ClearTrace for Extended Events from my corporate site.  A few notes about this buildIt's a very early release.  Everything seems to work for me and it passes all the tests.

Posted on: 7 July 2019 | 5:05 pm

Running GO as a Windows Service

Checklist for successfully running a GO executable as a Windows service

Posted on: 16 July 2018 | 7:35 pm

Send SQL Server Extended Events to Logstash (ELK)

xelogstash is a great way to send SQL Server Extended Events to Logstash and Elastic Search and Kibana (ELK)

Posted on: 13 June 2018 | 6:24 am

SQL Server Extended Events in Production

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

Posted on: 16 May 2018 | 9:02 am

Is It SQL - 1.0.28 now with better AG backups

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

Posted on: 15 May 2018 | 12:45 pm

Get a Phone Alert When a Query Finishes

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

Posted on: 4 October 2017 | 9:48 pm

PASS Summit Speaker Idol 2017

I’m extremely honored to have been chosen once again to participate in Speaker Idol at PASS Summit 2017! I’m in deep trouble too, considering the contestants this year: https://www.dcac.co/syndication/your-pass-summit-2017-speaker-idol-contestants-are I’ve previously blogged about why Speaker Idol is such a great opportunity: http://weblogs.sqlteam.com/robv/archive/2015/09/28/pass-speaker-idol-2015.aspx …and have shared my notes and feedback from the first time I’ve participated: http://weblogs.sqlteam.com/robv/archive/2015/10/24/notes-from-speaker-idol-2014.aspx …and my good friend Shabnam wrote about her experience in 2016: https://shabnamwatson.wordpress.com/2016/11/10/pass-summit-speaker-idol-2016/ …and for those interested you can see the Speaker Idol 2015 presentations on YouTube: https://www.youtube.com/watch?list=PLoGAcXKPcRvbJ5ge4J2DHSleRy4OjSlJw&v=Koz-2S3sPso https://www.youtube.com/watch?list=PLoGAcXKPcRvbJ5ge4J2DHSleRy4OjSlJw&v=83sj1ZMp6vs https://www.youtube.com/watch?list=PLoGAcXKPcRvbJ5ge4J2DHSleRy4OjSlJw&v=SpYTZ2GCVWI https://www.youtube.com/watch?list=PLoGAcXKPcRvbJ5ge4J2DHSleRy4OjSlJw&v=q5cDX_eHa-I The 2016 Speaker Idol videos are available on the PASS Summit recordings package for purchase or download from PASS. If you attended Summit that year you should be able to view them. Also check with your local SQL user group, if they are a PASS Chapter they probably have the videos available. Denny Cherry has a video chat for contestants with a lot of great advice: https://www.youtube.com/watch?v=8ZRqn9V9JSQ ...and there's also this really helpful commentary by some previous judges: https://youtu.be/Lz_4BTsI3i4 If all of this seems overwhelming, don’t be alarmed! There’s a few key things I’d recommend focusing on that have helped me and others: The 5 minute time frame is the killer feature of Speaker Idol. The more attention you pay to meeting this limit the better you’ll do Aim your rehearsal to hit between 4:30 – 5:00, 4:45 is best. This leaves some buffer time for you to handle unforeseen circumstances or technical difficulties. Don’t go below 4:30 when you actually present, you can add a summary or a simple item to fill the remaining time instead. As a rehearsal aid, or even as your presentation method, consider the Ignite Talk format:  http://www.ignitetalks.io/ Ignite talks are 5 minutes (the length for Speaker Idol presentations) but the slides automatically advance every 15 seconds (20 slides total). It’s a great format to help with timing, and it keeps the presentation flowing. You can always tweak your timing (either duplicate a slide you need more time for, or simply adjust as needed as long as you end at 5 minutes). If nothing else it’s a good starting point if you’re adjusting a longer presentation or starting fresh with new material. Zoomit! If you really dig into the advice and videos you’ll think that using ZoomIt or similar tools is clichéd and taken for granted. It’s not. Slide visibility is a consistent complaint at PASS Summit and you DO NOT want to be the lone Speaker Idol contestant with small fonts. I prefer NOT using ZoomIt. I increase the PowerPoint font sizes, and install SSMS 2016 or higher which has built-in zooming and presentation modes. I also avoid using laser pointers, instead using some underlining, color highlights or basic animation to emphasize something on the slides. Shabnam’s blog has some advice on font sizes that you should follow. Rehearse, Rehearse, Rehearse! Like the old saying “How do you get to Carnegie Hall?”, practice makes perfect. If you read my blog notes from 2014, I did much better in my 2nd round because I took a few hours to run through my presentation entirely for timing (and just before the final round too!) If you’re worried that the presentation may become rote or stale from multiple deliveries, I’m confident that it won’t, because you’ll have an audience and you will react differently in their presence. The rehearsal is there to make you comfortable with the material and the timing. It also protects you against technical difficulties, because: Handling and recovering gracefully from mishaps counts in your favor I had microphone difficulties in 2015 but I moved forward through the presentation despite them, and the judges were complimentary about it. The winner for Speaker Idol 2016, Peter Kral, had a serious technical issue crop up in his first round. He modified his presentation in the Final round to avoid the problem, and he won! Handling problems is an important skill to have in all facets of life. This is where rehearsal comes in; you should actually rehearse UNTIL you encounter a technical difficulty, so you learn how to deal with it. Also, if you can present at a user group or SQL Saturday event that offers Lightning Talks, do so! SQL Saturday Oregon is scheduled the weekend prior to PASS Summit, and they offer a Lightning Talk slot for speakers. This is a great way to shake out any last bugs in front of an audience and to get feedback from them. Finally, you should especially take time to rehearse at PASS Summit in the session room where Speaker Idol will be held. If you get to Seattle before Wednesday you should be able to check the room out, and possibly rehearse or at least walk around the stage to get the feel of it. This has been an invaluable help to me and I’ve done it every time I’ve participated. At the very least you should do a technical check of your laptop with the video and audio connections.  This is the last “unknown” factor in your presentation, and the easiest and last way you have to fix it before the big event. As long as security doesn’t throw you out, stay there and practice as long as you can! Attend as many Speaker Idol sessions as you can Even if it’s not your turn, you’ll get preliminary feedback from the judges and can tweak your presentation beforehand, because you should: Incorporate all feedback you get from the judges I can personally vouch for this as it got me to second place in 2014. You can also see its effect in David Maxwell’s presentation in 2015, which contributed to his ultimately winning that year. Which brings me to: Wildcard candidates have the same chance as first-round winners! The 2015 Speaker Idol winner was a wildcard, and I placed 2nd as a wildcard in 2014. There have also been dropouts each year Speaker Idol has been held (and some last-minute replacements for 2017), so your chances of advancing are much better than would first appear. Don’t accept lack of placement after the first rounds as a failure; you never know what might happen, and even if you don’t advance: You spoke at PASS Summit. Congratulations! This is the most important aspect of Speaker Idol, regardless of who wins. 400-500 people submitted to speak at PASS Summit in 2017, less than 200 were chosen, and probably only 100 if you exclude Microsoft speakers. At least 6 non-finalists have spoken or will be speaking at PASS Summit since presenting at Speaker Idol. As I said previously, it’s a no-lose situation. Congratulations and good luck to all Speaker Idol 2017 contestants! -Rob

Posted on: 25 September 2017 | 1:43 pm

Is It SQL - 1.0.27

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.

Posted on: 27 July 2017 | 9:00 am

Is It SQL has an Admin GUI

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

Posted on: 11 May 2017 | 5:59 pm

Is It SQL build 24 shows missing backups

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

Posted on: 20 April 2017 | 6:17 am

Configuring MSDTC Across Domains

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

Posted on: 31 December 2016 | 9:11 am

Configuring MSDTC Across Domains

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.  Troubleshooting MSDTC Communication Checklist – This is one of the better checklists I found.  Has a good list of required ports. Troubleshooting Problems with MSDTC – Another good resource DTCPing – A VERY useful utility to confirm basic connectivity. 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: Port 135 both ways (for RPC) 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: Enable the three inbound Windows Firewall rules for Distributed Transaction Coordinator 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. Network DTC Access Allow Inbound Allow Outbound 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.

Posted on: 31 December 2016 | 3:11 am

Is It SQL now shows Availability Groups

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

Posted on: 19 December 2016 | 8:02 am

Is It SQL now shows Availability Groups

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. 

Posted on: 19 December 2016 | 2:02 am

Database Mirroring in Is It SQL

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

Posted on: 10 November 2016 | 3:40 pm

Database Mirroring in Is It SQL

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.

Posted on: 10 November 2016 | 9:40 am

Tagging Servers in Is It SQL

Release 1.0.20 is out.  You can download it from my corporate site at http://www.scalesql.com/isitsql/. This release adds a beta feature to allow you tag servers and then filter the list by tag. Instructions for enabling beta features are available when you sign up for the newsletter.  The newsletter is limited to Is It SQL features and releases. The current release automatically tags servers based on their version.  This release also tags servers based on their domain too.  It allows you to enter user-defined tags for each server.  Common tags I’ve been using include development, test, production, disaster recovery, physical location, virtual vs. physical, application support, reporting, SSAS, SSRS, etc. When I first starting writing this tool and using it I mostly focused on the key production servers.  While testing this feature I’ve gradually added more servers to it.  It’s been very helpful keeping an eye on servers I rarely look at but seem to have weird issues (cough, dev boxes, cough).  Tagging allows me to filter those out of my main list but still be able to find them when I need them.

Posted on: 20 September 2016 | 3:40 am