Allen White

New Certifications and more in June

It's been a really great month!

During Tech Ed I attended great educational sessions, met some amazing people, and renewed acquaintences with others. I also passed the 70-431 exam, satisfying the requirements for both Microsoft Certified Technology Specialist: SQL Server 2005 and Microsoft Certified IT Professional: Database Administrator at one time, since I'd passed the beta exams for 70-443 and 70-444 back in December.

During the week after Tech Ed I passed the 70-441 exam as well, so I'm now one test away from the ITPro:Database Developer certification as well. (I'll be taking that test very soon now.)

Probably the most exciting news of all to me was that my abstract was accepted and I'll be speaking at the 2006 PASS Community Summit in November:

DBA-201: Using SMO to create custom SQL Server Management solutions
11/15/2006
1:30 PM - 2:45 PM

I hope everyone has a great holiday weekend!

Allen

Create Databases using SMO without resorting to Transact-SQL

You know, sometimes things that are really confusing become amazingly simple once someone explains them.

On Friday I'd attended a session at Tech Ed about SMO. One of the things I've found frustrating was the inability to specify database location and size properties before creating the database. I could create the database and then alter it, but the objects were already created in default locations by then and that's less than optimal. I also had to resort to building a Transact-SQL string to create a snapshot database. I mentioned these frustrations to Richard Hundhausen, the presenter of the session and a Microsoft Regional Director. He tried a couple of things and had the same results I did.

Richard sent an email to Euan Garden, his source for a lot of solutions to SQL Server 2005 problems. Euan is no longer working on SQL Server, but directed us to Michiel Wories and James Howey. I explained my application requirements to them, and Michiel sent me some sample code showing how to solve both of these issues.

        Dim strDBName As String
        strDBName = "TestDatabase"

        Dim dbCopy As Database
        Dim dbFG As FileGroup
        Dim dbFile As DataFile
        Dim dbLogFile As LogFile
        Dim dbSnap As Database

        dbCopy = New Database(srv, strDBName)		'Instantiate the new database
        dbFG = New FileGroup(dbCopy, "PRIMARY")		'Instantiate the PRIMARY filegroup
        dbCopy.FileGroups.Add(dbFG)			'Add the FileGroup
        dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
        dbFG.Files.Add(dbFile)				'Add the File
        dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf"	'Define the actual file system name
        dbFile.Size = 25.0 * 1024.0			'Define the size of the file
        dbFile.GrowthType = FileGrowthType.Percent	'Define the Growth Type
        dbFile.Growth = 25.0				'Define the Growth Percent
        dbFile.MaxSize = 100.0 * 1024.0			'Define the Max database size

        dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
        dbCopy.LogFiles.Add(dbLogFile)			'Add the log file
        dbLogFile.FileName = "L:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf"	'Define the log file system name
        dbLogFile.Size = 10.0 * 1024.0			'Define the size of the log file
        dbLogFile.GrowthType = FileGrowthType.Percent	'Define the Growth Type
        dbLogFile.Growth = 25.0				'Define the Growth Percent

        dbCopy.Create()					'Create the database

Michiel's example defined multiple filegroups and data files but my needs were simpler. His example also is in C#, and I mostly work in VB. (As soon as he posts his examples I'll link to them.)

Building the snapshot database was also very straightforward.

        dbSnap = New Database(srv, strDBName + "_snap")	'Instantiate the new database
        Dim fgSnap As FileGroup
        For Each fgSnap In dbCopy.FileGroups		'Step through the existing filegroups
            Dim dbFGSnap As FileGroup
            Dim dbFileSnap As DataFile
            dbFGSnap = New FileGroup(dbSnap, fgSnap.Name) 'Instantiate a new filegroup to match the base database's filegroup
            dbSnap.FileGroups.Add(dbFGSnap)		'Add the FileGroup
            For Each dbFileSnap In fgSnap.Files		'For each file in the group, create a corresponding snap file
                dbFGSnap.Files.Add(New DataFile(dbFGSnap, dbFileSnap.Name, dbFileSnap.FileName + ".snap"))
            Next
        Next
        dbSnap.DatabaseSnapshotBaseName = strDBName	'Define the base database for the snapshot
        dbSnap.Create()					'Create the database

As you can see, when it's explained by someone who knows what they're doing it's easy!

Thanks so much to Richard, Euan and especially to Michiel Wories, for helping me get over this hump.

Allen

TechEd Final Day - Friday

Well, we've finally reached the end of Tech Ed 2006. Last night's party at Fenway Park was great (except when the woman who got hurt during the Train concert, of course. Hopefully she's ok.) I called my Dad while standing in left field in front of the Green Monster - yep, had to do that. I also walked across the top of the Green Monster, and my wife took a picture of me in the visitor's dugout (where else would a Cleveland Indians fan be?)

This morning I focused on chalk-talk sessions in the TLC area, the first one by Durga Gudapati on SQL Everywhere. This looks like an interesting product, and a way to build small, very portable applications that can capture key transactional data. I can see some interesting possibilites with that product.

After that was a session on SQL Server Express, by Chad Hower. It's a fully functional version of SQL Server that has some memory, processor and database size limitations, but very capable for client-type applications. Chad had an interesting idea, though, in that it can be used for apps that can run entirely within the SQL Server engine, so that you send the SQL Server Express runtime and a prepopulated database to a client system and the application, by being built as a SQL CLR application, requires no other setup. Again, some interesting possiblities to think about.

This afternoon I attended Stephen Forte's presentation (DAT340) on Database Design Patterns. He talked about design patterns for OLTP databases, SCD (Slowly Changing Dimension) databases and DW (Data Warehouse) databases, and how each one of these types requires a different approach to the relational model. He firmly stated that these functions should never be mixed in a single database, and that one of the biggest mistakes application builders make is by doing reporting or analysis against an OLTP database, for example. I'm still a newbie when it comes to the BI space, but I see his point, and will be looking more closely at this in the near future.

Finally, the session I'd been waiting for all week, DAT342 - Programming SMO, presented by Richard Hundhausen. Before the presentation I had time to talk with Richard about a couple of the things I'd run across in SMO that I felt were lacking and, after doing a quick test and finding the same results I'd found, agreed. He's since put me in touch with the people at Microsoft who are responsible for SMO, and I'll be sharing my "pain points", as Microsoft is calling them now, with them, and we'll hopefully make some progress with that great product. Richard's presentation was well done, and he showed us an application he's written he called a "concordance" application, which evaluates columns within a database, shows their datatype and the tables they're present within, and red flags every instance where the same column is defined differently within a database. Very cool use of SMO.

This was a great conference, and I learned a lot, got a lot of cool ideas, and got to touch base with key people in the SQL Server world.

Allen

Hitting the Wall at TechEd (Thursday)

In a session on Monday Richard Campbell mentioned that TechEd week was like a marathon. Of course, my thought was "you're not a marathon runner, what do you know about what a marathon is like." Well, it's Thursday afternoon, and I've hit the wall. ("Hitting the wall" is a phrase we use in marathon running when you've reached your physical limit, usually somewhere after about 19-22 miles. After that it's pure determination to get to the finish.) I was sitting in Gert Draper's presentation on Visual Studio Team System for Database Professional (Part 3) and absolutely nothing was sinking in. I decided to take a rest and headed back to the hotel for a bit of R & R before tonight's Fenway Park extravaganza. Of course it's now raining in Boston and I hope that doesn't ruin the party tonight.

This morning in session DAT329 Dr. Greg Low gave a great presentation on CLR integration for DBA's. It's tempting as a DBA to not trust anything that a developer will want to load into a CLR assembly, because that code will execute within the SQL Server space. (We all know how much DBA's trust developers, don't we? Dr. Low presented the process clearly, and showed some real benefit to using CLR assemblies within SQL Server.

The next session today was DAT331 - Monitoring SQL Server with SQL Server Business Intelligence tools, presented by Lara Rubbelke. Now, I've been a DBA for 14 years, but I've never been able to pick up the whole "cube" thing. I see how people who know what they're doing can get some amazing information using them, but I just haven't been able to wrap my brain around it. I figured this session, being a DAT session not a BIN session, would finally allow me to get a handle on things, focusing on analyzing performance data, which I'm always looking to learn more about. Well, things went well for about 5 or 6 slides, but then she jumped to light speed and I was lost. I understood the performance results she was returning, but didn't have a clue as to how she got there. I'm going to be checking out her blog and downloading her examples to see if I can make any kind of headway.

Now it's time to get some rest before the big gig tonight.

Allen

TechEd Wednesday - A New Certification

Well, this turned out to be a really great day at TechEd 2006.

It started with sesstion DAT421 - Peeking Inside SQL Server 2005 Engine - with Dan Winn. Dan always does such a great job with his presentations, not just because he's so knowledgable, but also because he's so enthusiastic. It's really great to see someone who obviously loves what he's doing as much as he does. His explanation of the Dynamic Management Views available to monitor key SQL Server activity is clear, concise, and enjoyable.

After Dan's presentation I attended Michael Raheem's session DAT323 - Be More Productive with SQL Server 2005 Tools, presented with Roni Karassik and Paul Mestemaker. I first saw Michael present last fall at PASS in Dallas, and he didn't disappoint again. The features available in Management Studio, SQL Profiler, Database Tuning Advisor and others are so vast it's easy to forget all the tools we've got available, and their presentation was entertaining as well as informative.

I attented a lunch presentation put on by the editors at TechNet Magazine, because I'd like to start writing for publication, and they had some great ideas. The expo hall was open most of the afternoon and I went to visit Tony Mann, who was manning (no pun intended) the Authors area of the Microsoft booth. He was giving some very cool swag (consisting of a USB connectivity kit) if you filled out a survey form. Well, word gets out quickly about cool swag and before long he was completely swamped. I started helping him hand out these kits as people filled out the forms, and Tony had to run and get more boxes of kits. Victor Isakov, one of the other SME's I worked with on the 70-444 exam Alpha Review, stopped by and he pitched in as well, and we worked to keep up with what became a feeding frenzy. Finally Tony said there were no more kits available, and a few people complained, but the area cleared out. What an incredible experience.

After that I went over to the TLC area and shared the story with Rick Heiges, and he got me to do an interview with Chuck Boyce for a podcast interview for PASS, on both the certification work I did and the swag story. That was fun.

I finally steeled myself to go and take the 70-431 exam, and being very careful to not rush through any question, I PASSED the exam!!! Phew!

Spent, I headed back to my hotel, had dinner with my wife, and then went to the Microsoft Influencer Party over at Quincy Marketplace. That party is always fun, and I got a chance to catch up with Tony Mann, Fernando Guerrero, Tobias Thernström and Victor Isakov (from the 444 Alpha Review team), and got to chat with Adam Machanic about his position with Solid Quality Learning.

More sessions tomorrow, followed by the party at Fenway Park!

Allen

Another good day at Tech Ed (Tuesday)

I was only able to get to two sessions today, DAT210 - SQL Server 2005 Database Engine: Six Months After Its Release, led by Sameet Agarwal and Rob Reinauer. My favorite quote was when Sameet defined the Plan Forcing option of Plan Guides as the "shut up and do what I'm telling you" option.

Roger Wolter did a great job explaining some of the complexities in DAT315 - Building Reliable Service Applications with SQL Server 2005 and Service Broker. There are a lot of pieces to Service Broker, and figuring out the proper sequence to set things up can be confusing, but he makes it seem very straightforward, and it's evident there's a lot of power waiting to be tapped. (I think I need to build a presentation of this product so I can understand it fully myself.)

I spent some time in the TLC area chatting with Rick Heiges and watching Fernando Guerrero talk about the relevance of stored procedures with all the power available in ADO.Net 2.0, and I went through a very cool developers lab on Windows Vista, demonstrating its focus on running applications in the lowest necessary privilege levels. (The red backpack was a very nice bonus.)

Most of my time was spent going through the practice tests, preparing for my 70-431 exam. I don't feel real confident because there's a lot of focus in the exam on aspects of SQL Server I don't use, like replication and Service Broker, and a number of questions on XQuery, which goes against my personal feeling that XML removes the "relational" from relational databases. I got pretty annoyed, though, when on one of the practice tests I answered a question that described setting up backup schedules for full backups on one schedule, differentials on a second schedule, and transaction log backups on a third schedule. I answered that you run the Maintenance Plan Wizard multiple times to set this up. (I know this is true, both from my work with the product and from my work at Microsoft on the 70-444 exam. The practice test told me I was wrong, that it can be done with a single execution of the Maintenance Plan Wizard. Come On! Doesn't Microsoft review these practice tests for accuracy?

Well, enough of my ranting, I need to get some sleep so I'm ready for tomorrow.

Allen

TechEd Monday

TechEd is just SO amazing!

Last night's keynote was interesting, but I felt they tried to cover too much information in too short a period of time. Chloe from 24 was entertaining with her rattling off techno terms like she actually understood what she was talking about. (And the three guys she pulled up on stage will not forget the hugs they got from her any time soon.) I wanted to get some detail on the new VS Team System for Database Professionals, but they only touched on it. It was a 2 1/2 hour keynote, and it felt like they just barely had time to touch on each feature they wanted to highlight. I don't know how you solve that, but it didn't work for me.

This morning Dave Campbell and his team gave a great overview presentation on the vision for SQL Server moving forward. Between the features of ADO.Net 3.0, which will include support for LINQ, the BI tie-ins for Excel and the features planned for revs 2 and 3 of VSTS for Database Professionals, they had a lot to look forward to. We got to see small pieces of these technologies - enough to know that Microsoft isn't sitting pat on SQL Server 2005.

Richard Campbell, the Dot Net Rocks guy, gave a great talk on SQL Queries, focusing mostly on the enhancements in Transact-SQL in SQL Server 2005. His presentation was both informative and entertaining. I got a real kick out of every time he asked the crowd to "ask me WHY I know about this".

Speaking of Dot Net Rocks, I attended the live webcast of that program during lunch, and they had a great conversation with Cameron Skinner and Gert Drapers about both the development of the VS Team System for Database Professionals product as well as the future plans.

I see from other blogs on SQLJunkies that Kent Tegels and Rick Heiges are here as well, and in some of the same presentations. I chatted with Tony Mann and Brian Knight just before lunch, then got to make a couple of comments during Don Vilen's security presentation about my experiences with SQL 2005 in a clustered environment. During the Expo party I got to renew acquaintances again with Fernando Guerrero and with Kevin Kline as well. The networking here is just as great as the technical sessions.

More tomorrow.

Allen

Visual Studio 2005 Team Edition for Database Professionals Webcast

I just attended a web cast for the new Visual Studio 2005 Team Edition for Database Professionals. There's been a lot of buzz about this product this month, and I'm looking forward to learning as much about it as I can at TechEd next week. During the presentation I asked this question:

My developers have full dbo control in the development environment. When their project is ready they often tell me "apply the changes in the dev db to prodn". If I've created a base project before they start, can the VSTS app create a "change" script by reverse engineering the changes between the base line and current db schema?

The question was answered affirmatively, and that answer alone makes this tool invaluable to me as a DBA.

See you next week in Boston!

Allen

Supporting both SQL 2000 Named Instances and SQL 2005 in one Cluster

I ran into a rather serious problem last weekend when attempting to upgrade one of my clustered servers. A previous update required me to reboot the servers in the cluster, and after the reboot the one server running SQL Server 2000 as a named instance wouldn't come back online under cluster administrator. I was able to bring it up using the Services admin tool, but in the Application error log it showed the following message:

[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff;
message = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. 

Searching the net led me to issues with DNS changes and the like, but nothing specific to my situation. We called Microsoft Support and explained the situation to Chetan Rao, a Microsoft SQL Server Support Engineer, and he referred to a KB article which described a problem with SQL Server Browser permissions on the SQL Server 2000 registry tree if you install SQL Server 2000 after installing SQL Server 2005. This wasn't our case, but we looked at the permissions settings anyway, and found that the Browser service account had all the necessary permissions in the registry.

During the conversation Chetan mentioned the possibility of adding the server names and port numbers as aliases under SQL Native Client Configuration. I wasn't able to do that during the business day because the server having the problem was a production server, and that I'd try it first thing in the morning.

In fact, that solved the problem. I was able to bring the server back online via cluster administration once the server was set up as an alias. I then moved the server group to another server in the cluster, and it didn't come online until I went into that server and added the aliases.

In a nutshell, if you've got both SQL 2000 and SQL 2005 servers in the same cluster, and the SQL 2000 servers are named instances, set up aliases in SQL Native Client Configuration on every server in the cluster to ensure proper failover. And a special thanks to Chetan Rao for helping us out of this jam.

Allen