If you have a SQL Server failover clustered instance, often you'll want to know which server is acting as the active node. This is easy since you can just check in Windows Cluster Failover Manager which node is the current owner. But what if you have multiple clusters and you want to check them all?… Continue reading Quick Way to Check Which is the Active Node in a Failover Clustered Instance
Tag: SQL Server
Recovering a Database in Restoring State Leaves Broker Disabled
Earlier this week I came across an interesting situation where a tail log backup of some databases were taken before a migration - meaning a final transaction log backup was taken and the database was set into RESTORING state. The interesting part came where we recovered the same database on the same server (not restored… Continue reading Recovering a Database in Restoring State Leaves Broker Disabled
How to Silently Install SQL Server Management Studio
If you find that you're installing SQL Server Management Studio (SSMS) often or on many servers, you might find that installing it silently, a.k.a. unattended, is more suitable than installing it through the usual method using the install wizard. First, you can download the latest SSMS installer from Microsoft's SSMS download page, or from this… Continue reading How to Silently Install SQL Server Management Studio
Performance Testing a New SQL Server
When you build and configure a new SQL Server, one of the many things you should be doing is testing how it performs before it goes live. If you don't do that, then you probably should have a hard think about doing so. It's important the performance of the new server is tested to ensure… Continue reading Performance Testing a New SQL Server
Configure Database Mail and Test it – All using T-SQL
When configuring a new SQL Server instance, you probably want to configure Database Mail. As with all your post install configuration changes, it's better to do it using a script to save yourself a whole heap of time while ensuring all your SQL Server instances are standardised and consistent. Unfortunately it's not as easy to… Continue reading Configure Database Mail and Test it – All using T-SQL
How to Move TempDB to a different Drive or Folder
Sometimes you'll need to move the TempDB files to a different drive or folder. It's a pretty simple operation and I'll provide a script for you to use. Typically to move the TempDB files, you would do it in 3 stages: Set the new location in SQL Server, by running a command in SSMSRestart SQL… Continue reading How to Move TempDB to a different Drive or Folder
Detaching then Attaching a Database Loses the Database Settings
In my last post about moving database files, I touched on the point when you detach and then attach a database, you lose some of the database settings. This is a real gotcha that many people don't realise...until they've done it and wonder why things become broken. The database settings that get changed when you… Continue reading Detaching then Attaching a Database Loses the Database Settings
Moving Database Files, It Ain’t That Hard
From time to time, the database data files (like *.mdf and *.ndf), or transaction log files (like *.ldf) need to be moved or renamed, in some way or another. Sometimes, to a different drive or volume. Sometimes, to a different folder on the same drive. Sometimes, the files need to be renamed too. For some… Continue reading Moving Database Files, It Ain’t That Hard
Renaming and disabling the sa login
In theory, nobody should be using the sa login for anything. In reality however, I've seen many applications and processes configured to use it. Trying to change the way these applications and processes are setup, or convince people to change them, is a challenge to say the least... a challenge which I wont try to… Continue reading Renaming and disabling the sa login
Login Default Languages and the Effects of Getting it Wrong
There have been countless times where I've seen dates in SQL Server being interpreted differently to what people have expected. Sometimes the date is interpreted in the format dd-mm-yyyy whereas other times the date format is interpreted in the mm-dd-yyyy format. This can greatly change the results of queries or change the date that was… Continue reading Login Default Languages and the Effects of Getting it Wrong
