SimonS' SQL Server Stuff

Service Broker - Objects reside in each database

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Service Broker - Objects reside in each database
The new RSS feed is here

One thing you learn very early on with Service Broker is that even though Services and Contracts at first may appear to be server wide configuration, they are actually database objects.

This makes sense as the service broker objects relate to a function i.e. application and the level of granularity for backing up and maintaining such a function is a database.

However the difference between normal objects and Service Broker objects (apart from the case sensitivity) is that when you create Service Broker objects you can't specify the database or a schema. This means you have to be in the database you want to create the objects when you execute your CREATE statements.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Service Broker - Objects reside in each database

Service Broker - Asynchronous Logging

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Service Broker - Asynchronous Logging
The new RSS feed is here

One of the solutions I am looking to implement is an aysnchronous logging mechanism. Whilst this is not the a truly native messaging solution that Service Broker has all the bells and whistles for, it can still be achieved.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Service Broker - Asynchronous Logging

Service Broker - Clean up your transmission queue

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Service Broker - Clean up your transmission queue
The new RSS feed is here

This isn't about changing your air filter, spark plugs or oil. Its about what to do when, like me, you don't get things right first time.

If developing Service Broker applications and you misspell your services, forget to create a master key, or do any of the many things that result in your messages not being delivered, then your transmission queue will fill up.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Service Broker - Clean up your transmission queue

Service Broker - Grouping conversations

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Service Broker - Grouping conversations
The new RSS feed is here

In my investigation of service broker as an synchronous engine I wanted to be able to put messages on a queue and then for a batch process to take them off again.

What I however found was that if a message is sent to a service i..e from a trigger, then when my batch process receives from the queue it only gets the one message. This I can understand but thought there must be a way around it.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Service Broker - Grouping conversations

Restoring your SQL Server - Chicken and the Egg

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Restoring your SQL Server - Chicken and the Egg
The new RSS feed is here

Have you tested your DR plan? Do you have one?

Well if you do have one then unless you test it its worthless. There are many nuances that occur in a recovery process. The Storage Engine Team have posted about one suhc nuance.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Restoring your SQL Server - Chicken and the Egg

SSIS - Be very very careful

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/SSIS - Be very very careful
The new RSS feed is here

I've mentioned before that a good practice is to not use the table or view option in the OLEDB source and lookups due to returning more columns than you need and being tied to the structure (i.e column order) of the table.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SSIS - Be very very careful

SQL 2005 Rocks

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/SQL 2005 Rocks
The new RSS feed is here

Those are the words my ops DBA utered this morning, was it some big new feature like database mirroring, snapshot isolation, ssis.

No it was the fact that failed login attempts, when recorded in the log file, now include the Client IP address.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SQL 2005 Rocks

Service Broker Gotchas - Its case sensitive

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Service Broker Gotchas - Its case sensitive
The new RSS feed is here

I've started using service broker and its full of new terminology. This will be a set of posts of the gotchas I've found on the way.

The first is "case". Most of us run SQL in case insensitive mode so we can refer to our tables and columns by what ever case is trendy at the time, camel case, title case, upper case etc.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Service Broker Gotchas - Its case sensitive

object_name() to be extended in SP2

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/object_name() to be extended in SP2
The new RSS feed is here

Kalen recently posted a suggestion that the object_name() function should take a database as well to resolve the name. This has bugged me for quite a while, generally when I've been looking at locks. The lock information resides in on table in master. and referenced the relevant DB of an object involved in a lock.

This meant that you have to either run the SQL specific to the database containing your objects, or join to that database's schema tables. Either way it was nasty and wasn't very flexible.

So the good news is that Kalen's suggestion is currently flagged for inclusion in SP2 and will enable you to provide a database Id and an object Id to get the object name.

This goes to show that connect does work, as can also be seen with the bugs I've raised recently against the Master Target server management in SSMS.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/object_name() to be extended in SP2

Performance improvements for Katmai the next version of SQL Server

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Performance improvements for Katmai the next version of SQL Server
The new RSS feed is here

If you use SQL Server and have a great suggestion for performance improvements to the engine then post them at the sql tips blog because they want to know
http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_DW_PERF_IMP_NEXT_SQL.aspx

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Performance improvements for Katmai the next version of SQL Server

Full text suggestions for SQL Server 2007?

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Full text suggestions for SQL Server 2007?
The new RSS feed is here

Who knows when it will be out, but these are 2 of my suggestions for enhancing Full Text.

Allowing it to index documents outside of the database.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=208632

and incorporating it as a first class index or add the ability to include other non text columns in the full text index so when someone does a search for a common keyword performance doesn't hit the floor.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=208636

Its free to make suggestions, just go to the connect website and fire away.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Full text suggestions for SQL Server 2007?

Do you think SSMS is slow?

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Do you think SSMS is slow?
The new RSS feed is here