SQL Server 2012 - New Service Broker Features - SQLServerCentral

SQL Server 2012 - New Service Broker Features - SQLServerCentral

Moving to www.sqlwithsanil.com

Hi,


I am moving my blog to my very own website : www.SQLWithSanil.com ! In addition to posting blogs, I can list SQL Server Related events, books and much more with my new website. See you there !

What Just Happened to my online index operation !

I was always under the impression that when you perform online index operations, only the following guidelines apply (Click here for the complete description on msdn ) :

• Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
• Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.
• Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.

Only if things were that simple ! But that doesn't happen with me & I usually end up finding myself in interesting situations. Allow me to explain.

This is just for demo, but let’s say I have a table dbo.cake ;

CREATE TABLE dbo.cake (

      eggs INT NOT NULL

      ,flour INT NOT NULL

      ,sugar INT NULL);

With a unique non-clustered index on eggs (don’t ask me why there isn't a Primary Key !);

CREATE UNIQUE NONCLUSTERED INDEX idx_un_cake_01

ON dbo.cake ( eggs);

A change to the application required that duplicate values  be allowed in the column eggs, but the combination of values in the column eggs & flour be unique.

Since I was asked to try to keep the table available for use during implementation of this change, I decided to make this index change online, using this script;

CREATE UNIQUE NONCLUSTERED INDEX idx_un_cake_01

ON dbo.cake ( eggs,flour)

WITH (DROP_EXISTING = ON, ONLINE = ON);

When this error message caught me by complete surprise (This was on my local box, no production servers were harmed during this test) !

Msg 1844, Level 16, State 2, Line 1

Create index online is not supported on unique nonclustered index.

I have done this a few times in the past, so this error message was a complete surprise to me. A little bit of research, let to this obscure “Readme” document for SQL2005, where in lies the explanation. Here is the relevant excerpt ;

“4.1.23 Unique Nonclustered Indexes Cannot be Created Online

In the final release of SQL Server 2005, unique nonclustered indexes cannot be created online. These indexes include unique nonclustered indexes that are created due to a UNIQUE or PRIMARY KEY constraint. SQL Server Books Online incorrectly states that this is a supported operation. The following error message is returned when you create a unique nonclustered index online by using CREATE INDEX or CREATE INDEX WITH DROP_EXISTING:

Msg 1844, Level 16, State 2, Line 1 Create index online is not supported on unique nonclustered index.

If the unique nonclustered index is created due to a UNIQUE or PRIMARY KEY constraint, (ALTER TABLE ADD CONSTRAINT), the following error message is also returned:

Msg 1750, Level 16, State 0, Line 1 Could not create constraint, See previous errors.

To resolve this issue, remove the ONLINE index option from the Transact-SQL statement. In SQL Server Management Studio, clear the Allow online processing of DML statements while creating the index box from the Options page of the New Index dialog.

SQL Server 2005 does support online index operations that create clustered indexes or nonunique nonclustered indexes, drop or rebuild clustered indexes, or rebuild unique or nonunique nonclustered indexes.”

SQL Server Books Online is wrong they say !! My first reaction was to try running my script again on a SQL2008 instance, where no-doubt, it ran successfully. I decided to test this out on a few versions of SQL2005 I could lay my hands on, and the script failed with the same error on each one of these versions.

Release	Product version
SQL Server 2005 Service Pack 4	9.00.5000.00
SQL Server 2005 Service Pack 3	9.00.4035
SQL Server 2005 Service Pack 2	9.00.3042

I am really glad about having used the same version of SQL Server for testing this, as my target production instance !

I take a few lessons from this experience;

• This can be used an excellent reason to ask for an upgrade to SQL Server 2008, if you are still stuck with using SQL2005
• Be mindful of Disk Space, Performance and Transaction Log considerations with any online index operations
• Don’t assume, and don't just trust books online...........……..always be thorough with your testing!

 Collapse this tableExpand this table

A big thanks to the SQL Community for SQLSaturday #101

Kansas City is definitely a fun place to be, but it got a lot more exciting on the Saturday of 10/29, thanks to the rocking SQLSaturday #101 event !

This was my third SQL Saturday event, but my first one in Kansas City as well my first time speaking. Our usual St.Louis SQL Saturday gang was augmented with quite a few first time attendees from St.Louis. The  Friday night speakers dinner at  Boulevard Brewery was an excellent opportunity to network with other speakers, organizers & volunteers, while watching the Cardinals win the world series. Coupled with delicious food and great beer, the evening was a perfect start to a weekend of SQL learning.

Cerner's Riverport campus is quite an impressive facility. After completing the check-in real quick, we were faced with the tough decision of choosing what sessions to attend. I wish SQL Saturday sessions were recorded like the PASS Summit. Its unfair to have to choose between so many great learning opportunities. The sessions were a perfect mix of experienced and first time speakers.

I really appreciate that I was given the opportunity to speak at this session. I sincerely thank every one who attended my session, even though it was the last session of the day. Service Broker was something I really got interested in after attending Aire Jones's session at  SQLSaturday #82 - Indianapolis 2011 . I learned a lot through trial and error while implementing Service Broker on several projects at work. My experiences through all this was my inspiration behind creating this beginner level session. The slide deck is available for download at SQLSaturday #101 - KC website Link to download slide deck. I am still having some trouble uploading my demo scripts there, so please drop me an email & I will send them over.

After surviving through this exciting experience , we headed over to the Raffle, where several lucky winners walked away with iPads & Kindles. As per our SQL Saturday tradition, we went out for Sushi before heading back to the Lou that night.

Talking about Service Broker at SQL Saturday #101: Kansas City

I am happy to share, that SQL Saturday #101, Kansas City has offered me the opportunity to be a first time speaker! After being a first timer at the PASS Summit this year, speaking at SQL Saturday is going to be another exciting first timer activity for me this month.

A few days before the PASS summit, the good folks at SQL Saturday #101 accepted my session. I got to meet with Bill Fellows at the PASS summit and thank him for giving me this opportunity. I will be speaking about "Asynchronous programming with Service Broker" - a beginner level session. Without divulging too much detail, I am just going to say that SQL Server professionals who also happen to be Food Network fans and asynchronous messaging enthusiasts, are going to love it !

I was fascinated by Service broker, after attending Arie Jones "Getting started with Service Broker" session at SQL Saturday #82, Indianapolis earlier this year. I have been a Service broker enthusiast ever since. I found a lot of good use for service broker in a wide range of applications, learned quite a few things while setting them up and had to put out a lot of fires! Now, I am looking forward to share some my experiences.
 
I have been spending the past couple of weeks refining my slide deck, setting up a good demo, practicing my public speaking skills and bothering my colleges/SQL buddies to review my material. I am starting appreciate how much effort goes into creating a session for a such an event. Makes one wonder how the speakers at the PASS Summit manage to make it look so easy!



The session line up for SQL Saturday#101 is pretty impressive. I am definitely looking forward to a Saturday of SQL learning as well as some Kansas City Barbecue. It's only been two weeks since PASS, but I was starting to miss the SQL Server Community. If you haven't already registered for it, please register and make every effort to attend. It's only 4 days away, on 10/29. You can stay updated using the twitter hashtag #sqlsaturday101. I can assure you that it will be worth your time!

A First Timer at SQL PASS 2011

Could there be a better time to start blogging, than to share my first timer experience at the SQL PASS Summit in Seattle ? So here is my very first blog about the very best SQL Server event on this planet !

Yes, I was super excited when my boss picked me to go for the PASS Summit 2011, but nothing could really prepare me for the magnitude of the event. I was lucky enough to be able to attend a pre-con on Tuesday, so I landed in Sea-Tac on Monday evening and couldn't help but compare it to Lambert-St.Louis. Went for the networking dinner that night at Lowell's - had the best Alaskan Salmon of my life & of-course met a ton of SQL professionals.



Next up on Tuesday was my very first full day pre-con, which left my brain completely saturated with the mind-blowing new features in SQL Server 2008 R2 & 2012. The welcome reception was truly a Red Carpet event for us first timers, we literally walked in on a Red carpet !

The next 3 days of the main event were a constant buzz of activity, full of key-note speeches , sessions , vendor demos, raffles and networking events. I not only learned a ton of cool technology from the experts, but also mingled with  database professionals from Ukraine, Indonesia, Belarus, India & Netherlands ! And then there was the party at Gameworks...... I don't remember the last time I played so much Air Hockey and flight simulation !

I did manage to find some time to visit the Public market, specifically to shop at Chukar Cherries and Market Spice, where I found the most exquisite green tea. We even got to see the Occupy Wall Street rally there ! Before I knew it, four happening days had passed and it was time to head back home. I now understand why people want to keep going back to the PASS Summit year after year. I sincerely thank the SQL community for this summit .I bid farewell to all my SQL friends at the summit and I am hoping to meet some of them again at  SQLSaturday #101 in Kansas City.