T-SQL Tuesday #66 – Monitoring

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Monitoring”. If you want to read the opening post, please click the image below to go to the party-starter: Cathrine Wilhelmsen (Blog | @cathrinew).



 
Monitoring the performance or health of your environment might be the single most important thing in your daily job. But this might be the most important question you need to answer, before you start working on any monitoring or process. You don’t want to find out that your database isn’t working anymore, because someone from the business starts calling you his application isn’t working anymore. You want to know it as soon as possible, so when the business calls you, you can tell them you’re already working on it, or maybe even solved it. But how do you do that?

 
What do you need to monitor?
There are many different things you can and want to monitor, that’s why it’s difficult to explain the different aspects of monitoring. You don’t only want to monitor SQL Server, but also the server it’s running on. If a disk fills up, SQL Server will eventually stop working. But do you want to find out that you have a problem when your SQL Server stops working? I don’t think so…

But even within SQL Server, there are so much things you can monitor, depending on your needs. A number of these things are explained by Kevin Kline (Blog | @kekline) in his SQLBits session: “An End-to-End Troubleshooting Checklist for SQL Server”. He talks about troubleshooting SQL Server, but he mentions a lot of stuff you can start monitoring today. So if this will ever cause problems in the future, you can always look back at the history of a specific setting or configuration. You don’t want to spend time on searching for a previous setting value, when your SQL server isn’t working anymore.

 
Different ways of monitoring
There are different ways of monitoring your environment. Some parts you want to monitor with an automatic monitoring solution, that raises errors when you need to take action. But there are also parts of your environment, that you might want to monitor “manually”. Let me explain that.

 
SCOM
One of the corporate monitoring solutions you could use is System Center (SCOM). This gives you the oppertunity to monitor your whole environment from top to bottom: from server to specific disk. The downside is that it might be pretty expensive when you compare it to your other options.

And SCOM might be to overwhelming for your environment or process. Dealing with SCOM means you need a certain level of knowledge of your environment, and you need to know how to configure SCOM. This learning curve might be too much, when you just want to monitor a specific part of your environment or server.

 
SQL Sentry

SQL Sentry has proven itself as a tool provider with tools like SQL Sentry Plan Explorer. Personally, I use this tool every time I need to do some performance tuning. We’ve blogged about this tool with the SQLCoop team. It’s also featured on the SQL Sentry Plan Explorer page (which I’m very proud of!).

But besides that awesome tool, SQL Sentry is a company that built a number of monitoring solutions for your SQL Server. These can be found on their website. These are created specifically for monitoring SQL Server, and is a better choice compared to SCOM, if you just want to monitor SQL Server. You can use this if you want to monitor your whole SQL Server environment, with a tool build specifically for SQL Server.

 
Manual monitoring
Let’s say you have a production SQL Server instance. On this instance, you run a number of SQL Server Agent jobs. These jobs run on a specific schedule, and you want to monitor the outcome of those jobs. But perhaps you don’t need to take action when a specific job fails. If this is the case, using a big solution like SCOM for example, might be a bit too much.

Having a manual tool to check on job outcome and run time might be a better solution for you. If you want to do that, the SQL Server Agent job history window in SQL Server Management Studio (SSMS) might be the right tool for you. There’s no need for big tools, if your monitoring needs to be as basic as that.

Another tool you could use is SQLjobvis. I’ve mentioned this tool before, and I still use it almost on a daily basis. This can be used to monitor your environment “manually”.

A failed job can also send out a notfication by mail. This can be configured in the SQL Server Agent. This can be used to monitor your job activity as well.

 
Creating your own monitoring solution?
In the years I’ve worked in IT, I’ve seen a lot of companies and different monitoring systems. And there’s always that one company, that want to create its own SCOM implementation. They think the rest of the world is crazy, and that they can build a system better that’s better than everything else. Let me tell you, all custom build SCOM systems I’ve seen so far never lasted longer than a year. After 12 months, they use either another system on the side to fill up the gaps the custom SCOM system has left, or the custom SCOM system is gone entirely.

Maybe you can build a custom monitoring system for your business-specific processes (like checking the outcome of a webservice call), that could work. But when you’re building your own system to check the free space of a server, sorry to burst your bubble, you’re doing it wrong! Do you really think that you can build a custom system that can beat another monitoring solution that has evolved in 10+ years, and that has been tested in thousands of companies? I need to come across a system like that yet…

 
Conclusion
Looking at the different ways to monitor your environment, and the tools you can use for that, we can only conclude you have a world full of options. But in the end, you just fall back to 1 basic rule: if you monitor your environment, you need to act upon it as well. If a SQL Server Agent job fails, don’t just notice it and carry on your job, but act on it! Fix the job, or fix the underlying issue.

It’s basically the same as your backup process: when you’re taking backups of your database, and you never try to see if you can restore it, just stop taking backups! You can test, monitor and backup your entire environment, but if you don’t act on any issues, it’s all a waste of time. And I’ve seen that happening just too often…

On which port is SQL Server running?

Earlier this week, a good friend asked me the simple question: “How can I check on which port my SQL Server is currently running?” There are multiple ways, so let’s take a look at the ways you can find out the answer!

 
SQL Server Error Log
It’s a misconception that only error messages are logged in the SQL Server error log. There are also informational messages logged, which you can use to find the port on which SQL Server is currently running:

xp_readerrorlog 0, 1, N'Server is listening on'

 
The result:

 
SQL Server Configuration Manager
The SQL Server configuation manager is a tool which is installed alongside SQL Server. In this tool, you can change for example the TCP/IP settings of your SQL Server. It also shows you the current port on which SQL Server is running:

 
DMV
You can also query the system DMV’s:

SELECT DISTINCT 
    local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL

or

SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID

 
Command prompt
Finding the used port via a command prompt requires some more information. First, you need to find the process ID (PID) that SQL Server is using. The quickest way is to use the Task Manager in Windows. Once you have the PID, you can run the commmand below:

“netstat -ano | findstr [PID]”

The result:

 
Registry
The port number can also be found in the registry. Just remember, that the folder you see in the screenshot below contains my instance name (SQL2014DEV). Change this to your instance name!

 
You can also use the “master.dbo.xp_regread” stored procedures to read this registry key from T-SQL:

DECLARE @TCPPort NVARCHAR(5),
        @RegKeyName VARCHAR(8000);

SET @RegKeyName = CONCAT('Software\Microsoft\Microsoft SQL Server\',
                         @@SERVICENAME,
                         '\MSSQLServer\SuperSocketNetLib\TCP')

EXEC xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKeyName,
  @value_name = 'TcpPort',
  @value = @TCPPort OUTPUT;

SELECT @TCPPort;

 
Event Viewer
SQL Server also logs the port in the Windows Event Viewer. If you open the application log, and you filter on eventid 26022, you’ll see the port that is in use:

 
PowerShell
After I posted this blog, Johan Bijnens (@alzdba) sent me a message: I forgot the PowerShell option!

#tcpport.ps1
#Evaluates the SQL Server instances on a Windows server and returns the TCP port number used by each instance
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | Out-Null
$m = New-Object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') 'HOME'
$m.ServerInstances | ForEach-Object { $m.Name + '\' + $_.Name + ', ' +
       $m.ServerInstances[$_.Name].ServerProtocols['Tcp'].IPAddresses['IP1'].IPAddress.IPAddressToString + ':' +
       $m.ServerInstances[$_.Name].ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value
       }

 
Thanks Johan for reminding me on this! The full code can be found on MSDN.

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!

Speeding up your backups

Last week, I watched the SQLRally session of Pieter Vanhove (Blog | @Pieter_Vanhove), where he talked about Advanced Backup and Restore. He mentioned striped backups (taking a backup of a database, divided over a number of files). After watching the video, I started to wonder what made the biggest difference: the number of files, the number of disks, compression.

 
Test setup
In order to test this, I restored a copy of the AdventureWorks2012 database, that you can download from msftdbprodsamples.codeplex.com. But because this database is only 200MB, taking a backup would only take a second. In order to make this a bit more interesting, I used a script Adam Machanic (Blog | @AdamMachanic) wrote. This script creates 2 new objects in the AdventureWorks database (dbo.bigProduct and dbo.bigTransactionHistory), which results in a database of 2.8GB. You can download the script here.

 
What matters most?
In order to test the differences in performance, I’ve tested multiple combinations:

– Multiple files on single disk
– Multiple files on 2 disks
– Both options above, with and without backup compressions

After running all the different tests, I’ve added the results to an Excel sheet:

 
The results you see are in milliseconds. The analysis on these numbers is a bit difficult, so let’s put these numbers in a graph:

 

 
As you can see, the number of files (when looking at a minimum of 2 files) isn’t the biggest difference. The number of disks, and compression vs no compression make the biggest difference. In my case, I tested it on 2 SSD’s, but you may have more disks in your server. Or better yet, you are able to take backups on multiple LUN’s on your storage, and the LUN’s use multiple disks. The more spindles you can use, the faster your backup will become.

But there’s also a downside to this. When you want to restore a backup, you need all the files to do that. So when you restore a normal backup, you only need 1 file. If you’re going to stripe your backup over 16 files for example, you need all 16 files to perform a restore. So basically, the chance of having a corrupt backup file is 16x as high, compared to a normal (single file) backup. For every advantage, there’s always a disadvantage…

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

Julie Koesmarno: On sabbatical
Mickey Stuewe: Transaction Log Backups for the Accidental DBA
Chris Yates: Backups – They Are Needed, Who Knew?

Find SQL Server Agent job history retention with T-SQL

In SQL Server, the Job Agent has its own retention that you can set. This retention determines how much execution history of SQL Server Agent jobs is being kept. But if you have a lot of instances across your domain, or if you reinstall a new server, how do you determine if this value is set correctly?

 
Doing it old-school: via the GUI
You can check the SQL Server agent retention via the GUI, by right-clicking on the SQL Server Agent, and clicking on properties. In the window that popped-up, click on History, and you’ll see something like this:

 
T-SQL / Registry
The way I wanted to check it is with T-SQL. Because I want to run the statement against a 40+ instances, I use a multi-server query for that. The reason I do that, is that this returns all the results in a single table, which makes reviewing and comparing the results a lot easier.

But there doesn’t seem to be a way to query the set values. But with a little bit of searching, I found out that you use the stored procedure below to change one of the values:

EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = 999

 
So by dissecting this procedure, I found out SQL Server just reads the value from a registry key. I took out the piece that I need to read the retention values:

DECLARE @jobhistory_max_rows INT = NULL,
        @jobhistory_max_rows_per_job INT = NULL


EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRows',
                                        @jobhistory_max_rows OUTPUT,
                                        N'no_output'
SELECT @jobhistory_max_rows = ISNULL(@jobhistory_max_rows, -1)


EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRowsPerJob',
                                        @jobhistory_max_rows_per_job OUTPUT,
                                        N'no_output'


SELECT @jobhistory_max_rows, @jobhistory_max_rows_per_job

 
But when I wanted to look up the values in the registry, to see if it returned the correct results, I couldn’t find the actual keys. I thought I was losing my mind, but there’s a catch…

 
xp_instance_regread vs xp_regread
There are 2 stored procedures you can use to read registry keys from your SQL Server:

DECLARE @jobhistory_max_rows INT = NULL

EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRows',
                                        @jobhistory_max_rows OUTPUT,
                                        N'no_output'

SELECT @jobhistory_max_rows

 
or

DECLARE @jobhistory_max_rows INT = NULL

EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRows',
                                        @jobhistory_max_rows OUTPUT,
                                        N'no_output'

SELECT @jobhistory_max_rows

 
The difference between the two is perfectly explained in this Stack Overflow thread:

xp_regread reads the literal registry path that you specify. xp_instance_regread “converts” the path you specify so that it matches the instance of SQL Server that you’re currently using.

 
So if you run multiple instances on your machine, you want to use the instance version of the stored procedure. If you’re just running 1 (default) instance, you can use both versions.

So eventually, I searched for the instance folder, and I did find the registry keys:

 
Conclusion
Especially for very large environments, it’s a good idea to write system- and/or server-checks in T-SQL. That way, you can script them in SCOM (or another monitoring tool), or if you use SQL Server Policy Based Management (and I would really like to recommend this Pluralsight course from a very good friend of mine: Boris Hristov (Blog | @BorisHristov)!), or just a multi-server query.

Creating a free SQL Server test environment in 15 minutes

In order to test new SQL Server tools or other applications, I use a small test environment on my laptop. I wanted to share how I normally (and pretty quickly) set up a test environment.

 
Installing Hyper-V
Right now, I rely on Hyper-V to host my virtual machines. This is build-in in Windows, and easy to use. Before I could use that, I enabled and installed Hyper-V on my Windows 8 machine.

 
Adding network connectivity for your VM’s
When you enabled Hyper-V, and before you create your first VM, you want to add a virtual network card to your VM. You can do that by clicking on “Virtual Switch Manager” on the right of the Hyper-V Manager. In the window that pops up, you choose “External”, and click on “Create Virtual Switch”. Because I work on a laptop, I can choose between 2 adapters: UTP and WiFi. But because I want my VM to work within the company network, I choose the external network adapter (personal preference).

 
Create a VM and install Windows Server 2012
Now that you have a working Hyper-V setup, you can download Windows Server 2012 Evaluation. Just create a new VM in the Hyper-V Manager, go through the options (configure number of CPU’s, memory, etc.), and double click on the VM you created. In the menu bar, click on “Media”, and mount the Windows ISO in the DVD Drive menu. Start the VM, and run through the Windows setup.

Approximate time needed: 6 minutes

 
Enable .NET Framework 3.5
For some reason, when enabling or installing .NET Framework 3.5, Windows Server 2012 throws an error. I haven’t been able to figure out why this fails, but you can’t manage without it because it’s needed to install SQL Server.
After the installation of Windows Server 2012, you can run the command below in either command prompt or PowerShell (Change X: to the driveletter with the Windows installation files):

Dism /online /enable-feature /featurename:NetFx3 /All /Source:X:\sources\sxs /LimitAccess

Approximate time needed: < 1 minute

 
Changing the machine name
To make it easier for yourself to work with SQL Server (and possible connections to and from other machines on the network) later on, you should change the machine name. If you don’t do that, you need to connect to “WIN-ABCD1E2F3GH\InstanceName” with SSMS. A shorter machine name is more useful and meaningful. This prevents mix-ups of multiple VM’s, or really long connectionstrings in application config files.

You can do that by pressing Windows Key + X, click on “System”, “Change Settings”, “Change”, and change the “Computer name” value. When you change that, you need to restart your VM.

Approximate time needed: < 1 minute

 
Installing SQL Server
Now that you have a working environment, we can start with the actual important bit: installing SQL Server. For my test environment, I use SQL Server 2014. You can just download the SQL Server 2014 Evaluation Edition from the Microsoft website. Personally, I just install the engine and the tools (SSMS), but that depends on the reason I create that machine.

Approximate time needed: 6 minutes

 
Was that fast or what?!
As a result, you have a fully working test environment that lasts 180 days (the duration of the evaluation editions), without the cost of licenses. And the more you need a test VM, the faster you can do it. Personally, I store both ISO files in a directory on my laptop. If I need a test environment, it takes me about 10 minutes to install a brand new VM (depending on disk performance). You just need to create a new VM, install the operating system, install SQL Server, and it’s ready to go!

Enjoy your testing or learning!

How to determine SQL Server uptime?

Determining the SQL Server uptime can be difficult. Because SQL Server is a Windows service that can be stopped and started without restarting the OS, the uptime of your SQL Server can be completely different compared to your server uptime. So how do you determine both uptimes from within SQL Server?

 
tempdb
One of the ways to determine the last restart of SQL Server, is by looking at the tempdb. Because the tempdb is recreated on SQL Server startup, you could get an indication of the uptime of your SQL Server, by querying the creation date:

SELECT create_date AS START_TIME_INSTANCE FROM sys.databases WHERE name = 'tempdb'

 
SQL Server error log
In the SQL Server error log, the startup time is stored on a regular basis, together with a process ID. This information can be retrieved in 2 ways. You can look for either the process id event:

DECLARE @XREL TABLE
    (LogDate DATETIME,
     ProcessInfo VARCHAR(100),
     Text VARCHAR(MAX))

/* Insert current log */
INSERT INTO @XREL
EXEC xp_readerrorlog
 
/* Insert previous log */
INSERT INTO @XREL
EXEC xp_readerrorlog 1
 
SELECT TOP 1 *
FROM @XREL AS X
WHERE X.TEXT LIKE '%Server process ID is%'
ORDER BY LogDate DESC

 
or look for the informational message regarding the process ID:

DECLARE @XREL TABLE
    (LogDate DATETIME,
     ProcessInfo VARCHAR(100),
     Text VARCHAR(MAX))

/* Insert current log */
INSERT INTO @XREL
EXEC xp_readerrorlog
 
/* Insert previous log */
INSERT INTO @XREL
EXEC xp_readerrorlog 1
 
SELECT TOP 1 *
FROM @XREL AS X
WHERE X.Text LIKE '%This instance of SQL Server has been using a process %'
ORDER BY LogDate DESC

 
In the first query, you can look at the LogDate. In the second query, you need to extract the datetime from the Text column.

 
sysprocesses
Another way to find out the startup time, is by looking at the view sys.sysprocesses. This contains information about running processes. And when you look at SPID 1 (system process), you’ll find the startup time of SQL Server:

SELECT
  login_time AS START_TIME_INSTANCE
FROM sys.sysprocesses
WHERE spid = 1

 
sys.dm_os_sys_info
The last possibility for SQL Server uptime I want to share is querying the sys.dm_os_sys_info view. Looking at MSDN, this view contains “a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.” Also, the instance startup time:

SELECT sqlserver_start_time AS START_TIME_INSTANCE
FROM sys.dm_os_sys_info

 
Server startup
Not only SQL Server uptime can be important, but also the server uptime (the hardware on which SQL Server runs). But if you run Windows 8 or Windows Server 2012, this isn’t always accurate. But you can retrieve the accurate with T-SQL:

SELECT
  DATEADD(MILLISECOND, (sample_ms * -1), GETDATE()) AS BOOT_TIME_MACHINE
FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'), 2)

 
Dashboard Report
Another way to retrieve the server startup time (without T-SQL, and without using the event viewer in the OS or other tools), is to use the SQL Server Dashboard Report. You can view this report, by right-clicking on the servers name in SQL Server Management Studio (SSMS), and select Reports -> Standard Reports -> Server Dashboard. If you look at the report, in the left table you’ll see the Server Startup time.

Follow

Get every new post delivered to your Inbox.

Join 70 other followers