Yes we're doing it again. SQLBits (www.sqlbits.com) will be back on the 1st March at The Lakeside Conference Centre in Birmingham.

Its a bigger venue than TVP so we shouldbe able to let more people attend.

Registrations aren't open yet but if you register on the site we will let you now when it does open.

In the meantime if you want to submit a session then please do so following the instructions here.

http://www.sqlbits.com/information/SessionSubmission.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQLBits 2008 In Birmingham on 1st March Call for speakers

How many people actually create accounts for the SQL Server services to use? I would expect that once you step out of the enterprise the majority wouldn't be. Even though its bet practice.

Why do I think that? Because its generally a pain. If installing the full suite, you have the engine, analysis services, reporting services, integration services, the browser and sql agent all needing service accounts.

So thats 6 accounts, you then need to assign the relevant service to the relevant group.

Then you need to add the SQL Agent user access to the SQL Server.

So what do people do, I suspect most either, use their own account (which is likely to be a domain account and/or a local admin), the local admin account, the network service and the local system account. None of which follow the principle of running with least priviledges.

That results in lots of setups that are running with high elevated priviledges and/or situations where the network or local system accounts have ben used which result in certian features not working, and thus casing confusion and annoyance.

Since the setup fr SQL has change in CTP5 and the service account selection is very different some MVPs have been discussing the options. Hugo Kornelis came up with a great idea. Why doesn't setup create the accounts for us. I remember IIS used to do that to avoid a highly priviledge uer being used as the default IIS account. So why can't SQL.

Here is Hugo's suggestion on conect, https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=310609. If you think its a good idea please vote. Even though the item is still closed, if we get enough votes MS should reconsider it.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Creating service accounts should setup do it for you?

Katmai increases the concurrency of SSIS packages by a change to the pipeline.

In SQL 2005 a dataflow is split into sections, This is called the execution tree. Each section has the same buffer strucuture and the buffers are passed to each component in the section of the pipeline. A new secton starts when an asynchronous component is found. i..e the buffer in is not the buffer out like the aggregation component

The key here is that the data is not copied to a new buffer for each component. Rather each component accesses the same buffer (one after each other). This is great because copying data is an expensive operation.

However it does mean that you are limited to one active buffer. So if you have multiple components only one is accessing the buffer at anyone time.

Well that was the case in SQL 2005. In SQL 2008 the situation changes. Each component will be able to access a buffer thus greatly increasing the ability for you package to run quicker. You are still restrcited by the resources on your machine, CPU and memory being the main ones for SSIS.

I was reading Alberto Ferrari's blog and though of this http://sqlblog.com/blogs/alberto_ferrari/archive/2007/11/15/ssis-multicast-and-trash-or-separate-and-union.aspx. In Albertos situation he initially uses multicast and conditional split. Whilst you might think these cause new sections in the execution tree, they don't they are in fact synchronous components. What this means is that in the first package he is only maintaining one copy of the data.

However in the second the use of the UNION which is an asynchrone ous component, a new section is started and thus the data is copied into a new buffer and so the time difference he experiences is due to the fact that the data is being copied.

In summary once data is in the SSIS section of a pipeline if you use it or not is irrelevant. If a piece of data is not used at all, then don't put it in the pipeline. Thats why you get the warning message saying a column is't being used. Your package may perform better if it is not used.

So bring on Katmai and we should see Albertos scenario improve even further.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SSIS Pipeline - Katmai changes - Reusing data

I read Steve Jones editorial inDatabase Weekly today and saw his statement.

"I still recommend jumping from 2000 to 2008 if you haven't already upgraded"

My personal view is that if you are looking to replace your systems then 2005 has some great features that you get out of the box and is now stable.

Some of the most important features for me are around consistency and recoverability. I've had 2 clients in as many weeks that have supsect databases. Something has caused data corruptions and the database is inaccessible.

SQL 2005 has page checksums which are a great mechanism to prevent crap from being written or read from the disk. It is also contains numerous bug fixes that SQL 2000 doesn't.

But this on top of the fact that SQL 2008 (Katmai) will not RTM for a considerable amount of time and the first service pack probably won't be in during 2008 (but what do I know). That means to get a stable SQL 2008 you have to wait at least 14 months.

Further more having native 64bit versions of SQL means that the scalability of SQL 2005 is so much more that SQL 2000 especially on standard edition.

Personally I would move to SQL 2005 now, to benefit from the high availability and scalability features.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Should you wait for Katmai.

Did you know that your package has an identifier in it?

Well it does and it can be used to provide automated reporting of your package executions. Using the eventhandlers you can log information to a central store to provide easy reporting.

The key for this to work is to use the identifier for the package so you will be able to distinguish between packages that are running and logging to the same database.

However if you are using best practices and working with template packages then you may find that all your packages have the same identifier. By default if you reuse a package the identifier doesn't change. So what can you do.

Well in the package there is a ID property that you can regenerate. To do this,

1.      Open your package.

2.      Click on the design sruface of the control flow. This selects the package

3.      In the properties window there should be an ID property.

4.      If you click on it there should be a drop down, with one option "<Generate New Id>"

5.      Clicking on this will generate a new Id

Once done this generates a new ID. It doesn't generate new IDs for the tasks in your package. For this reason in your logging solution when you log the status of a task you need the package ID AND the task ID to be able to uniquely reference the task.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SSIS - Change your GUIDs