Friday, 23 July 2010

The week that was Friday 23 July 2010

Its been a busy week this week. From troubleshooting several production issues to writing some incremental load SSIS scripts, the week has flown by. I Still haven’t managed to get my replication post out the door, and after re-reading and re-working again I think it will be a few weeks yet before it makes it on to the blog. If it doesn’t get dramatically better soon I may just ‘can’ it altogether.

I intend to write some posts on the incremental data loads I have been working on this week, they will be written and published in the coming weeks, I have a few other ideas for technical posts and they will be written in the near future.

On the home front, Mrs Ellis and I were hoping to make it to an outdoor theatre production this week bit the weather has been terrible with heavy rain so we haven’t made it.  The puppies on the other hand love the rain they love swimming in the lake that is close by, they love rolling in the mud and getting dirty. Trouble is they now stink so I think it may be bath time they weekend. They hate baths though, if it smells, is muddy or dirty in any way then they love it, if it cleans them up and makes them look nice then they hate it.

Not a very technical post but it is Friday, I will have been in work for several hours when this post is published and any minute now I’ll betting myself a bacon roll….emm bacon!

Tuesday, 20 July 2010

Raining Tuesday – Cricket Cancelled – Replication Stinks - SQL BITS VOTE NOW!

Well this was going to be replication post, but I have read it, re-written it, read it, re-written and it still stinks. When I got fed-up of reading and re-reading my stinky replication post I turned on twitter and read a few tweets by active SQL Server community members in the UK, people like Simon Sabin and Neil Hambly and Gavin Payne. They were talking about the SQL BITS conference being held this autumn in York. SQL BITS is a great conference we don’t get many of them in the UK but this is really well run, well organised and well worth your support if you are a database/sql server professional here in the UK.

All the speakers have submitted their sessions, and now its up to us, the attendees to vote on what we want, there is over 90 sessions to chose you simply have to register and you get to vote for ten sessions, I think  the sessions with the most number of votes when voting closes gets picked, democracy rules, think of it as the SQL X-Factor (or SQL Idol for my American friends), but its free to vote and we can think of the organisers such as Simon Sabin and Christian Bolton(to name just two) as Simon Cowell and Loui Walsh (Not sure if we have a Cheryl Cole or Dannii Minogue though!) but I over heard on twitter that only 44 people have voted thus far, I’m one of them, and I think you should be too. To vote for your favourite act simply visit the website sign in, read the abstracts (at most a paragraph in length) and pick the sessions you would like to attend, support the event and the speakers and everyone benefits. And there is no JEDWARD either so just vote for your favourite.

Gavin Payne said that if his session doesn’t get picked he will do a boot leg session out the back:



Now I have never been to York, but when I was at university the people on campus outside in the street projecting their voice on the weekend were usually buskers, don’t make Gavin go SQL busking in October, just vote for him and maybe learn a little Oracle too.

Friday, 16 July 2010

The week that was: Friday 15th July 2010

This is just a short post for a Friday, I have no technical content ready to publish today but lots and lots of ideas and a couple of drafts. This week I have looked a little at replication, I have a few posts lined up next week on that.  I have also been doing some initial prep work on migrating quite a large SQL Server 2000 database to SQL Server 2008 and I intend to write some posts around that too.
I had a TSQL2sday post go live on Tuesday all about learning, you can find my post here. I have read some of the posts already, not all of them yet, I like to wait for the round up post from the host before reading them all. Robert said on twitter that there were over 30 submissions so I guess that post may take a while to come out.
This weekend I have a whole bunch of DIY to do…I hate DYI but it needs doing. I’m playing cricket this Saturday for Senghenydd Cricket Club,  my two Golden Retriever puppies (They are dogs really but will always be puppies to me) will want to do something nice too, they do like to go to the beach perhaps we will get down there at some point. The cricket and the beach are weather dependant though and as yesterday is Saint Swithun’s Day and it rained all day, it could rain for the next forty days :-(
Have a great weekend!

Tuesday, 13 July 2010

TSQL2sday #008 – Gettin’ Schooled

TSQL2sDay This months TSQL2sday is being hosted by Robert Davies of MCM and Microsoft fame, you can find his post here. This month’s topic is titled ‘Gettin’ Schooled’ and for those participating in the blog party this month we will be looking at how we learn and how we teach.

This fits in quite nicely with my post from last Thursday where I wrote a short note on the SQLBITs conference being held in the UK this Autumn.

How do I learn?

I have been working with SQL Server since 2002, for most of that time as a DBA. This post will focus on the learning that I have undertaken since becoming a DBA. I know we all went school, some of us went to university, some of us may have even taken higher degrees in the form of Masters or PhD's etc. There has actually been quite a lot of debate and quite a lot written about whether you need college/university degree to be a DBA and I don’t want to go down that route and discuss that topic here, instead I want to look at the type of learning I think most DBAs and developers will have gone through at some point to train themselves to do their jobs.

Technical Training

When I first started out working with SQL Server I was very lucky.  My employers at the time had a reasonable training budget and they were prepared to send me on several official MCT courses. All SQL Server based and all based around the MCDBA. I forget about the actual course titles but there was a course on administering SQL Server. As a ‘newbie’ to the DBA profession and relatively new to SQL Server those courses did actually provide a good grounding for me because I was able to apply what I learned on the course in a practical environment, supporting production SQL Servers. I did also take the exams/certs associated with the courses and I have achieved both the MCDBA (SQL Server 2000) and the MCITP for SQL Server 2005. Certification is another discussion altogether! The training was good for me because I was able to combine  the theoretical  lessons and labs of the course with practical hands on experience. This meant I learned and gained experience in the technology and later I was able to pass the exams. I think  that practical real life work experience was vital though for any DBA.


The best learning experience for me personally was in doing the job of a DBA. I was lucky, I got good training, more importantly I got two great mentors in my first full time DBA job. These guys taught me a lot and they helped me greatly early in my career. They gave me plenty of responsibility but at the same time were always available to help me out when I needed it and because of the help and responsibility  those guys gave  I was able gain great experience.

As the years have gone by and I have changed jobs and setup my own contracting company I have found that as technology changes and new features and products have come out the best way to learn these new technologies is to use them and figure them out for yourself. I’m lucky I have a MSDN subscription which allows me to use the Microsoft products and technologies as they come out. I have a very powerful desktop where I can run a several virtual machines at once so I have very own test domain setup  where I can try different  technologies and experiment with different setups.


I have read many SQL Server books over the years and books are still a great way for a DBA to learn. These are three of my current favourite titles:

SQL Server MVP Deep Dives

Professional SQL Server 2008 Internals and Troubleshooting

Microsoft SQL Server 2008 Internals (Pro - Developer)


I read a lot of blogs from people in the  SQL Server community, I find this a great way to learn and keep to update, from the documented experiences and knowledge of other people doing a similar job to myself. My top 10 SQL Bloggers (Taken from my Google reader reading trends) are

1.Paul Randal
2.Kimberly L Tripp
3.Steve Jones
4.Brent Ozar
5.Brad Mcgehee
6.Buck Woody
7.Jack Corbet
8.Adam Mechanic
9.Thomas LaRock
10.Denny Cherry

There are many more great bloggers out there and I read most of them but I could fill the page with a list of the good blogs that I read, apologise if I haven’t listed you, but feel free to leave a link to your blog in the comments if you think it is something that we all should be reading.

User Groups and Conferences

I like conferences they are a great way to learn and good way to network. There are many conferences and user groups out there, I wrote a post last week about the SQL BITS conference you can find that post here. I also recently attended a SQL Server Master Class held by Paul Randal and Kimberly L Tripp you can read my review of that great event here. There are a number of virtual user groups you can attend. I like the events put on by pass especially if travelling distances is difficult for you, you can attend these events from the comfort of your own desk and they are a great way to learn.


I have been writing about SQL Server since 2007. I have been writing this blog since late last year. I found writing on my website a little more time consuming and tricky than writing on the blog so I tend to write more on my blog these days but the website is still available. I started writing to improve my knowledge and enforce my understanding of SQL server. If I’m required to learn something new I like to write some blog posts about it. It reinforces my knowledge, If I get anything wrong people are very helpful and point out my mistakes or maybe ask questions which again allows you to grow and expand your knowledge. If you write on a blog or website it will be there for future reference and you might just help somebody else along the way.

Monday, 12 July 2010

SSIS - Enabling Logging

Have you ever had a scheduled run of one of your SSIS packages fail? Have you ever wished you had a little more information available to you when troubleshooting? SSIS has a neat little feature that you can enable called 'Logging' and it does pretty much what it says on the tin. It logs the execution of your SSIS packages, you have a variety of logging options to choose from including logging to a SQL Server database itself, and if the worst happens and the execution fails you can gain some useful information regarding the reasons behind the failure.

Setting Up Logging

You can enable logging within your package. Open the SSIS package in BIDS (Business Intelligence Design Studio)

My SSIS package has two control flow tasks 1. Execute SQL Task which is used to truncate a staging table and 2. A Data Flow task which copies some data from the Sales Header table in the Adventure works database to a staging database.

Right click on  some open space on the control flow tab and select <Logging...>



After selecting <Logging> you will be faced with the screen below:


I want to enable logging to capture the execution of the whole package so I enabled the tick box called package (the root container) which automatically selected the other two control flow tasks.

On the <Providers and Logs> tab I selected a provider type of <SSIS log provider for SQL Server> and selected <Add>. In the "Select the log to use for the container" section (My container in this case is the whole package) I selected the tick box for my log provider. In the configuration column (I had created a separate database to hold the log table, but you could use any database if you wish) I setup a new connection to point at the logging database.

On the details tab I selected the following events:

  • OnError
  • OnPostexecute
  • OnPreExecute
  • OnWarning

There is a whole bunch of events to choose from


Click <OK> and you have configured logging.

I then run my package.

And I can see what has been logged to the SQL Server table by running this simple query:

select * from dbo.sysssislog

The results contain a whole bunch of useful information including any messages returned including errors and start and end time of specific tasks.

Friday, 9 July 2010

SQL Server Agent – Proxy error

I was setting up some SQL Server agent jobs in a test environment. I needed one of our testers to be able to run the job on demand. I made the testers account the job owner and I created a proxy for the the job to run under. I granted  the proxy appropriate rights for SSIS. The SSIS package needed permission on the share I added the testers account to the “SQLAgentUserRole” and asked the tester to try the jobs.

The job failed with the following error:

“Proxy (11) is not allowed for subsystem "SSIS" and user "xxx\xxxxxx". Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy.
The 'sp_sqlagent_is_member' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. (Microsoft SQL Server, Error: 14516)”

This failed because the tester’s account did not have permission to use the proxy. To fix this I run the following script.

USE msdb ;

EXEC dbo.sp_grant_login_to_proxy
@login_name = N'xxx\xxxxx',
@proxy_name = N'ProxyName' ;

The tester was then able to run the job.

Thursday, 8 July 2010

SQL Bits 7 – The 7 wonders of SQL

SQLBitsLogo I have been procrastinating this evening, I have several blog posts that I want to write but the football is on TV and my wife is writing up an assignment for her Master’s degree  and she wants me to proof read her work. So while I’m waiting for the next instalment of her assignment I thought I would do my bit to promote the  SQLBITS conference. I have another post all about learning that I intend to write this weekend for TSQL2sday but this is simply a blatant plug for the SQLBITS conference. This is lifted from the website:

“We are pleased to announce SQLBits - The 7 Wonders of SQL conference, which will be taking place from September 30th to October 2nd at York University. SQLBits is the largest SQL Server conference in Europe and the previous six conferences have established it as a must-attend event for DBAs, developers and BI professionals who work with Microsoft SQL Server in the UK and Europe. We are making this the biggest event ever with capacity for over 500 SQL Server professionals.”

It is a great event, I have only attended one, the community day of the SQLBITS Goes West Conference  held at the Celtic Manor in Newport(The venue of this years Ryder Cup which incidentally is being held on the same weekend as SQLBITs). I saw presenters like Brent Ozar, Simon Sabin, Justin Langford and Christian Bolton give top class presentations on SQL Server…All for free! It was a great day.

It is a great opportunity to get some top class SQL Server training at very little cost.

If you are interested in presenting a session at the event then you can submit your abstracts here. The last I heard they had 56 sessions submitted already but they needed more.

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