It’s hard for me to believe that I started working with the first board of directors for PASS nearly a decade ago, but it’s true.  PASS has grown over the years due to the support of the user community, the SQL Server expert community, and Microsoft’s SQL Server team.  This year’s conference is just around the corner in mid-November.  If you haven’t thought about registering, you should!  It’s the best and most concentrated SQL Server learning you can get anywhere.  This year there are over 130 sessions, dozens of full day pre-conference seminars, and huge contingents from Microsoft’s SQL Server support, consulting, and development teams.  Take a look at the event schedule at http://www.sqlpass.org.

I’ve had some neat and funny experiences over the years.  In 1999, I was awed to meet the late, great Jim Gray for the first time.  When I saw him again later that year, he remembered me in detail and, every other time after that, always expressed interest and excitement in what I was up to.  What an honor.  Back in 2001, I fell off the stage while delivering a session on translatinge between Oracle PL/SQL code and SQL Server Transact-SQL code.  That tought me to never anger the gods of Transact-SQL by speaking about other database platforms at their conference.  In 2005, I experienced and blogged about the shower that tried to kill me in the conference hotel.  And at the conclusion of the 2007 conference, I’d been working from 6:00 am until midnight or later all week.  This resulted in Rick Heiges sending me a picture of me unconscious, literally stone cold asleep, on the lunch table on the conference on Friday. (Did you blog that Rick?)  In 2004, I had one attendee stop me on Monday to tell me it was her worst conference ever and that she hated it.  I worried about her all year, then in 2005, I saw her sitting at one of the lunch tables.  “I worried about you all year!” I told her.  “The conference was awesome after the first couple sessions.  I really love PASS now.  I was just mad because I kept getting lost,” was her reply.  Whew!

One of the things I’ve most enjoyed about the conference is the awesome people I get to meet.  I remember meeting Allen White a couple years ago and was impressed with how eager and intelligent he was (and a good speaker too). Low and behold within a year or two, he had joined the ranks of the Microsoft SQL Server MVPs.  I’ve seen that pattern repeated many times now.  It’s always a pleasure meeting the true geniuses of the business like Kalen Delaney, Itzik Ben-Gan, Kimberly Tripp, and the late Ken Henderson.  If you’ve ever wanted to talk to the best known authors in the business, you should consider attending the event.  They’re all gracious people and are happy to speak with everyone.

So I thought it’d be fun to hear some other stories from other PASS events.  I’ve tagged several friends below (in addition to Allen and Rick above).  Now it’s their turn to tell some good PASS stories and to tag a handful of other bloggers in turn! 

• Joe Webb at http://weblogs.sqlteam.com/joew/
• Andy Kelly at http://sqlblog.com/blogs/andrew_kelly/default.aspx
• Roman Rehak at http://sqlblog.com/blogs/roman_rehak/default.aspx
• Paul Randal at http://www.sqlskills.com/blogs/paul/
• Aaron Bertrand at http://sqlblog.com/blogs/aaron_bertrand/default.aspx

Check their blogs to read their stories. Enjoy!

-Kev

I ran into an interresting situation today while working with a customer and thought it worthwhile to blog on the subject since my internet searching proved lengthy on the subject.  While trying to run a RECONFIGURE statement after making an sp_configure change I received the following message:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

I’m running on SQL Server 2005 SP2+ so my first thought was… obviously, but I’m not trying to update system catalogs, I’m trying to make an sp_configure change.  After doing some digging I found that the culript was the sp_configure ‘allow updates’ parameter.  This configuration in SQL Server 2000 allowed or dissalowed direct system table updates.  In SQL Server 2005, this configuration item still exists but it is obsolete since direct access to system tables in always prohibited.  While the configuration item is obsolete, having it set to 1 in SQL Server 2005 requires you to run the RECONFIGURE statement using WITH OVERRIDE, otherwise you will get the message above.

If you ever see the message above when trying to run RECONFIGURE you will either need to run RECONFIGURE WITH OVERRIDE, or first run:

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

I find it confusing that an “obsolete” configuration parameter can have an effect on instance behavior, but oh well.  I’d love to hear comments if anyone has any insight into this or similar issues.

Thanks to Jasper Smith, who posted here and pointed me in the right direction.

In this vblog entry I describe and discuss the Microsfot MCP (Master Certification Program). I hope this helps anyone that is interested in this program. Enjoy and I look forward to your feedback!

Check out this latest vblog entry on the topic of efficiency and effectiveness on the job.  I hope that this information helps you in both your career and day to day on the job.

As always, I appreciate your feedback on these vblogs.

Kevin

Hello everybody, I’m posting my first attempt at a video blog entry.  Take a look and see what kind of things I’ll be posting in video form here and please leave your comments, they’re always appreciated. 

Although I blog at least once a week on average, I’ve always been the sort of blogger who spends a lot of time thinking about my blog posts.  In effect, I’ve treated them like small magazine articles in which I write up the blog post, reread it, tech edit it, noodle on it some more, and finally post it.  But that takes a long time.  And, after much thought, I’m not sure that it dramatically improves either the efficacy of the information or is the best use of my time.

So, I’ve been thinking about shifting gears on blogging into the “near Twitter” realm of blogging rapidly and without too much emphasis on clean grammar, well-structure paragraphs, and the like.

What do you think?

Is more content better?  Or is better content more important?

Thanks,

-Kev

Basically, you want to create a distributed query. This is a very common, and relatively simple process from a SQL Server standpoint. From the SQL Server side you can add a linked server then access the external data by using OPENDATASOURCE.

Here’s a good start-to-finish walkthrough entitled Configuring a Linked Microsoft Access Server on SQL 2005 Server that should help in your particular case.

Before I begin, I want to mention that there is a wealth of information on this topic on quest.com including the Don’t Hate - Consolidate and How Do I Know When to Defragment My Database? webcasts, the Tips on SQL Server Consolidation podcast and more.

Consolidation projects should answer the following questions (and this is not intended to be a complete list):

1. What is the deadline for the consolidation?
2. What is involved in the consolidation?
3. What are the performance characteristics of the environments you’re consolidating?
4. What are the growth trends of the environments?
5. What kind of maintenance windows will you have once you’ve consolidated?

What is the deadline for the consolidation? How much time do you have? This information will drive how you proceed. Ideally, you’ll want a minimum of 3-6 months to determine all the characteristics of both the source and the target environments so you can accurately determine the nuances of all of the environments involved. Information is your best friend here, but be aware that the information is only as good as your ability (read: time and resources) to process it.

What is involved in the consolidation? When you look to consolidate SQL Server environments, you have to bear in mind that you’re dealing with more than just the SQL Server instances and their databases. There are always a number of people and applications that rely on an instance of SQL Server, including websites, internal applications, third-party tools, and existing maintenance plans to name a few. The best way to determine what uses the SQL Server is to set a period of time when that SQL Server environment will be monitored to determine exactly who or what uses the SQL Server that will be moved or migrated before the project is undertaken. You’re going to need time to plan for outages, to schedule migrations and to simply communicate the venture to all of the appropriate parties.

What are the performance characteristics of the environments you’re consolidating? If you consolidate high-traffic environments, its possible that you’ll be creating performance bottlenecks where there weren’t any before. Again, your best defense here is to gather enough data about those environments to ascertain these types of issues. Also, what about the hardware on the consolidation target? Having good, thorough performance data that includes the specs of the hardware you were monitoring is critical in defining the subsystem of the consolidation target environment. And crunching numbers is just the first step here - once you have the hardware configured, you’d be well-advised to stress test that environment, preferably using real databases and real application code. There are some good products in the marketplace from Microsoft, Quest and others that will help you simulate user loads using the application SQL you captured during your monitoring period.

What are the growth trends of the environments? Whenever you deal with a SQL Server database, you’ll inevitably have to deal with database growth trends. Are you still allowing data and log files to auto-grow? This could pose a fatal problem in a consolidated environment where it was just a periodic performance-killer in the past. The 3-6 month monitoring period I suggested should also be used to determine, pretty accurately if you’re allowing that much time, how your databases grow. Whether you use complex algorithms or simple linear plotting, having an idea of your consolidated environments disk storage needs will help you order the right hardware and save a lot of time and hassle with Systems Administrators, Storage Administrators and management in the future.

What kind of maintenance windows will you have once you’ve consolidated? Database backups, index maintenance, ensuring up-to-date statistics, and maybe creating data-marts are activities all DBAs are familiar with. But even though a DBA is familiar with maintenance plans, there is usually some degree of superstition or other “don’t touch it unless something breaks” attitude in every environment. Well, when you’re gearing up to perform a consolidation you’d better get familiar with what’s running and when. Conflicting backups are just one of many issues you could run into. The monitoring you’re performing will tell you when different activities are being performed and should expose when certain additional or different maintenance windows will be available. If you realize you won’t have time for maintenance under the current application and environment settings, you’ll need to adjust your consolidation plan because this is definitely a step you can’t ignore. Missing a backup is something every DBA understands the criticality of, but not maintaining indexes or statistics can ultimately lead to decreased performance, throughput and even to shorter maintenance windows.

In summary, you need to plan, monitor, diagnose, and test before you start consolidating environments. Good communication is important throughout the process, both to ensure that everyone that needs to be in the loop is, in fact aware, but also because this is one of the best ways to expose potential problems before they arise. I hope this helps!

There is definitely no shortage of SQL Server information online, but when you’re looking for a specific answer, it helps to know you can trust the information you’re reading. Here’s Paul Randal’s blogroll of SQL Server team members, MVPs and other trustworthy sources you can add to your list of trusted sites to help you get the reliable answers you’re looking for.

If you’ve ever been a production DBA, or if you’ve worked on a mission-critical software application then you’ve come across a situation you don’t know how to handle…but you have to handle it five minutes ago and the clock is ticking. While most queries will turn up a slew of entries using Google (or another search engine to keep it fair), sometimes you just can’t find the answer you’re looking for, or you’re not sure if you can trust what’s been submitted to the forum you’ve landed on.

Enter the warchest: the collection of websites you know you turn to for definitive answers to even your most seemingly niche questions. Here are my favorites:

• SQLServerPedia (I’m not above shameless self-promotion)
• SQL Server Storage Engine
• PSS SQL Server Engineers (Troubleshooting)
  
• Kalen Delaney’s Blog (SQL Server Internals)
• Ken Henderson’s Blog (SQL Server Internals)
  
• Itzik Ben-Gan’s T-SQL Blackbelt SQLMag posts (T-SQL Programming)
• Tips, Tricks and Advice from the SQL Query Processing Team (Query Processing)

9 times out of 10, if you can at least narrow-down your question to one of the groups above (T-SQL, the Storage Engine, Troubleshooting, Internals), they’ll guide you to answers you can trust.