Upgrading to SQL Server 2014 – Campus Days talk (slides in Danish)

Below you will find slides, demos, and references for my Campus Days talk on “Upgrading to SQL Server 2014”. The slides are in Danish as it was a conference in Denmark.

Slides

Demos

Demos are at github.

Further reading / references

Upgrading to SQL Server 2014 – Campus Days talk (slides in Danish)

Integration Services (SSIS) for the DBA

Below you will find slides, demos, and references for my PASS Summit 2014 talk on “Integration Services (SSIS) for the DBA”. 

Slides 

Demos

Demos are at github.

Further reading / references

Integration Services (SSIS) for the DBA

Bulk load: methods for better data warehouse load performance

Slides and demo scripts are now available for my talk at SQLSaturday #337 Oregon today.

Abstract

When loading a data warehouse you want the data inserted into the tables as fast as possible. You know you have to use bulk loading, but what do you need to do to ensure a minimal footprint on the transaction log? In this session, you will learn about minimal logged operations. You will also learn about the different methods for bulk loading data into your data warehouse; using SSIS, BCP and T-SQL.

Slides

Demos

Demos are at github.

Bulk load: methods for better data warehouse load performance

SQLRally Nordic in Copenhagen – Full day training on SSIS internals and performance

On 2 March 2015, I am giving a full day pre-conference training session on SSIS internals and performance at SQLRally Nordic in Copenhagen

You have worked with SQL Server Integration Services (SSIS) for a few years, but more often than not, you see performance problems with your packages. Some packages are running slow while others are taking up more memory and CPU than before. 

The first part of this full-day session will cover the internals of SSIS. Starting with an overview, we will move into the details of the control flow engine and the data flow engine. At the end of the internals section, you will have a deep understanding of the strengths and weaknesses of SSIS and what is the best way to design your packages. 

The second part will cover the architecture of the SSIS Server, which came with SQL Server 2012. We will dive into the SSIS catalog and the host process used to execute the packages. 

The third part will cover how we can use the knowledge about the internals of SSIS to gain better performance. This part will show practical examples of common performance problems, and how to solve these. Furthermore, we will look at how designing and tuning your data flows can increase performance.

Join me for a day full of fun with SSIS. Super-early bird price is ending on 27 October 2014. Register before your colleague.

SQLRally Nordic in Copenhagen – Full day training on SSIS internals and performance

Regional and language settings for Oracle client used with SQL Server linked server

Today, I ran into a problem while migrating a Data Warehouse solution to another server. 
The solution have linked servers for extracting data from an Oracle source system. 
The query would go something like this: 

INSERT INTO dbo.MyTable 
SELECT * 
FROM OPENQUERY(UNDPFSP1ICC, 'SELECT 
 TO_CHAR(COL1_DT, ''YYYY'') AS Col1Year 
 , COL2 
 , COL3 
 , SUM(AMOUNT) AS Amount 

 , SYSDATE 
FROM MYTABLE 
GROUP BY 
 TO_CHAR(COL1_DT, ''YYYY'') 
 , COL2 
 , COL3 
')

I had migrated everything, but while running the extract job, which ran queries like the above, we would get errors like these: 

Conversion failed when converting the nvarchar value '123456,78' to data type int. 

Error converting data type nvarchar to numeric. 

The problem was that instead of using a dot (.) as the decimal symbol, a comma (,) was being returned. This would make the data convert to nvarchar, and an implicit conversion to a NUMERIC or INT would fail. 

Everything was running fine on the old server. The collation of the server and databases were identical (Latin1_General_CI_AS), but the regional and language settings were different. On the old server they were set to English (United States) and on the new server to Danish. Changing the settings on the new server to English (United States) did not help. 

After troubleshooting a bit on #sqlhelp with Amit Banerjee (twitter | blog) and William Durkin (twitter | blog), as well as via Skype with Jørgen Guldmann (twitter | blog), Jørgen ended up pointing me in the right direction. 

The problem as that the NLS_LANG parameter was set to Danish (where we use a comma (,) as the decimal symbol). It is stored in the registry under the following sub-key: 

HKEY_LOCAL_MACHINESOFTWAREORACLEHOMEIDNLS_LANG 

Where ID is the unique number identifying the Oracle home. 

The trick was to change this sub-key from: 

DANISH_DENMARK.WE8MSWIN1252 

To: 

AMERICAN_AMERICA.WE8MSWIN1252 

And then everything worked fine, and the decimal symbol was now a dot (.) instead of a comma (,).

Regional and language settings for Oracle client used with SQL Server linked server

SQLSaturday Johannesburg & Cape Town pre-con: Understanding SSIS internals and performance tuning

I am very happy and super excited to announce that I will be doing a full day pre-con the day before SQLSaturday #327 Johannesburg on 29 August, as well as SQLSaturday #301 Cape Town on 5 September 2014. Having lived in South Africa, I have had the pleasure to visit Johannesburg, Cape Town and Stellenbosch on a number of occasions. I must say, I can’t wait to go back!

Two years ago, I started speaking at SQL Server conferences, when Jody Roberts (twitter) pinged me on Twitter and asked if I didn’t want to give a talk at SQLSaturday #134 Johannesburg. I gave my first talk on Layered Partitioning, and it was such an awesome experience that I decided to go all in; which has resulted in three big conferences (and quite a few smaller ones) this year.

So, this year I am going back to South Africa where it all started to give a full day pre-con on Understanding SSIS internals and performance tuning.

Abstract:

You have worked with SQL Server Integration Services (SSIS) for a few years, but more often than not, you see performance problems with your packages. Some packages are running slow, while others are taking up more memory and CPU than before. But why are they slow or taking up a lot of resources? What is actually going on behind the scene? Join this full-day session to find out.

The first part will cover the internals of SSIS. Starting with an overview, we will move into the details of the control flow engine and the data flow engine. At the end of the internals section, you will have a deep understanding of the strengths and weaknesses of SSIS and what is the best way to design your packages.

The second part will cover how we can use the knowledge about the internals of SSIS to gain better performance. This part will show practical examples of common performance problems, and how these can be solved. Furthermore we will look at how designing and tuning your data flows can increase performance significantly.

If you are in Johannesburg or Cape Town (or feel like visiting the beautiful South Africa), I would be happy to see you at my pre-con.

Sign up for a full day of understanding SSIS internals and performance tuning in Johannesburg on 28 August.

Sign up for a full day of understanding SSIS internals and performance tuning in Cape Town on 5 September.

SQLSaturday Johannesburg & Cape Town pre-con: Understanding SSIS internals and performance tuning