I’ve been involved in building a solution on top of SQL Server peer to peer replication (SQL 2014 SP1) and slowly building up a script library for general DBA tasks within that solution. One common task is to add a new table/article to the publication.
The basic steps for adding… Read more
After a data centre power outage a decision was made to switch the start up mode of a bunch of our SQL Servers to manual so that the on call DBA could manage the reintroduction of the servers in a controlled fashion.
I jumped onto the first of our Windows… Read more
A colleague of mine discovered an interesting behaviour change from SQL Server 2012 to SQL Server 2014. We recently upgraded one of our servers from SQL 2012 to SQL 2014 and a job started failing. (Although I haven’t explicitly tested this behaviour on earlier versions – the job dates back… Read more
When I first began this blog, back at the beginning of my DBA career, it was purely a place for me to keep queries, things I was learning, processes and handy links. This is one of those posts, there’s nothing new or ground-breaking here – just an elegant simple solution… Read more
The page came at a reasonable hour. All of the overnight ETL and processing jobs were just winding up before the business started in earnest for the day. One of the final jobs of the morning failed, a job that called an SSIS package that transferred some email logging to… Read more
I recently came cross another great reason to enable the remote admin connections server configuration on SQL server.
The DAC is great tool… Read more
What is it about 4:45 pm on a Friday afternoon that makes CPUs spike, drives crash, databases corrupt? I wish I knew but I don’t. I do know that I have been on the receiving end a few times. Just shutting down the last applications when a call or a… Read more
The Situation: I was recently involved in the migration of a group of core databases from three SQL Server 2005 instances to three SQL Server 2012 instances. There were quite a few moving parts that needed to be considered as part of the upgrade including replication, CLR, service broker and… Read more
Once upon a migration I would use sp_help_revlogin to migrate logins and remap the SQL server logins using sp_change_users_login. During some recent migration work I realised that SQL Server Management Studio could do most of the work of sp_help_revlogin. I still needed to use sp_change_users_login – but I came… Read more
I’ve been doing some work on a credit card payment system lately. Obviously this needs to be robust and consistent so I’ve been working on error handling and “transactionalising” certain parts of the process.
I needed to answer a couple of questions that I had along the way. What happens… Read more
If you’re like me, you might be thinking “I want some more things to think about when I migrate between SQL Servers.” … No? Okay, fair enough. There’s a whole list of things to think about when migrating from one SQL instance to another including:
- Linked Servers.
We all have blind spots in our knowledge. We don’t know they are there and when we are thinking through an issue our mind quickly rejects some plans because of these blind spots. This happened to me recently when I was tasked with doing a security audit of a SQL… Read more
I’d be struggling to remember a week where I hadn’t run a query against the dmv sys.dm_db_index_usage_stats to find unused or under used indexes (or even heaps). It would have to be in the top shelf of many DBA’s toolboxes. So I was alarmed when I came across this post Read more
Problem: You require the ability to perform a repeatable standardised installation of multiple instances of SQL Server or the ability to create as-built documentation of an installation.
Solution: I’ve made a few attempts at documenting a SQL Server install, and all the various options that were chosen at each step,… Read more
The Problem: A legacy table contains amounts and a char column indicating whether the amount is a credit or a debit. We want an aggregate (over a given range) of the amounts. We need to add the credits and subtract the debits.
The Solution(s): Two possible approaches to the problem… Read more
It’s a well known fact that any sitcom that runs for a certain length of time will release a clip show. Similarly any blog about SQL Server will end up doing a top tips for using SQL Server Management Studio. Here then, in no particular order, are mine:
1.… Read more
A while ago I blogged my about my approach to service packing a SQL Server 2008R2 active active cluster. Time to apply SP1 to SQL 2012 on top of a Windows 2012 failover cluster. The basic approach is similar but there are a couple of subtle differences.
The key… Read more
I struck this error in one of my SSIS packages, I found lots of tips and suggestions online for the error but none of the solutions worked for me. So I want to add my solution to the mix so that it might give the next person searching an extra… Read more
Standard best practise is to have auto create and auto update statistics set for SQL Server databases. But there is no corresponding setting to remove statistics that are no longer necessary. Unless explicitly managed by the DBA these statistics could be causing excess resource usage.
Create a… Read more
I came across an issue today while trying to create a WMI alert for a mirroring state change. The error was something along the lines of WMI Access denied. Error: 0×80041003.
My first port of call was to verify the WMI query by using the WBEMTest utility.
This is a… Read more