Redgate’s DLM Dashboard: The awesome continues

The artist formerly known as SQL Lighthouse, underwent a HUGE overhaul, and now goes by the name DLM Dashboard. This tool allows you to: “monitor your database schemas and alerts you to schema changes, including database drift”. And even though I find Redgate’s tools awesome, I’m still going to be brutally honest in this blog (and I know they appreciate that!).

In the second half of this blog post, there is also a Q&A with a Product Marketer of Redgate. In that Q&A, he answers a few open questions that will clarify the product and the future of this product.

 
Download and install
When you download and install the Redgate DLM Dashboard, you get a number of things. The most important things the installation folder (C:\Program Files (x86)\Red Gate\DLM Dashboard 1) contains are a webserver and a monitoring service. These are used to host the webinterface of the DLM Dashboard, and the service needed for monitoring your environments and databases. There are also 3 Windows services installed: “DLM Dashboard Monitoring Service”, “DLM Dashboard Storage Service” and “DLM Dashboard Web Server”.

 
Configuring your environment
After the installation, the DLM Dashboard is started:

 
When you click on “Set up DLM Dashboard”, you are redirected to the configuration page. First you need to add a project:

 
I created a Sandbox project, in which eventually all my environments will be configured. So my project will contain my 4 environments (DTAP). DTAP stands for Development, Test, Acceptance and Production. More on DTAP can be found here. In this test, I’m going to start with configuring a Development environment:

 
After adding that, you need to add a server:

I’m using the sa account to connect to this server, because it’s on a stand-alone server. If you start using the DLM Dashboard (or any other software), it’s bad practice to use the sa account. So if you want to use this in your environment, don’t use the sa account!!!

 
In the next screen you see, you get the question if you want to run the monitoring script yourself, or if you want the DLM Dashboard to run it:

 
When the script completes, you end up with a new database on the server that you configured in the dashboard. The database is named Redgate, and the name of the database can’t be changed at this point. For further information on this, check out the Q&A at the bottom of this post.

This database contains only 1 table named “DDL_Events”, to store all the changes that happened in your database(s).

The DLM Dashboard also creates a server DDL-trigger called “RG_SQLLighthouse_DDLTrigger” (which can be found under “Server Objects” -> “Triggers” in SSMS). This trigger registers all the changes on your database, and stores those changes in the Redgate database.

Now you need to combine all the configured items (Environment, Server and Database) in 1 last configuration:

 
I’ve executed these steps (configure an environment, add a server, and add a database) twice, to add a test environment as well. I’ve skipped the Acceptance and Production (which would complete the DTAP) in this test. So now my dashboard looks like this:

 
This is the default view when you just configured your databases. The schema of your database is basically a snapshot of your current database status.

 
Making changes, and marking versions
After you’ve configured the dashboard with all your environments (in my case 2 environment, just for testing purposes), it’s time to make some changes in your databases. Let’s add a table to the Sandbox database on development:

USE Sandbox
GO


CREATE TABLE dbo.TriggerSchemaChange
  (ID INT IDENTITY(1,1),
   Value VARCHAR(10))

 
If you wait for a few seconds (the dashboard will automatically refresh), you see that one of your databases is starting to “drift”:

 
If you click on “Review”, you get an overview of the changes that were made, and by who they were made:

 
In this case, I’ll tag this version as 1.0.0.1, and save that change. The dashboard changes, and the new version is shown. And if we execute the same change on the other database, the DLM Dashboard automatically recognizes the version:

 
You just need to acknowledge the “new” version on the test environment, just like on the development environment, and your dashboard looks all okay again:

 
The world ain’t all sunshine and rainbows…
Even though I think this is a wonderful way to keep an eye on your environment, there are still a few things that are missing in my opinion. I’ve asked Redgate these questions, and Andrew Denty (Blog | @andrewdenty), who is a Product Marketer at Redgate, was kind enough you answer these questions. This Q&A is published after getting permission from Andrew and Redgate. Thanks again Andrew!

 
1) What is the delay in tracking changes and refreshing the webpage, and can we change that?

The DDL trigger we install on the SQL Server puts entries into a table each time a database is changed.

Currently we poll the server every 13 seconds and query that table to see if there have been any changes.

This at the moment is not configurable, but should be as it is not a lot of work (and will probably be done before we leave beta). It’s also worth pointing out that you don’t need to hit refresh or anything – the dashboard live refreshes on its own.

 
2) Can we change the port number on which the webinterface runs (default 19528)?

Yes see http://documentation.red-gate.com/display/SL1/Changing+the+port+SQL+Lighthouse+uses

 
3) Can we change the database name (rename from RedGate to DBA for example), or log events to a dedicated database or server? And how does it react when the target server isn’t available?

At the moment no, the Redgate database is hard coded into the application, and is expected to be on the same server as the databases we are monitoring. The DDL trigger lives on the server so it always runs if the server is running. If DLM dashboard cannot reach the SQL server it will show you in the UI the server is not available. When it becomes available again DLM dashboard will check if there has been changes whilst it has been unavailable’.

 
4) Why do you use a DDL trigger, instead of Extended Events?

In SQL Lighthouse, because our DDL Trigger runs when a change happens, and crucially because it runs synchronously with the change, i.e. the change is temporarily blocked until we return. Therefore we not only get the information that we’re given, but we can also ask for other information, like the:

1. Accurate date it happened
2. The application that made the change
3. The IP address that made the change
4. The nest level, e.g. was this a change made directly, or did a change run a DDL Trigger + that DDL Trigger made this change
5. The transaction ID to help us group changes together

In contract, events run asynchronously, so by the time we see the message on the queue, the information above *may* still be queryable, but will probably not be available, and we can’t rely on it. The plus side of events is that they can’t break anything, because they run asynchronously, whereas if the DDL Trigger throws an error it’ll break what the user is doing.

In summary, we want the extra information so had to go with DDL Triggers.

 
5) The configuration limitations, are these just in the beta? The current configuration limitation is 5 database per environment

At the moment the beta isn’t designed to scale. We’re going to be working on improving the performance and scalability over the coming month. When we leave beta we’re planning to have a pro and the current free version. The pro version will allow you to monitor more instances and databases than the free version which will have some limitations. We’re still working out the exact details though.

 
6) Is there any SQLCompare integration planned? So for example, an overview of the differences between 2 environments (Development and Test), or syncing of 2 databases on different environments?

Yes, we’d like to add this to the Dashboard (assuming you have SQL Compare as well). We’d like to do this later this year– but we’re being agile and responding to feedback so we might change our plans and do something else first. Please do vote this up on UserVoiceas we’re using that to help us decide what to do next.

 
7) What is the plan on Octopus Deploy integration? We’re looking into that for automatic deploys, and it would be great to deploy a version from DLM Dashboard for example

Triggering deployments from the Dashboard isn’t something we have planned, though we do want to help be part of your release management process. I’d like us to be able to report back to Octopus if there has been drift away from the release it thinks is deployed (e.g .a hotfix has been made directly on production), so that you can get those changes back into process. Our SQL Releaseproduct (which is a database deployment plugin for Release Management tools like Octopus) may well use elements of the dashboard in the future.

 
 
Bugs
There are still a few bugs in the software:

– If you remove your project, the whole dashboard is empty, the database monitoring is still active, but you’re not asked if you want to disable/remove it completely
– If you remove your database, the database monitoring is still active, and Redgate database isn’t removed
– If you’re adding a column to a table, 2 columns are marked as changed (bugfix in progress)
– Cancel button that doesn’t work on the email notification screen (bugfix in progress)
– Can’t mark your initial version to a certain name (for example, tag it as version 1.0.0.0) (bugfix in progress)
– If your machine name is long, it’s trimmed in the review changes screen (bugfix in progress)
– Sometimes the webservice doesn’t start after a reboot of the machine, and you need to start it manually (bugfix in progress)

As mentioned, most of these bugs are already fixed, and ready for the next release. Redgate is really fast with picking up on bugs and fixing them. So if you find any bugs, please let them know!

 
Recap
Looking at the possibilities of the DLM Dashboard, and the version that is available now, I’m pretty confident this can become a great product! Redgate haven’t announced the differences between the free and paid versions yet, but there will always be a free version. Also, knowing Redgate, getting the paid version won’t be the biggest problem to overcome. Their licenses are pretty affordable, and the tools are indispensable if you ask me.

I think Redgate discovered a whole new market with this. They are jumping on the “release early, release often” train, and they do it quite well on the first try! I’ve never seen database professionals do this, in all the companies I’ve seen in the past. Only the .NET developers did that, and we all mocked them for it. But now, we’re starting to see a shift in the market, and Redgate is willing to help us on this. And for that, I want to thank the people of Redgate!

Looking back at 2014, and forward to 2015

The last year was a little bumpy, and had a lot of ups and downs. Looking back at 2014, I accomplished a lot of cool things, and set some things in motion for 2015 already. So what happened for me in 2014?

 
FORG
2014 was the first year I joined the Friends of Red Gate (FORG) program. I was surprised and felt honored, that I got the confirmation in February that I was a part of FORG. Red Gate has always been a company I respected a lot (so much, that some people asked if I had Red Gate stocks, when I mentioned them again in a conversation). The fact that they let me in their program was a huge honor for me. They enabled me to contact the product teams directly, which (hopefully) ended up in us (FORG members and Red Gate developers) improving the tooling this year.

For next year, I’m hoping I can continue working with Red Gate. There are a lot of things I would like to do in cooperation with Red Gate, that I didn’t or couldn’t do this year. Hopefully 2015 will be the year that I can do some of the things I have on my to-do list!

 
SQLCoop
In February of 2014, I started SQLCoop with 3 other people:

Julie Koesmarno (Blog | @MsSQLGirl)
Mickey Stuewe (Blog | @SQLMickey)
Chris Yates (Blog | @YatesSQL)

 
We wrote a total of 8 posts this year, in which we talked about a number of subjects. Hopefully for next year, there will be more posts about various subjects.

 
Blogging
My blogging in 2014 didn’t go exactly as planned. At the end of last year, I set myself a goal: try to blog more often, and try to write a blog post once a week. I really tried to keep up with that pace, but unfortunately I lost that pace early in the year. But I did manage to publish 30 posts this year (including this one). This is one more than last year. So I did reached my goal (blog more than last year), but not as much as I would’ve liked.

 
Blog traffic
The blog traffic increased again, in a way I didn’t expect. Between 2012 and 2013, the traffic to this blog was quadrupled. This was a stunning success, and I didn’t expect any growth for this year. But looking at the statistics right now, I see that the visitors to my blog are more than doubled comparing to last year! I was hoping for a small increase, but not this much! Looking at that, I can only hope I helped a lot of people save time, with the ramblings I call blog posts!

 
Events
This was the year of the great events. In July, I attended SQLBits in Telford, UK. This was a great event, wonderfully organized, and certainly an event everyone needs to visit at least once! People told me this was the biggest and nicest SQL event in Europe, and I can tell you: they weren’t lying! What an event, what an attendees, and what a great job from the organizers!

In October there was SQL Saturday Holland. This year was (again) better than last year, and it was a great event! Also, I got the chance to volunteer at the event. Definitely something I’m doing again next year. It was really great to help the organizers out. It was a wonderful day with great sessions, and it was the moment I got my first speaking opportunity. Nothing but good things happened that day.

 
New friends
This year I’ve also met a number of wonderful people from the community. A lot of them I met at SQLBits this year, but also at SQL Saturday. It was great to meet Matan, Tobiasz, Chris, Julie, Nicky, Pieter, Brent, Kevin, Grant, etc, or talk to them again. Meeting all those people really shows how big and diverse the community is, and it reminds me why I like this community so much. I’m confident I will meet a lot of cool people next year, and catch up with people again after not seeing them for too long.

 
Speaking
This year I started to seriously think about speaking at events and user groups. All the good stories I heard from friends in the community made me consider this. In July during SQLBits, I submitted 3 sessions for SQL Saturday Holland, and that was the first step. I wasn’t surprised that I didn’t get picked. There were a lot of good speakers, and I was a newcomer with no experience. But at SQL Saturday Holland, I met Pieter Vanhove (Blog | @Pieter_Vanhove). We talked about all different kinds of subjects (including speaking), and he invited me to do a session at the SQL Server User Group Belgium in January 2015. This means I’m speaking at my first ever User Group session. Hopefully this will be the start of something good, with which I can give back to the community in time.

 
I want to with you all a very happy, successful and interesting new year! Go and try to make 2015 your year, and take that leap of faith when you have the chance! I know I did! 🙂

Become a T-SQL Hero with SQL Prompt

Since 1999, Red Gate Software has produced ingeniously simple and effective tools for over 500,000 technology professionals worldwide. From their HQ in Cambridge UK, they create a number of great tools for MS SQL Server, .NET, and Oracle. The philosophy of Red Gate is to design highly usable, reliable tools that solve the problems of DBAs and developers.

Every year Red Gate selects a number of active and influential community members (such as popular blog writers and community site owners) as well as SQL and .NET MVPs who are experts in their respective fields, to be part of the Friends of Red Gate (FORG) program. I’m proud to announce that I’m part of the 2014 FORG selection. This post is a part of a series of post, in which I try to explain and show you why the tools of Red Gate are so loved by the community.



 
What SSMS misses
The tool that Microsoft provides you with when you install SQL Server is pretty nice. It’s nicely designed (even though I’ve heard other opinions), it’s stable, and it does what it should do: it allows you to administer your servers. But that’s not the only thing that it should do in my opinion. If you take a look at Visual Studio as an example, that studio contains more options that helps developers do their job. And remember, SQL Server Management Studio (SSMS) is actually a Visual Studio instance with a different layout (just check the Ssms.exe.config)…

So why doesn’t SSMS have a schema compare option, like Visual Studio has? Visual Studio is no longer the environment that is used only by developers that work with ASP.NET and C#, but it evolved to much more the last few years. It’s now the tool for working with Data Quality Services (DQS) and SQL Server Integration Services (SSIS). So let’s talk about some other features that SSMS misses in my opinion, and let’s see how SQL Prompt can fill that gap.

 
IntelliSense
SSMS ships with a default intelliSense, but this isn’t an implementation that I would like to see. It misses a few vital features. For example, the fact that SSMS IntelliSense doesn’t take relations between objects into account, is one of the biggest shortcomings. One of the companies that created a tool to fix that is Red Gate. If you install SQL Prompt, you get IntelliSense 2.0, or IntelliSense on steroids if you like.

When you installed SQL Prompt, it gives you suggestions when you write a JOIN clause. This means that it scans column names, and traces primary- and foreign key relationships on the tables you are joining. The join suggestion based on keys can be recognized by the little key symbol in front of it:

 
Object discovery
Whenever you’re working in a database, and you’re writing your queries, there comes a point that you can’t remember a column name or datatype. In SSMS you need to navigate the object explorer to the object (let’s say a table), and generate a create script, or click on the table to get to the column list. SQL Prompt allows you to hover your mouse over an object, and see some extra information:

 
If you click on the popup, you’ll get another popup window with the creation script (by default), or a summary of the object:

 
Scripting options
Whenever you need to script an object, or want to see the contents of for example a Stored Procedure, you need to navigate to the object in your object explorer. With SQL Prompt, you can also use the mouse context menu to script objects. Just right-click an object you referenced in your query, and choose the “Script Object as ALTER” option:

 
This will generate an alter script for the object you selected. This makes it a lot easier to see the contents of a Stored Procedure or View, and change it when needed.

 
Useful functions
The last feature I want to show you is the menu of SQL Prompt. This shows you another set of useful tools and functions. For example, how do you format your T-SQL query? SQL Prompt can do that for you with a few mouse clicks, or if you press the hotkey combination. Another great feature is the “Find Unused Variables and Parameters”. This saves you time when you try to find out which declared variables you don’t use anymore, in a very large query. All of these options can be found in the SQL Prompt menu:

 
If you want, you can also create a style-export for all your colleagues, so your entire department or company formats queries according to the same layout. You can find out more about this in the SQL Prompt menu, under Options -> Format -> Styles. You can export your formatting options as a .sqlpromptstyle file, or import one.

 
Is it worth it?
If you would ask me, my answer would be: yes! Even though it’ll cost you about €285,- (or $390,-), it’s definitely worth it. It saves you a lot of time, and it adds a lot of useful (and needed) features to SSMS.

If you want to try it out, just go to Red-Gate.com, or the product site for SQL Prompt. You can download a trial there that contains all features, for a limited time.

 
If you want to read more about this topic, don’t forget to check out these blog posts:

Julie Koesmarno: Clean And Tidy SQL With SQL Prompt
Mickey Stuewe: Becoming a SQL Prompt Power User
Chris Yates: SQL Prompt – The Power Within

SQL Search: The indispensable tool just got better

Since 1999, Red Gate Software has produced ingeniously simple and effective tools for over 500,000 technology professionals worldwide. From their HQ in Cambridge UK, they create a number of great tools for MS SQL Server, .NET, and Oracle. The philosophy of Red Gate is to design highly usable, reliable tools that solve the problems of DBAs and developers.

Every year Red Gate selects a number of active and influential community members (such as popular blog writers and community site owners) as well as SQL and .NET MVPs who are experts in their respective fields, to be part of the Friends of Red Gate (FORG) program. I’m proud to announce that I’m part of the 2014 FORG selection. This post is a part of a series of post, in which I try to explain and show you why the tools of Red Gate are so loved by the community.



 
It gets better
As you might have read in my earlier blog post, I love using Red Gate’s SQL Search. It saves me a lot of time querying system objects, or looking through source files stored on disk, when looking for a specific query or object. Even though it had a few shortcomings, the tool was really good. And now they’ve solved some of those shortcomings in a new release: SQL Search 2.0. In this short post, I want to quickly show you the changes that make it even more useful for me to use SQL Search.

 
Search on specific databases
In the previous version you could only search on 1 specific database, or on all databases:

In the new version, it’s more flexible. You can search on all databases or a selected few databases:

 
This means you’ll be more flexible when searching for specific queries and objects. For example, if you have 2 databases that use an object from a third database, you don’t need to search the whole instance, or execute the search twice on different databases. This can save you a lot of time and effort.

 
Search on specific objects
Searching for specific objects was the same as the database search box. In the old version, you could only search for all object types, or on one specific type:

 
In the new release, you can search on any combination of objects you can think of, or search on all objects:

 
Search results extended
In the new search results, you’ll also see that tables and functions are included. Before you couldn’t see the table definition in the search results, only the table name. Now, the results include the columns of the table. As an example, I’ve searched for tables containing “Employee” on the AdventureWorks2012 example database:

 
Wildcards
You can also use wildcards and boolean expressions in the search in this new version. It does seem to work on some occasions, but I’ve also seen some weird results when testing this new functionality. But as an example, let’s try out a boolean expression:

 
And looking at the results, it shows only objects that contain “Employee”, and not “Employees”:

 
Performance
Because I work with SQL Search a lot, I’ve also noticed that the performance and stability improved a lot. In the old version, I sometimes notices that the searches took a long time, especially searches with a lot of results on instances with a lot of objects and databases. This performance looks better now. But the fact that you can search on specific objects and databases also helps.

 
You don’t use it yet? Try it out!
Looking at my daily job, I’m almost certain that I can’t live without SQL Search anymore. It makes it easier to quickly find a reference to an object, or views en stored procedures that depend on specific objects. So if you don’t use it now, just try it out. It’s completely free for download at Red Gate.

 
If you want to read more about this topic, don’t forget to check out these blog posts:

Julie Koesmarno: SQL Server 2012 SSIS Project Versions
Mickey Stuewe: New and Improved SQL Search By Red Gate
Chris Yates: RedGate SQL Search Has What????