Skip to main content


Showing posts from January, 2017

SQL Server 2008 RTM - Maintenance Plans need Integration Services installed

The tail end of last week was a bit of a throwback week for me. For reasons that I’ll not go into here I have been building some SQL Server 2008 instances. This is because of a large SQL Server migration project that I’m working on for a client. The 2008 instances are for legacy database not supported on later version of SQL Server.
There is nothing fancy in terms of database housekeeping and backups. Native SQL Server tools and maintenance plans are being used for automated tasks from backups to index maintenance.
What I did learn for at least the second time in my life, I think I knew this previously but have long forgotten is you need Integration installed on SQL Server 2008 (prior to SP2 or SP1 plus cumulative update) for a maintenance plan to run. If you try and run a maintenance plan without integration services installed you will get an error similar to the one below
The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQ…

Enabling Remote Access to the Dedicated Admin Connection

Recently I was running a health check on one of my clients SQL Servers and I noticed that access to the dedicated admin account was disabled. They would not be able to connect to the Dedicated Admin Account (DAC) remotely.  It is generally good practice to enable remote admin connections. If you have a server that is under so much load that all the resources are used up. SQL Server always allows saves enough resources for one single session to connect. You can’t use object explorer so you will be limited to one Query Window or connect through SQLCMD.  Enabling this would allow my clients in-house DBAs to connect to the DAC when you are not physically connected to the SQL Server in question should the need arise.

This is a pretty useful feature and whilst you can use a local DAC connection by default. That is, when you are connected locally to the server in question, you can connect using the DAC. If you want to connect remotely you need enable the ‘remote admin connections’ server opt…

SQL Server Community Events

By David Postlethwaite

I have just come back from a successful SQL Saturday in Vienna, Austria where I had 60 people listening to my presentation on SQL 2016 new features.
It was interesting how many people still wanted to know about SQL Server 2016 even though it’s been out for over six months.

How to Count the Total Number of Rows Across Multiple Tables

I have been working on SQL Server Consulting assignment,  a SQL Server 2014 migration for a new client over the past few weeks. When I’m undertaking such tasks I’m often asked additional questions that make my  day a little interesting. 

I got a question last week How to Count the Total  Number of Rows Across Multiple Tables. 
Lets look at a simple example
T1 has 13 rows T2 has 12 rows T3 has 12 rows
The scripts to create these tables and temporary tables in temp db is

CREATETABLE #t1 ([c1] [nchar](10)NULL)ON [PRIMARY] CREATETABLE #t2 ([c1] [nchar](10)NULL)ON [PRIMARY] CREATETABLE #t3 ([c1] [nchar](10)NULL)ON [PRIMARY]
/*Insert Some data to the tables*/
DECLARE @i INTSET @i = 1 WHILE @i & lt;= 13 BEGININSERT INTO #t1 (c1)VALUES('x')SET @i = @i + 1 ENDDECLARE @x INTSET @x = 1 WHILE @x & lt;= 12 BEGININSERT INTO #t2 (c1)VALUES('x')INSERT INTO #t3 (c1)VALUES('x')SET @x = @x + 1 END
The questions seems like a simple one. We can use the aggregate functions in SQL…

SQL Server 2016 SP 1

By David Postlethwaite

SQL Server 2016 SP1 was released in November 2016 only five months after the initial release.

There have been some significant changes and additions in this release. The most significant of which is that many features that once only existed in Enterprise edition, such as Always Encrypted, partitioning, in memory, Columnstore and compression are now available in lower editions as well.

The image below shows a list of previous enterprise edition features that are now available in other editions:

The one feature that remains Enterprise only is Transparent Data Encryption (TDE) which is a shame given how important security is becoming.
So the main difference between Enterprise and Standard edition is now over the amount of memory and CPU they can access. With so many new features now available in standard edition there is even less reason not to move to SQL 2016. 
One new T-SQL command that was introduced in SQL 2016 was “DROP IF EXISTS”. So rather than writing a co…