Search This Blog

Tuesday, 31 January 2017

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

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

SELECT*

FROMsys.configurations

-->

wherenamelike'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

Usemaster

GO

sp_configure'remote admin connections', 1

GO

RECONFIGURE

-->

GO

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

Thursday, 26 January 2017

By David PostlethwaiteI 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.

Sunday, 22 January 2017

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.

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

SELECTCOUNT(*)FROM #t1

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

SELECTCOUNT(*)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

SELECTCOUNT(*)

FROM #t1

UNION

SELECTCOUNT(*)

FROM #t2

UNION

SELECTCOUNT(*)

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.

SELECTCOUNT(*)

FROM #t1

UNIONALL

SELECTCOUNT(*)

FROM #t2

UNIONALL

SELECTCOUNT(*)

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

(SELECTCOUNT(*)as rn

FROM #t1

UNIONALL

SELECTCOUNT(*)

FROM #t2

UNIONALL

SELECTCOUNT(*)

FROM #t3)

SELECTSUM(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:

Monday, 9 January 2017

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

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

DROPTABLEIFEXISTS [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:

CREATEORALTERPROCEDURE dbo.sp_MyProc as

BEGIN

PRINT'This is a new Procedure';

END

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.