Tuesday, 31 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 SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services.  The package execution failed.  The step failed.

When you install the latest service pack for SQL Server 2008 – which is service pack 4. Integration services is no longer needed.

Why is he talking about this old version of SQL Server I hear you ask? I have a few reasons. I’m sure I’m not the only one working on legacy versions of SQL Server. This information may come in handy for others. It will also help me remember this stuff going forward, which I might need to know. It also brings up another interesting, relatively recent development for Microsoft. At the tail end of last year Microsoft announced SQL Server Premium Assurance – which will basically allow you run older version of their software for longer. I’ll be discussing this is an future newsletter that I write over on the Learning Tree blog. You can sign up for that here.

Monday, 30 January 2017

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 options.

You can check is the ‘remote admin connections’ is enabled by running the following query

FROM sys.configurations

where name like 'remote admin connections'

A value_in_use of 0 indicates local connections. A value of 1 means that remote connections are enabled

You can change the option if you need by using SP_CONFIGURE and the code below

Use master
sp_configure 'remote admin connections', 1


Enabling the DAC can be a lifesaver if you get a rogue query chewing up all the resources on a server and it can be a good idea to enable it. I wrote a post over 6 years ago saying to connect to the DAC you can see the link to that below

Related Posts
Connecting using the DAC - http://www.gethynellis.com/2010/09/sql-server-dedicated-administrator.html

Thursday, 26 January 2017

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.

It got me thinking about how we go about keeping our knowledge up to date.
When we start our IT career we are often sent on courses with a training company like Learning Tree. These companies provide great  technology training including updating your knowledge on SQL Server 2016.
For many who have been in the industry for a while, training courses can't always provide very specific, in-depth training in very specific part of the chosen technology within which we work. These specific areas might not need a multi-day course or even a full day course. Maybe just an hour or so of material.
This is where conferences like SQL Saturday, SQL Bits and SQL Relay come into play. They are all-day events, generally free to attend and you will have the chance to hear about all sorts of areas of SQL Server from people who have first-hand experience and are prepared to volunteer their time.There are hour long presentations on a variety of topics which you can pick and choose as you like. You have a chance to network with other attendees and the speakers and there will be companies there, sponsors of the event, even though its free to attend the event doesn't happen at zero cost, who may have products tools and services  that might just be what you are looking for. And some great prizes to be won.
SQL Saturday Vienna

SQL Saturday Rhineland

If you can’t get time to attend in person there are also online conferences such as “24 Hours of Pass” and virtual chapters that you can attend
There are also SQL user groups all over the country and all over the world where you can meet like-minded SQL professionals and hear presentations from local and national speakers on a variety of subjects, and often some free pizza.
Once you have been working in SQL for a few years you will have learnt something that could be interest to others. Every speaker has to start somewhere and no one will put you down for trying. I was persuaded by Gethyn to give it a go and have now spoken nearly twenty times at conferences and user groups, not just in the UK but over Europe as well. The buzz of seeing people learning from you is brilliant, you get to meet great people and even as a speaker you learn when someone asks a question that you’d never thought of.

So you can see that if your company is cutting back on their training budget you don’t need to despair, there are plenty of opportunities available to grow as a SQL professional. You can do what I do and combine a holiday in Europe with a SQL Saturday, I have visited many countries that I would never have been to if it wasn’t for SQL Saturday.
For more information:

Sunday, 22 January 2017

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

CREATE TABLE #t1 ([c1] [nchar](10) NULL) ON [PRIMARY]

CREATE TABLE #t2 ([c1] [nchar](10) NULL) ON [PRIMARY]

CREATE TABLE #t3 ([c1] [nchar](10) NULL) ON [PRIMARY]

/*Insert Some data to the tables*/


SET @i = 1

WHILE @i & lt;= 13
 INSERT INTO #t1 (c1)
 VALUES ('x')

 SET @i = @i + 1


SET @x = 1

WHILE @x & lt;= 12
 INSERT INTO #t2 (c1)
 VALUES ('x')

 INSERT INTO #t3 (c1)
 VALUES ('x')

 SET @x = @x + 1

The questions seems like a simple one. We can use the aggregate functions in SQL to help do the maths in terms of counting the rows However the solution needs to two steps.

Like all things in SQL there is more than one way to achieve the same outcome this is the solution I came up and I’ve broken it down into separate parts.

Using the COUNT Aggregate function we can quickly  count the rows in one table. The first query counts all the rows in table t1

FROM #t1

So we have a starting point. Next we need count all the rows in the second table

FROM #t2

Will give you a count of all the rows in table2.

We need to repeat this for all the tables that we want to include in the final total row number.

When we have counted rows in all the necessary tables individually  we then need to combine the individual results into one result set.

We can do this with a UNION ALL. This will combine the counts for each table into a single result set.

The UNION ALL is important. A UNION does an implicit distinct so any tables with the same number of rows will result in the duplicates being removed. Compare the two results below

FROM #t1
FROM #t2

FROM #t3

Tables t2 and t3 each have 12 rows each. If we build our table row count table with a UNION it will do implicit distinct and remove duplicates. therefore we have two rows in the result set that have the value 12 so only one is included with the UNION. Therefore our next calculation of adding all the counts together will be wrong

UNION ALL does not do the implicit distinct duplicates remain in the final result. Which is what we want in this case.

FROM #t1
FROM #t2

FROM #t3

So we have a result that is giving us the count of the rows in each table. Now we need to add the row totals together. We can do that by putting our UNION ALL query into a derived table or common table expression (CTE). An inline view if you prefer. The CTE or derived table will allow us to include the result set of our UNION ALL statement in the FROM clause and the SELECT from it to sum the total rows. 

In this example I will use a CTE

;with cterc as
(SELECT COUNT(*) as rn
FROM #t1
FROM #t2
 FROM #t3)

 SELECT SUM(rn) as totalrowNo

from cterc

I use in SUM in the outer query to add the number of rows of each table in the final result set. Giving us 37 rows across the 3 tables t1, t2 and t3 

If you are looking to do this yourself. You need to do the following:

  • Use SELECT COUNT (*) on each table to have its rowed total
  • Use UNION ALL to build a result of the row count of each table
  • Wrap that result set in CTE or derived table
  • Select from the CTE or derived table SUMing the row count column

You can learn all about querying SQL Server on Learning Tree’s 534 Writing SQL Queries for Microsoft SQL Server course. If you would like attend the event running in London starting Feburary 7th either in class or online you can book a seat at a special price of £750 plus VAT. Email training@gethynellis.com for more details or to book your seat.

Monday, 9 January 2017

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:

SQL Server 2016 SP1 previous Enterprise-Only Features
SQL Server 2016 SP1 previous Enterprise-Only Features

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 complicated query to check if an object exists before dropping it you can now simply say

DROP TABLE IF EXISTS [Person].[CountryRegion];

As a result of this there was a campaign to add a function that Oracle has had for a long time namely “CREATE or ALTER”. And in SP1 we now have it. You can now write:

PRINT 'This is a new Procedure';

These two commands can be used against most objects in SQL Server  2016. For those of you that write installation scripts for your applications these two new commands should make your code much easier to manage. 

Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...