SQL Server Reporter

Get Top Posts and Jobs Weekly via Email:

Subscribe Unsubscribe

476 posts from: Carpe Datum

---

The New World of Work: Unique Tasks

(Indexed 2010-08-26):

In a previous post I mentioned that I’ve seen some Gartner Inc. studies, among others, that state that we’re in a “new world of work”. Since I’ve been working in technology for quite some time, and working around the world for longer, I agree that things are not what they were in the 9-5, Monday through Friday, pre-defined, single-employer world of yesterday. Things are far more dynamic, and include emerging work methods like the “swarms” I mentioned, an...(truncated)...

A combination of crowdsourcing, people helping people, and the best technical community there is

(Indexed 2010-08-19):

I talk a lot about “giving back”. It’s a personal issue with me – I grew up quite poor, and from time to time someone would take notice that my mom and I didn’t have enough to eat, and they would help us out. I’ve never forgotten those folks. So whenever I can, I try to help others out as well. And I’ve found that the folks that work with SQL Server have that same spirit. My good friend Arnie Rowland down in Oregon has started an amazing project. Backed ...(truncated)...

Direct Contact with SQL Server's Future

(Indexed 2010-08-17):

Many times I'm asked "does Microsoft ever ask the users what they want in SQL Server"? Yes, we do. A lot, actually. And you can be a part of that conversation. For starters, you can hit the http://connect.microsoft.com site, read up on how it works, and submit bugs (problems you've found in the software) or features that you want. No, it's not a perfect system, but it's more than I've seen at most software vendors I deal with. You'll be asked do a search (it's a pain, but a necessary one) to see...(truncated)...

Watch those clustered indexes

(Indexed 2010-08-11):

I had a discussion with a friend in Microsoft IT this morning, and as we were evaluating some query plans we ran into more clustered index woes. Remember that clustered indexes affect the base table itself (which is why there can be only one) and are expensive to update. They are fantastic for reading data, but you need to carefully evaluate if they meet the criteria for having an efficient use of this type of index. More here: http://www.sqlskills.com/BLOGS/PAUL/category/Indexes-From-Ev...(tru...(truncated)...

ISV Applications versus In-House Applications

(Indexed 2010-08-10):

I’ve written a series on creating an “Application Profile” for your organization, which allows you to create a strategic plan on everything from Business Continuity to Disaster Recovery, but many of us just don’t have the time to do that much work. But that doesn’t mean you shouldn’t do *something*. There is one very important bit of information to have – the names of the applications that run against your SQL Servers and who wrote them. Versions would ...(truncated)...

Schemas as Security Boundaries

(Indexed 2010-08-03):

There was a question yesterday on Twitter (hashtag #sqlhelp) wondering how to let developers create stored procedures and then grant the rights to those procedures to other people. I believe that question got answered, but it also brought up the subject of Schemas, which I've blogged about before. Schemas can act both as a container and a security boundary. That means you can combine a role and schema in SQL Server to create an "area" or bucket of things you want the developer to have full contr...(truncated)...

Dont mess with the system databases in SQL Server, or Error: 916

(Indexed 2010-08-02):

Note: If you’re reading this more than a few months away from July of 2010, do more research. Never trust an old blog as gospel on anything, including my entries. Always refer to Books Online for the authoritative answer, and if it’s wrong, file a bug against it using the “Feedback” Button. It kinds of goes without saying (so of course I’m saying it) that unless you have a *really* compelling reason to change anything in the system databases you shouldn’t. An...(truncated)...

The Microsoft IT Showcase

(Indexed 2010-07-28):

I had dinner in Seattle (three words: bacon-wrapped-steak) with the folks that put on "How Microsoft Does IT" (get it? Does "it", "IT"? OK, I'm easily amused) and I wanted to point out this fantastic resource to you - again. I rave about it all the time, because before I started at Microsoft I worked at shops large and small, and many times I was called on to come up with strategies and architectures for my organizations. And what better place to start than the people who wrote the software, and...(truncated)...

The TechNet Wiki and Updated Security Checklists

(Indexed 2010-07-27):

You're probably familiar with a Wiki - a document set that anyone can edit. Did you know TechNet (Microsoft's source for technical professionals) has one? And did you know there are lots of folks keeping it up to date? Well, Rick Byham, one of my friends over in the SQL Server group has posted a bunch of security checklists - and you know how much I love checklists! You can go to the Wiki here: http://social.technet.microsoft.com/wiki/ and search for "Checklists", but here's what he's posted to ...(truncated)...

Exploration and Understanding

(Indexed 2010-07-26):

As most of you know, I tend to spend a little time on social networks like Twitter, FaceBook and LinkedIn. On one of those networks, Ken Simmons sent the following thought: “One hour sessions give you great ideas you can explore; full day sessions give you in depth knowledge you can use.” He was talking about the difference in a one-hour session and an all-day event at the Professional Association of SQL Server (PASS) summit. He brings up a great point. Many sessions at a technical ...(truncated)...

When youre asking for help, make sure you explain what youre trying to accomplish

(Indexed 2010-07-22):

At some time, all of us need technical help with something. Whenever you do, make sure you try and frame the question not necessarily in terms of what you want to know, but what you're trying to do. Spending time on thinking through your questions will help you get better answers, and people will appreciate that you're putting some effort into the process, and they are more willing to get help.For instance - let's say yo're stuck on a particular SQL Server Replication issue. You're not sure whet...(truncated)...

Presentation Links: SQL Server Performance Tuning (Quest)

(Indexed 2010-07-21):

I worked with a team of folks brought together by Quest software, and we presented multiple sessions on SQL Server Performance Tuning. Here are the links I mentioned during those presentations: Blitz! SQL Server Takeovers (Brent) You're minding your own business in your corner office - well, no, you're a DBA, so it's just your cubicle - when somebody says, "Did you know about this SQL Server over here?" Suddenly, you have to find out what the server's doing, how it was set up, and whether things...(truncated)...

PowerShell and Extended Properties

(Indexed 2010-07-20):

I use Extended Properties on databases and their objects all the time. They are a great way to include information about the object – I use them for versioning the database, detailing what a column is used for and so on. They can be a little tricky to set, but it’s really not bad once you learn how. Ken Simmons, a SQL Server MVP has a fantastic article here that explains more: http://cybersql.blogspot.com/2010/07/extended-property-aweso...(truncated)... Although it’s not as s...(truncated)...

Side-By-Side Installations What gets changed?

(Indexed 2010-07-15):

I was asked yesterday if it was OK to install SQL Server 2008 R2 on a system that already had SQL Server 2008, but without upgrading. In other words, they wanted to keep both versions of SQL Server running at the different levels. Thats supported; and its called a side-by-side installation. that means youll end up with at least one Named Instance, one at the first version and the other at the newer version. Everything is kept separate. Well, not *everything*. While the engine, databases and all...(truncated)...

Cross-Pollination

(Indexed 2010-07-13):

I was reading this post on J.D. Meier's Blog, which deals with the cloud (I really dislike that term). You might wonder what that has to do with SQL Server, since it isnt specifically about SQL Azure. Ill come back to that in a moment. I play a little music now and then, on the keyboards and with a guitar as well as the mandolin and banjo. Im not very good, although I do play in public each week. I try to get better all the time, but sometimes I hit a wall not in the mechanics of playing like ...(truncated)...

SQL Server Best Practices problem with this Windows Installer Package

(Indexed 2010-07-12):

A Gotcha is a got you or problem when you try to do something. Last week I blogged that I would be installing the Best Practices Analyzer for SQL Server 2008 R2. And I did but not without a little trouble. I ran into an error that said: There is a problem with this Windows Installer Package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor. Since I AM the package vendor, I had to look elsewhere. :) Theres actually already a blog e...(truncated)...

The SQL Server Best Practices Analyzer and Policy Based Management

(Indexed 2010-07-08):

The SQL Server Best Practices Analyzer (BPA) came out for SQL Server 2008 R2 recently, and Ive been asked what the difference is between the BPA and Policy Based Management (PBM) that was introduced in SQL Server 2008. While its true both of these tools can do similar things, each has strengths and weaknesses. The Best Practices Analyzer has a long history, and has various rules that compare settings on a server and provide guidance through some very nice reports. Many of these rules became Po...(truncated)...

Quote of the Day: Travel and its Consequences

(Indexed 2010-07-02):

Im just back from Missouri, and I stayed in the Mark Twain State Park for a week. I visited Florida, MO where Samuel Clemens was born, and Hannibal, MO, where he grew up. Hes a fount of knowledge and wit, and one of my favorite writers. I though it especially fitting that I use this quote for todays QOD: Travel is fatal to prejudice, bigotry, and narrow-mindedness, and many of our people need it sorely on these accounts. Broad, wholesome, charitable views of men and things cannot be acquired b...(truncated)...

The Windows Page File and SQL Server

(Indexed 2010-06-29):

What is the best Page File size for a Windows system running SQL Server? I see this question over and over and I see people answering it incorrectly all the time. Note: Im talking specifically about 64-bit architectures here. The information is different for 32-bit architectures, but Ill blog about that at another time. First, lets start out with defining a few terms. A Page File is simply a file on the hard drive that handles situations where the system wants to move (or page out) sections ...(truncated)...

Quote of the Day: A Credo

(Indexed 2010-06-18):

To live content with small means, to seek elegance rather than luxury, and refinement rather than fashion, to be worthy, not respectable, and wealthy, not rich, to study hard, think quietly, talk gently, act frankly, to listen to stars and birds, to babes and sages, with open heart, to bear all cheerfully, do all bravely, await occasions, hurry never, in a word to let the spiritual, unbidden and unconscious, grow up through the common, this is to be my symphony. William Henry Channing[Image]

Never Bet Against the Impossible

(Indexed 2010-06-15):

My uncle used to say If a man tells you that his car squirts milk in his eye when you lift the hood, dont bet against that. Youll end up with milk in your eye. My friend Allen White tells me this is taken from a play (and was said about playing cards), but I think the sentiment holds, even in database work. I mentioned the other day that you should allow the other person to talk and actively listen before you propose a solution. Well, I saw a consultant bet against the impossible  the othe...(truncated)...

SQL Saturday 43 (Redmond, WA) Review

(Indexed 2010-06-14):

Last Saturday (June 12th) we held a SQL Saturday (more about those here) event in Redmond, Washington. The event was held at the Microsoft campus, at the Mixer in our new location called the Commons. This is a mall-like area that we have on campus, and the Mixer is a large building with lots of meeting rooms, so it made a perfect location for the event. There was a sign to find the parking, and once there they had a sign to show how to get to the building. Since its a secure facility, Greg Lars...(truncated)...

TechEd 2010 Day Three: The Database Designer (Isn't)

(Indexed 2010-06-09):

Yesterday at TechEd 2010 here in New Orleans I worked the front-booth, answering general SQL Server questions for the masses. I was actually a little surprised to find most of the questions I got were from folks that wanted to know more about Stream Insight and Master Data Services. In past conferences I've been asked a lot of "free consulting" questions, about problems folks have had from older products. I don't mind that a bit - in fact, I'm always happy to help in any way I can. But this time...(truncated)...

TechEd 2010 Day Two No SQL Server in Sight

(Indexed 2010-06-08):

Today I worked the booth at TechEd 2010, manning the new “Surface” computer, which is just the coolest object on the planet. After that I didn’t attend a single SQL Server session – instead I’ve been frequenting SharePoint, Microsoft Office, and even the High-Performance Computing sessions. The reason is that I get really high quality SQL Server presentations at PASS, SQL Saturdays, and online from Microsoft and other vendors. While there are SQL Server sessions her...(truncated)...

Back up a single table in SQL Server

(Indexed 2010-06-03):

SQL Server doesnt have an easy way to take a table backup, so I often use the bcp (Bulk Copy Program) to accomplish the same goal. Ive mentioned this before, and someone told me when they tried it they couldnt restore the table ah the dangers of telling people half the information! I should have mentioned that you need to have a format file ready if the table does not exist at the destination. In my case I already had the table, in this persons case they did not. The format file can be used to ...(truncated)...

Find Rules and Defaults using the PowerShell for SQL Server 2008 Provider

(Indexed 2010-06-01):

I ran into an issue the other day where I couldn't set up some features in SQL Server 2008 because they ddon't support the use of Rules or Defaults. Let me explain a little more about that. In older versions of SQL Server, you could decalre a "Rule" or "Default" just like you do with a Table Constraint today. You would then "bind" these rules or defaults to the tables you wanted them to apply to. Sure, there are advantages and disadvantages to this approach, but it certainly isn't standard Data ...(truncated)...

You Might Be a DBA

(Indexed 2010-05-29):

With all apologies to Jeff Foxworthy, I was up late Friday night on a holiday weekend (which translated into T-SQL becomes Maintenance Window) and I got bored in between the two or three minutes I had between clicks. So I started a Twitter meme and it just took off. I havent cleaned these up much, but here, in author order as of Saturday the 29th of May is the list You might be a DBA from around the Twitterverse: buckwoody Your two main enemies are developers and SA...(truncated)...

What are the most effective learning methods?

(Indexed 2010-05-26):

After I got done speaking at the SQL Server 2008 R2 Launch Event yesterday I came back to the hotel room for a web-meeting with some of the other teachers at the University of Washington. As teachers we are always looking to improve the knowledge transfer to our students – and the Program Director found an interesting study that I thought I might share here. Below is an un-labeled chart showing the effectiveness of learning methods according to a recent study. At the top are the labels. (&...(truncated)...

Data-tier Applications in SQL Server 2008 R2

(Indexed 2010-05-25):

I had the privilege of presenting to the Adelaide SQL Server User Group in Australia last evening, and I covered the Data Access Component (DAC) and the Utility Control Point (UCP) from SQL Server 2008 R2. Here are some links from that presentation: Whitepaper: http://msdn.microsoft.com/en-us/library/ff381683.aspx Tutorials: http://msdn.microsoft.com/en-us/library/ee210554(SQL.105).aspx From Visual Studio: http://msdn.microsoft.com/en-us/library/dd193245(VS.100).aspx Restrictions and capabilitie...(truncated)...

More than one way to skin an Audit

(Indexed 2010-05-20):

I get asked quite a bit about auditing in SQL Server. By "audit", people mean everything from tracking logins to finding out exactly who ran a particular SELECT statement. In the really early versions of SQL Server, we didn't have a great story for very granular audits, so lots of workarounds were suggested. As time progressed, more and more audit capabilities were added to the product, and in typical database platform fashion, as we added a feature we didn't often take the others away. So now, ...(truncated)...

More Tables or More Databases?

(Indexed 2010-05-19):

I got an e-mail from someone that has an interesting situation. He has 15,000 customers, and he asks if he should have a database for their data per customer. Without a LOT more data its impossible to say, of course, but there are some general concepts to keep in mind. Whenever youre segmenting data, its all about boundary choices. You have not only boundaries around how big the data will get, but things like how many objects (tables, stored procedures and so on) that will be involved, if there...(truncated)...

Use Those Schemas, People!

(Indexed 2010-05-18):

Database Schemas are just containers they arent users or anything else think of a sub-directory on the hard drive. In early versions of SQL Server we hid schemas, placing all objects under dbo, which gave the erroneous perception that Schemas are users. In SQL Server 2005, we un-hid or re-introduced schemas within the database. Users can have a default schema (a place where their new objects go), you can add new schemas and transfer objects between them, and they have many other benefits. B...(truncated)...

The SQL Server Setup Portal

(Indexed 2010-05-17):

One of the tasks that takes a long time for the data professional is setting up SQL Server. No, it isnt that difficult to slide a DVD in a drive and click Setup but the overall process of planning the hardware and software environment, making decisions for high-availability, security and dozens of other choices can make the process more difficult. And then, of course, there are the inevitable issues that arise. Microsoft supports literally hundreds and even thousands of combinations of hardware...(truncated)...

Do you have a data roadmap?

(Indexed 2010-05-13):

I often visit companies where they asked me What is SQL Servers Roadmap? What they mean is that they want to know where Microsoft is going with our database products. I explain that were expanding not only the capacities in SQL Server but the capabilities were trying to make an information platform, rather than just a data store. But its interesting when I ask the same question back. What is your data roadmap? Most folks are surprised by the question, thinking only about storage and archival. ...(truncated)...

What 5 things should SQL Server get rid of?

(Indexed 2010-05-12):

Ive been tagged by my friend Paul Randal. Its a high-tech way of making someone else do what you want, but since its Paul, well, I guess Im OK with that. Hes asked in his recent blog entry What five things would you get rid of in SQL Server if you were in charge? This is, of course, a delicate issue. After all, I work at Microsoft, so anything I say here might be taken as a criticism that would require action but of course it really doesnt. Interestingly, you may have more to do with what goe...(truncated)...

Performance-Driven Development

(Indexed 2010-05-11):

I was reading a blog yesterday about the evils of SELECT *. The author pointed out that it's almost always a bad idea to use SELECT * for a query, but in the case of SQL Azure (or any cloud database, for that matter) it's especially bad, since you're paying for each transmission that comes down the line. A very good point indeed.This got me to thinking - shouldn't we treat ALL programming that way? In other words, wouldn't it make sense to pretend that we are paying for every chunk of data - a l...(truncated)...

SQL Server PowerShell Provider And PowerShell Version 2 Get-Command Issue

(Indexed 2010-05-10):

The other day I blogged that the version of the SQL Server PowerShell provider (sqlps) follows the version of PowerShell. Thats all goodness, but it has appeared to cause an issue for PowerShell 2.0. the Get-Command PowerShell command-let returns an error (Object reference not set to an instance of an object) if you are using PowerShell 2.0 and sqlps its a known bug, and Im happy to report that it is fixed in SP2 for SQL Server 2008 something that will released soon. You can read more about t...(truncated)...

Book review: Microsoft System Center Enterprise Suite Unleashed

(Indexed 2010-05-06):

I know, I know whats a database guy doing reading a book on System Center? Well, I need it from time to time. System Center is actually a collection of about 7 different products that you can use to manage and monitor your software and hardware, from drive space through Microsoft Office, UNIX systems, and yes, SQL Server. Its that last part I care about the most, and so Ive dealt with Data Protection Manager and System Center Operations Manager (I call it SCOM) in SQL Server. But I wasnt famili...(truncated)...

SQL Server PowerShell Provider follows the Version of PowerShell on the Host and other errata

(Indexed 2010-05-05):

There may be some misunderstanding on how the PowerShell Provider for SQL Server works. Ive written an article or two explaining that you can use PowerShell with SQL Server, without having the SQL Server 2008 (or higher) provider around. After all, PowerShell just uses .NET, and SQL Server Server Management Objects or SMO listen to that interface as well. In SQL Server 2008 and higher we created a MiniShell for PowerShell that gives you the ability to treat a SQL Server Instance as a drive (ca...(truncated)...

Testing and Validation You Really Do Have The Time

(Indexed 2010-05-04):

One of the great advantages in my role as a Technical Specialist here at Microsoft is that I get to work with so many great clients. I get to see their environments and how they use them, and the way they work with SQL Server. Ive been a data professional myself for many years. Over that time Ive worked with many database platforms, lots of client applications, and written a lot of code in many industries. For a while I was also a consultant, so I got to see how other shops did things as well....(truncated)...

But what version is the database now?

(Indexed 2010-05-03):

When you upgrade your system to SQL Server 2008 R2, youll know that the instance is at that version by using the standard commands like SELECT @@VERSION or EXEC xp_msver. My system came back with this info when I typed those: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Developer Edition on Windows NT 6.0 (Build 6002: Service Pack 2) (Hypervisor) Index Name...(truncated)...

SQL Server for the Oracle DBA Links

(Indexed 2010-04-28):

I do a presentation (and a class) called "SQL Server for the Oracle DBA". It's a non-marketing overview that gives you the basics of working with SQL Server if you're already familiar wtih how Oracle works. This class and these links DO NOT help you with "Why should I use Oracle/SQL Server instead of Oracle/SQL Server" - I'll assume you're already there, and if not, there are LOTS of sites to help you make that decision. Although these links might contain slight marketing slants (I don't control...(truncated)...

Data Movement and the Decision Matrix

(Indexed 2010-04-27):

Maybe its my military background, or maybe Ive always had this predilection, but I like to use two devices when I need to make a complex decision: A checklist and a decision matrix. I like to use a checklist because it ensures that I remember the big bits of what I need to do, and brings up questions or areas that I didnt think about when evaluating options for the decision. And the decision matrix thats the thing I use to actually lay out those options. Its simply a spreadsheet-like grid (I u...(truncated)...

Upgrades from Beta or CTP SQL Server Software are NOT Supported

(Indexed 2010-04-26):

As of this writing, SQL Server 2008 R2 has released, and just like every release, I get e-mails and calls from folks with this question: Can I upgrade from Customer Technical Preview (CTP) x or Beta #x or Release Candidate (RC) to the Released to Manufacturing (RTM) version?No.Right up until the last minute, things are changing in the code and you want that to happen. Our internal testing runs right up until the second we lock down for release, and we watch the CTP/RC/Beta reports to make sure ...(truncated)...

Which Edition of SQL Server 2008 R2 should you use?

(Indexed 2010-04-21):

SQL Server 2008 R2 has just released to manufacturing (RTMd) as I write this. With each new release, we make changes to the Editions we offer. A SQL Server version deals with the major changes in the product and edition is a set of features and capabilities within that version (youre welcome). So definitely run over to http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.105).aspx and check out the changes. For instance, did you know that SQL Server Express now supports 10GB databases? Wel...(truncated)...

Create and Track Your Own License Keys with PowerShell

(Indexed 2010-04-15):

SQL Server used to have  cool little tool that would let you track your licenses. Microsoft didnt use it to limit your system or anything, it was just a place on the server where you could put that this system used this license key. I miss those days we dont track that any more, and I want to make sure Im up to date on my licensing, so I made my own. Now, there are a LOT of ways you could do this. You could add an extended property in SQL Server, add a table to a tracking database, use a ...(truncated)...

With Choice Comes Complexity

(Indexed 2010-04-14):

"Complex" may be defined as "Having many steps, details or parts." Many of Microsoft's products, including SQL Server, can be complex. I'm stating what most data professionals already know - there's usually multiple ways to do things in SQL Server. For instance, to import some data into a table you can use graphical tools, SQLCMD, bcp, SQL Server Integration Services, BULK INSERT, even PowerShell, just to name a few tools at your disposal.That's really not the issue, though. The bigger issue is ...(truncated)...

Three Buckets of Knowledge

(Indexed 2010-04-08):

As I learn more and more about SQL Server every day, I divide up my information into three buckets:ConceptsIn the first bucket are the general concepts about the topic. What is it? What does it do (or sometimes, what is is supposed to do?) How does one operation flow to another?For this information I use books, magazine articles and believe it or not Wikipedia. I dont always trust that last source, but I do use it to see how others lay out their thoughts around a concept. I really like graphica...(truncated)...

PowerShell PowerPack Download

(Indexed 2010-04-07):

I read Jeffery Hicks article in this months Redmond Magazine on a new add-in for Windows PowerShell 2.0. Its called the PowerShell Pack and it has a some great new features that I plan to put into place on my production systems as soon as I finished learning and testing them. You can download the pack here if you have PowerShell 2.0. Im having a lot of fun with it, and Ill blog about what Im learning here in the near future, but you should check it out. The only issue I have with it right now i...(truncated)...

Process Improvement and the Data Professional

(Indexed 2010-04-06):

Dont be afraid of that title Im not talking about Six Sigma or anything super-formal here. In many organizations, there are more folks in other IT roles than in the Data Professional area. In other words, there are more developers, system administrators and so on than there are the DBA role. That means we often have more to do than the time we need to do it. And, oddly enough, the first thing that is sacrificed is process improvement the little things we need to do to make the day go faster ...(truncated)...

Your Next IT Job

(Indexed 2010-04-05):

Some data professionals have worked (and plan to work) in the same place for a long time. In organizations large and small, the turnover rate just isnt that high. This has not been my experience. About every 3-5 years Ive changed either roles or companies. That might be due to the IT environment or my personality (or a mix of the two), but the point is that Ive had many roles and worked for many companies large and small throughout my 27+ years in IT. At one point this might have been a detrim...(truncated)...

List SQL Server Instances using the Registry

(Indexed 2010-03-31):

I read this interesting article on using PowerShell and the registry, and thought I would modify his information a bit to list the SQL Server Instances on a box. The interesting thing about listing instances this was is that you can touch remote machines, find the instances when they are off and so on. Anyway, heres the scriptlet I used to find the Instances on my system: $MachineName = '.' $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $MachineName) $regKey= $reg.Ope...(truncated)...

Backup Meta-Data

(Indexed 2010-03-30):

I'm working on a PowerShell script to show me the trending durations of my backup activities. The first thing I need is the data, so I looked at the Standard Reports in SQL Server Management Studio, and found a report that suited my needs, so I pulled out the script that it runs and modified it to this T-SQL Script. A few words here - you need to be in the MSDB database for this to run, and you can add a WHERE clause to limit to a database, timeframe, type of backup, whatever. For that matter, I...(truncated)...

Open the SQL Server Error Log with PowerShell

(Indexed 2010-03-29):

Using the Server Management Objects (SMO) library, you dont even need to have the SQL Server 2008 PowerShell Provider to read the SQL Server Error Logs in fact, you can use regular old everyday PowerShell. Keep in mind you will need the SMO libraries which can be installed separately or by installing the Client Tools from the SQL Server install media. You could search for errors, store a result as a variable, or act on the returned values in some other way. Replace the Machine Name with your ...(truncated)...

Cluster Nodes as RAID Drives

(Indexed 2010-03-25):

I'm unable to sleep tonight so I thought I would push this post out VERY early. When you don't sleep your mind takes interesting turns, which can be a good thing. I was watching a briefing today by a couple of friends as they were talking about various ways to arrange a Windows Server Cluster for SQL Server. I often see an "active" node of a cluster with a "passive" node backing it up. That means one node is working and accepting transactions, and the other is not doing any work but simply "stan...(truncated)...

Today I talk about you

(Indexed 2010-03-24):

Some time back I posted a blog entry (mirrored here and here) asking you how you design databases. Out of those responses, my own experience, studies I read, and interviews I conducted, I collected a wealth of data. Thanks for your responses. So what am I going to do with that information? Well, all along I had planned for that to be used today. I am giving a presentation at an event called TechReady called How Your Customers Design Databases. This is a Microsoft-internal event, where technical...(truncated)...

PowerShell: Read Excel to Create Inserts

(Indexed 2010-03-18):

Im writing a series of articles on how to migrate departmental data into SQL Server. I also hold workshops on the entire process from discovering that the data exists to the modeling process and then how to design the Extract, Transform and Load (ETL) process. Finally I write about (and teach) a few methods on actually moving the data. One of those options is to use PowerShell. There are a lot of ways even with that choice, but the one I show is to read two columns from the spreadsheet and out...(truncated)...

Challenge: Learn One New Thing Today

(Indexed 2010-03-17):

Most of us know that there's a lot to learn. I'm teaching a class this morning, and even on the subject where I'm the "expert" (that word always makes me nervous!) I still have a lot to learn.To learn, sometimes I take a class, read a book, or carve out a large chunk of time so that I can fully grasp the subject. But since I've been working, I really don't have a lot of opportunities to do that. Like you, I'm really busy. So what I've been able to learn is to take just a few moments each day and...(truncated)...

Using linked servers, OPENROWSET and OPENQUERY

(Indexed 2010-03-16):

SQL Server has a few mechanisms to reach out to another server (even another server type) and query data from within a Transact-SQL statement. Among them are a set of stored credentials and information (called a Linked Server), a statement that uses a linked server called called OPENQUERY, another called OPENROWSET, and one called OPENDATASOURCE. This post isnt about those particular functions or statements hit the links for more if youre new to those topics. Im actually more concerned about w...(truncated)...

What to leave when you're leaving

(Indexed 2010-03-15):

There's already a post on this topic - sort of. I read this entry, where the author did a good job on a few steps, but I found that a few other tips might be useful, so if you want to check that one out and then this post, you might be able to put together your own plan for when you leave your job. I once took over the system administrator (of which the Oracle and SQL Server servers were a part) at a mid-sized firm. The outgoing administrator had about a two- week-long scheduled overlap with me,...(truncated)...

System Variables, Stored Procedures or Functions for Meta Data

(Indexed 2010-03-11):

Whenever you want to know something about SQL Servers configuration, whether thats the Instance itself or a database, you have a few options. If you want to know dynamic data, such as how much memory or CPU is consumed or what a particular query is doing, you should be using the Dynamic Management Views (DMVs) that you can read about here: http://msdn.microsoft.com/en-us/library/ms188754.aspx  But if youre looking for how much memory is installed on the server, the version of the Instance...(truncated)...

Create Outlook Appointments from PowerShell

(Indexed 2010-03-10):

I've been toying around with a script to create a special set of calendar objects in Outlook that show when my SQL Server Agent Jobs are scheduled to run. I haven't finished yet, but I thought I would share the part that creates the Outlook Appointments.I have yet to fill a variable with the start and end times, and then loop through that to create the appointments. I'm thinking I'll make the script below into a function, and feed it those variables in a loop. The script below creates a whole ne...(truncated)...

Consolidation Strategy References

(Indexed 2010-03-09):

I have a presentation that I give on SQL Server Consolidation Strategies, and in that presentation I talk about a few links that are useful. Here are some that Ive found feel free to comment on more, or if these links go stale:   Consolidation using SQL Server: http://msdn.microsoft.com/en-us/library/ee692366.aspx SQL Server Consolidation Guidance:  http://msdn.microsoft.com/en-us/library/ee819082.aspx   More referenc...(truncated)...

Its OK to take a Shortcut Sometimes

(Indexed 2010-03-08):

I was working this weekend with a fairly simple Excel spreadsheet, and I had to decompose one cell in it out to three columns in a SQL Server table. There are tools within SQL Server Integration Services (SSIS) that should be able to do that, but I just couldnt find my way around them properly. Im not as familiar with SSIS as I would like to be its just not my day-to-day tool. I was struggling with the split of the cells, since it had several different types and kinds of delimeters for the dat...(truncated)...

PowerShell Version Two Get Continuous Perf Counters

(Indexed 2010-02-25):

In version 2.0 of PowerShell, you can now use a direct command-let (get-Counter) to get at the Performance Monitor counters. For instance, to show the current value of the Processor Percent Time, use this command:  Get-Counter 'Processor(*)% Processor Time'    The interesting part of get-Counter is that you can add a parameter at the end to keep sampling:  Get-Counter 'Processor(*)% Processor Time' - Continuous    Which is very useful if youre doing ...(truncated)...

How to get Microsoft to speak at your event

(Indexed 2010-02-22):

If youre holding a special event, such as a SQL Saturday or a user group, one of the challenges is to get speakers for the event. Now, the best speakers come from the community people who use the product day-in, day-out. They have a wealth of expertise, and many of them are really great presenters. But from time to time you might want to get a Microsoft person to speak at your group or event. Microsoft is a big company, and you can get everything from Marketing (yes, theres a place for that) t...(truncated)...

Code that Writes Code - A Good Idea or Not?

(Indexed 2010-02-16):

Im a big fan of code that writes code most of the time. For instance, whenever you use the templates feature in SQL Server Management Studio (SSMS) or the Maintenance Wizard, youre using code that writes other code. Theres even a trick of writing Transact-SQL (T-SQL) code that in turn creates other code.But there is a class of code that writes code that Im more cautious about. Whenever a program automatically generates a database schema, I begin to get nervous. No, Im not talking about Entity R...(truncated)...

Challenge for the day: Learn One New Visualization

(Indexed 2010-02-15):

Ive gotten hooked on TED-talks. I dont know if youve ever  seen these, but the basic idea is that really smart (and sometimes famous) people have less than 20 minutes to pitch a concept to other really smart people. One of the TED-talks I just watched this weekend was from a statistician, and he did some absolutely fascinating things with data visualization. You can check that talk out here: http://www.ted.com/talks/hans_rosling_shows_the_best_stats_y...(truncated)... It inspired me (an...(truncated)...

The SQL Server Health Check

(Indexed 2010-02-11):

My friend Brent Ozar, who is a top-notch SQL Server Professional, mentioned on his blog (http://www.brentozar.com/archive/2007/04/sql-server-health-...(truncated)... that he brought in Microsoft Support to do a Health Check. There were some questions about what this actually entails so I thought I would post that description here. A SQL Server Health Check from Microsoft is an offering provided through our support and consulting branches. If youve purchased a Premier support agreement, you can...(truncated)...

Start a SQL Server Agent Job using the SQL Server PowerShell Provider

(Indexed 2010-02-10):

Whew!  Thats a mouthfull. Anyway, I thought I would share part of a script I wrote today to help automate (more) from PowerShell. This assumes a few things: that youre doing this from the SQL Server 2008 PowerShell provider (not just good old regular PowerShell) and that you change the HAL9000 and SQL2K8 parts with your server name and your Instance name. Oh, and the Jobs I start are called Test and then something else. Thats the Test* part. You can use a specific name and drop the * or use...(truncated)...

Help for SQL Server

(Indexed 2010-02-09):

Sometimes issues pop up with your system. You need a way to find help, quickly. Heres a few links that might be useful feel free to post a reply to this post with other sources you might know, from web sources to your favorite consultant. Note this list is not exhaustive, and Im happy to add or edit it if you post a reply. I know a lot of SQL Server professionals, and I dont want to leave out anyone or commit them to something!Web Help Use these links to do a little research on your ownMain T...(truncated)...

Multi-Monitor Support in SQl Server Management Studio 2008

(Indexed 2010-02-04):

Did you notice that there are a lot of "little" things that were improved in SQL Server 2008? One of those was done by one of my favorite developers and deals with multi-monitor support. In previous versions of SQL Server Management Studio you could open the tool in multi-monitor and arrange the panes onto mulitple monitors. But when you disconnected the second monitory (as is often done with a laptop) you would "lose" the panes that were moved - SSMS just wasn't aware of those multiple monitors...(truncated)...

How Does Microsoft Do IT?

(Indexed 2010-02-03):

Microsoft is a big company and of course we have a lot of IT infrastructure that we have to manage. It might surprise you to learn that we have an IT group, just like at your company. We have a networking team, a server hardware team, software teams, DBAs, the whole bit. In fact, we have more Mac computers than just about anyone (other than that company down south from here) and we write some of the best-selling Apple software. We have a Linux lab. How do we do that? How do you manage 80,000+...(truncated)...

Transparent Data Encryption and the Latest Data Breach

(Indexed 2010-02-01):

Well, Its happened again. Hundreds of thousands of private records were stolen from a database. This one, however, was different. No one stole any passwords, no one did any social engineering, nothing was captured in-line. No, this one was accomplished by stealing the actual hard drives themselves!  When a thief breaks in and steals hard drives, you can be sure they know what they are after. So even if the company had taken all of the regular authentication precautions and so on, the thiev...(truncated)...

FILESTREAM: Storing Binary Objects in a database or not

(Indexed 2010-01-28):

Many shops need to store binary large objects (sometimes called BLOBS) in a database. There are really only two ways to do this: store in them in a table structure in the database itself using a binary data type, or store them in the operating system in a file folder somewhere and point to the file using a text field in a table. Both of these approaches have issues. Relational Databases arent really designed to hold that much data in a single field not ours, not anybodys. And pointing to a fil...(truncated)...

Data as Strategy

(Indexed 2010-01-27):

Ive created a presentation called Data as Strategy, and in it I cover multiple solutions for various issues that organizations face in dealing with the new world of data management. No longer are we just DBAs or Developers, now were part of the broader business strategy of using data in strategic decisions.   That presentation covers a lot of ground, really quickly, so I thought I would post some of the links I mention during the talk here. Ill actually come back and ...(truncated)...

Attaching a Database

(Indexed 2010-01-26):

I had someone ask me yesterday how they could get to a database used by a product that they owned, but that was installed using SQL Server Express. They didnt have access to the database, and they didnt know the password for the service that started Express, so they wanted to know if they could look at the data. There are a few ways to do this, but the quickest, safest and easiest for me is to attach the database on another Instance of SQL Server. To understand how this works, let me quickly e...(truncated)...

Tracking SQL Server Time

(Indexed 2010-01-25):

In the past few blog posts Ive showed you how to use several methods to track things in SQL Server. You can use the tags to the right of this post here at this site to list things like PowerShell, Performance Tuning and so on. Now that youre armed with these tools, what should you track? Well, one of the items I track is time. I track the time it takes for lots of things, but they fall into three general buckets: Queries Normally I track the five longest running queries with their query pl...(truncated)...

Hovering Over the Titles in Activity Monitor

(Indexed 2010-01-21):

I show this little-known feature in just about every perf tuning class I give, and its often a surprise to most of the folks there. Its not earth-shattering or anything, but I do find it helpful. In SQL Server Management Studio 2008, start the new Activity Monitor. Open one of the Bands, such as Processes. Move your mouse cursor over one of the header titles and leave it there. If that column is getting information from a particular Dynamic Management View (DMV), well tell you where we are gett...(truncated)...

Performance Tuning Methodology Preferences

(Indexed 2010-01-20):

Im holding Performance Tuning Workshops this week for my clients in Washington and Idaho. During that workshop, I explain that there are several ways to examine the SQL Server component of an application. Theres Hardware Component Pressure Measurement, Code Optimization and Index Analysis as starting points, and most folks start there and then move on to use all of them in a mix. But a newer methodology called Wait State Analysis helps you to evaluate what the system is waiting on, so that you c...(truncated)...

The Three Things that Brought Me Here

(Indexed 2010-01-19):

I've been asked to explain three major events that brought me to where I am today. That's a tall order for me, since I view everything as a continuum of multiple small events. But there are some "seminal" events that tend to either show or dictate a further direction, so in the context of working with technology in general and SQL Server in specific, I'll mention a "pivotal" three things that I believe brought me here, working on and with SQL Server.1. It's Spock's fault. Like many - perhaps mos...(truncated)...

Tools and Processes for Fitting it all in

(Indexed 2010-01-18):

Most data professionals Ive met work in two modes: we plan for our day, and we react to the situations around us. Im staring at my list of things that I need to do today right now, which is my planned work. Of course, I have no idea how much of that will really get done its optimistic to be sure. On the other hand I have several systems I manage, and at any moment one of them or the people that interface with them may change state such that I need to give them some attention. So how do I meld ...(truncated)...

Bonus Post The PASS Dakar

(Indexed 2010-01-15):

OK, this will probably never happen, and I have no official affiliation with the Professional Association of SQL Server (PASS) at all, everyone will think is crazy, but at the PASS yearly conference I would really like to see this competition:   Theres a special race called the Dakar Rally, which runs for weeks at a time over some of the most difficult terrain in the world. Hundreds of cars, bikes and trucks (!) start out, only a few finish. People get hurt in this rac...(truncated)...

The Database Design Process

(Indexed 2010-01-14):

I need your help. I know how I create databases, and Ive watched a lot of other data professionals follow their own processes for that, but I want to know how YOU do it. Ive written about the process I follow for a complete database design on InformIT (use the "Next" button at the bottom of these to see them all). Beyond starting with the business requirements and REALLY hammering that out, here is the general outline I use: 1. Pull out the nouns from the business requirements (Client, Firs...(truncated)...

Know Your Product Specifications

(Indexed 2010-01-13):

As the Data Professional in your organization, the rest of the org looks to you to ensure that the system can handle what the business requires. To do that, you need to know two things: what the business requires, and what SQL Server can do. But of course theres a bit more to it than that. Knowing the business side of the requirements well, I teach an entire course on that. But knowing what SQL Server can do is something you can find out on your own. SQL Server comes in versions, which are re...(truncated)...

Plan and Prepare or Just Do It? How about Both!

(Indexed 2010-01-07):

I'm kind of a type "A" person. OK, I'm a VERY type "A" person. I even cook by setting things up ahead of time. I'm definitely more in the "Plan and Prepare" camp than the "Just Do It" camp. But I do realize that there are times when you just can't stop and prepare. Sure, it would be great to know that server is going to melt down just now, but it happened and you have to deal with it. Now is not the time to open the plastic on that "Troubleshooting SQL Server" DVD course you bought! You just hav...(truncated)...

Spit it out already!

(Indexed 2010-01-06):

Youve probably seen that commercial where the chewing-gum company van stalks the guy who has been chewing the same piece of gum too long, and they attack him and make him chew another piece. I feel like that with SQL Server 2000. Almost every shop I go into has at least one primary application running on SQL Server 2000. Now, dont get me wrong SQL Server 2000 is a fine piece of software engineering. From over TEN YEARS AGO. In software time,  thats like a thousand years or something. Wh...(truncated)...

Performance Counters? Theres a script for that.

(Indexed 2010-01-05):

Its not hard to get Performance Monitor counters in PowerShell 1.0, and its REALLY easy in 2.0. This is one I used yesterday to monitor network bytes in and out on 2.0 (keep all of the green lines in this post on the same line) : get-counter -Counter "Network Interface(*)Bytes Total/sec" -SampleInterval 1 -MaxSamples 10   The SampleInterval is in seconds, and the MaxSamples sets how many you want. The Counter parameter is the object and counter you wan...(truncated)...

New Years Goals, not New Years Resolutions

(Indexed 2010-01-04):

Many people use the New Year as a time when they promise themselves to behave differently. They plan to be healthier, eat less, workout more and so on. My family and I have for several years had a similar tradition. We find a restaurant that is open early on New Years day (no small feat) and sit down with a little leather-bound book I write in. In that book we record our New Years goals not resolutions. Its a small change in terminology, but for us, its a larger change in meaning. Goals are bo...(truncated)...

SQL Server Chargeback Strategies

(Indexed 2009-12-29):

It seems the more things change the more they stay the same. One of the things I used to create on the mainframe system when I started years ago was a charge-back system.  It tracked the time and resources used by the employees so that we could charge their department money for the time they spent on the (very expensive) mainframe. When LAN systems came out, IT departments were just charged as a general expense. But these days, with shrinking budgets and deeper scrutiny on how resources...(truncated)...

PowerShell Demo Prompt

(Indexed 2009-12-24):

When I teach I have to set my fonts really big so that folks in the back can see. I show PowerShell from time to time, and people keep asking me about my latest prompt.  Heres what my prompt looks like:   { univacBuck on UNIVAC using AMD64 } --    12/23/2009 7:54:10 PM    -- C:UsersBuck   Theres a blank line under there, and all my text is green from there on. Having the prompt this w...(truncated)...

Its Data Tier Application and Data Application Component

(Indexed 2009-12-23):

OK In SQL Server 2008 R2 we did re-use an acronym or two (DAC and DTA), but its important to remember there are actually two parts to this new feature. One is the Data Application Component (DAC) and the other is the Data Tier Application (DTA). The DAC is the file created for a DTA. In SQL Server 2008R2 and Visual Studio youll find there is a new way to write and transfer database code. Ill blog about it more as I finish my testing, but the process works kind of like this You can birth a Da...(truncated)...

Monitoring SQL Azure For Performance

(Indexed 2009-12-22):

In SQL Server Azure, there are no Dynamic Management Views (DMVs) or Performance Monitor Objects and Counters that you can access, so you cant run your standard performance monitoring that way. I suspect that as time goes on, SQL Azure will have some instrumentation, but for the time being, youll have to go with a different metric round trip throughput. What I mean by that is youll need to measure the start of a transaction and its completion. In the end, this is the only metric that matters a...(truncated)...

More than One Platform

(Indexed 2009-12-21):

Many Data Professionals I deal with work with more than just SQL Server. I came from a background of COBOL flat-file databases, worked with Oracle, DB/2, Ingres, and lots of other databases as well. While I find that the SQL Server professionals I know are fine with learning a new platform, many times Ive run into folks on other platforms that dont want to learn SQL Server. Im not sure why this is. Data is data, and Im all about the right tool for the job. Ive talked with my friends in the Or...(truncated)...

My Perfect SQL Server Management Tool

(Indexed 2009-12-15):

As many of you know, I used to work on the product team on SQL Server Management Studio. I think it does a good job of balancing the needs of a data developer and a data administrator, even though there are things I would like to have done to make it even better. But if I were king, I would love to put in a completely new kind of management tool for SQL Server. Heres what that would look like: It would be in two parts. The first would be a management interface, the second an awesome, small, sl...(truncated)...

Wizards are evil wait, no they arent!

(Indexed 2009-12-14):

SQL Server contains many Wizards. Wizards are simply programs that collect information based on user choices. The Wizards screens explain each step and the choices on that screen. Based on those answers collected from the user, the Wizard performs some task. What could possibly be wrong with helping a user this way? Well, plenty. Wizards hide complexity from the user, which can prevent them from learning the process behind the Wizard. Wizards can also enable the user to choose the wrong options ...(truncated)...

The Dark Sides of Consolidation

(Indexed 2009-12-10):

Consolidation, as it applies to databases, is simply putting more databases or SQL Server Instances on less hardware. This is a good thing, normally, because it allows you to save on hardware costs and use what you have at its highest capacity. It also saves on energy costs, floor and rack space, and in some cases even licensing and software. But there are issues that you need to consider. When more databases or Instances there are on a single server, the higher your risk when that server is un...(truncated)...

SQL Server 2008 does not always script out all tables

(Indexed 2009-12-09):

I teach my college students to periodically script out their databases so that they can run the script to create the entire project on any system. I use this to check their homework, and to teach them about source-code control. We use the scripting wizard in SQL Server Management Studio (SSMS) and select most of the options as we go to get all objects and even the data. But in SQL Server 2008 RTM, you can actually miss a few tables this way. This is corrected in SQL Server 2008 SP1. Its inte...(truncated)...

SQL Server Best Practices: Use Roles When You Can

(Indexed 2009-12-07):

SQL Server has two major security vectors: Principals, which are primarily users and roles (groups), and Securables, which are primarily objects on the server or in the database, like tables or views. Many applications use Logins for their users, and then tie those Instance Logins to Database Users. The Database Users are then given rights and permissions to database objects like tables or stored procedures. If you can, its a good idea to apply permissions not to the individual users, but to da...(truncated)...

SQL Server Best Practices: Protect CmdExec

(Indexed 2009-12-03):

In SQL Server, there are times that you need to do things in the operating system, and to allow that there is a feature called CmdExec. This is not always a good thing whenever you leave the confines of SQL Server and go out to the operating system, you can cause issues, not the least of which are security-related. This best practice is primarily aimed at SQL Server 2000 in SQL Server 2005 and higher, youll have these as job step types in SQL Server Agent (or ActiveX). What you should to do is...(truncated)...

After the Upgrade, it runs differently

(Indexed 2009-12-01):

I got a question yesterday in the mail that I thought I would just answer here in a broad context. While I cant troubleshoot or do performance tuning from a distance, there are some interesting concepts and suggestions this e-mail brings up: I have recently seen a change from SQL Server from 2005 to 2008 in where it handles CASE statements differently. Previously we saw a tremendous improvement in performance by using CASE statements instead of OR statements. However when one of our client upgr...(truncated)...

Arent DBAs Just System Admins for Databases?

(Indexed 2009-11-30):

Last week I ran into an argument Ive had since I left the mainframe space decades ago. A developer told me DBAs dont design databases. The inference was that DBAs (i.e., Database Administrators) only worry about hardware, security, OS, database backups, things like that. He seemed amazed that a DBA would ever do data work. It may be the name. Perhaps the admin part confuses developers. Also, it is true that in some shops, a systems admin does double duty with Windows, SQL Server, and perhaps ev...(truncated)...

The Magical World of SQL Server Licensing

(Indexed 2009-11-26):

Licensing. Sometimes it feels like to you need a specialized degree, a black robe and a secret handshake to really understand it but its not quite that bad. There are several licensing models, from just buying SQL Server off the shelf to Software Assurance, or SA, where you just install to your hearts content and then true up or pay at the end of a certain period. But its best to go to the source. There are three guides I use: This is the simplest guide: http://download.microsoft.com/downlo...(truncated)...

Code that Writes Code

(Indexed 2009-11-25):

I have scripts that re-create my databases for testing and development purposes. But sometimes I want to take the data from a set of tables and move it as well I could use SSIS, or a SELECT INTO statement, but what if I want to re-set the data to a point in time? In other words, load it with some base data? I thought this might be a good place to demonstrate code that writes code. No, it isnt that big of a deal most DBAs know how to do this, but in the interest of those who dont, I thought I...(truncated)...

Changing the Primary Key After You Have Data

(Indexed 2009-11-24):

Yesterday I blogged about changing a Primary Key (PK) during the design phase, and before you have data in the database. Even then, its not trivial to change the data type or column(s) that make(s) up the PK. When you have data in that Primary Key and/or you have Foreign Keys (FK) that point to a PK field, this becomes a much more involved process. First, you MUST take a complete backup of the system, and you MUST do this work on a development system. Youre going to be manipulating base data, a...(truncated)...

Changing the Primary Key Before You Have Data

(Indexed 2009-11-23):

In my class this week, the students went through the inserts, updates and deletes that they had created for their database design, and then the moment of truth creating the queries that answer the questions from the requirements. Its often at this stage when you realize that the design isnt exactly what you want. Perhaps you missed a JOIN condition, maybe you dont have the Declarative Referential Integrity that you need. In a couple of cases in the class, the students had picked a Primary Key ...(truncated)...

Using Perfmon with SQL Server Part Two

(Indexed 2009-11-19):

Yesterday I posted a header from a script that shows you some links for how to use Logman, a Windows command-line utility that can automate your collection of Windows Performance Monitor counters. Towards the bottom, you can see the logman command I use, and you can see that it points to a counter file for the objects and counters I collect on a regular basis. In the script that follows, youll see the counters I collect. There are a LOT of caveats to this script For one, it assumes a Database...(truncated)...

Using Perfmon with SQL Server Part One

(Indexed 2009-11-18):

I held a workshop on performance tuning in Portland, Oregon yesterday, and as part of that I mentioned a process I follow for automatically collecting Performance Monitor Counters for a system. I use this data to store a baseline and then subsequently to have a reference set of data to compare with. I use the logman feature in the operating system, and I had some references there in my script, and I promised I would post the comments block at the top of the script. Tomorrow Ill post how I use th...(truncated)...

Channeling Bruce Lee: Tuning without Tuning

(Indexed 2009-11-17):

Im leading a Performance Tuning workshop here in Portland, Oregon today, and I normally start this kind of workshop with a segment on pre-tuning or, the steps you take so that you dont have to tune as much later. When we set up a system is the greatest opportunity for optimal performance. From buying the right hardware (like using 64-bit architectures) to configuring the drive offset, stripe and block size, to installing the proper version and edition of the Operating System and SQL Server, y...(truncated)...

Color Me Corrected Will the REAL sysobjects please stand up?

(Indexed 2009-11-16):

A few days ago I posted a way to look at the Dynamic Management Views (DMVs) using a query, which I then copy and paste into the Help viewers Index tab to learn more. Well, my good friend and Colleague, Cliff, pointed out that I was using the older sysobjects system table. Well, of course this is a deprecated feature, and not the correct way to reference system objects in newer versions like SQL Server 2005 and higher. I did that because I know a lot of you out there are still on SQL Server 2...(truncated)...

Store XML Document as Binary, Read as XML

(Indexed 2009-11-12):

I had an interesting question the other day, so I thought I would share both the question and the answer. We had an older application that had stored an XML document in a table as a binary data type column. I know, I know weve had an XML data type for some time, but not when this app was written. So of course the tables just moved along with the app, even though there was a better way to do it. The question I got was, can I read the binary field as XML, even though it isnt? The answer is: It...(truncated)...

Create an Excel Graph of your Big Tables with PowerShell!

(Indexed 2009-11-09):

I showed a demo of how to find the top ten tables in the database at the PASS Conference. Heres that script youll need to fix the server name, instance name, and database name. You can use this to display any numbers and even more. The mind reels with the possibilities. This uses the PowerShell provider from SQL Server 2008 (sqlps.exe) but works against 2005 and even 2000 Instances. Oh, you have to have Excel Installed, of course! All the usual caveats apply use a test server, know what your...(truncated)...

Learning New Tech on SQL Server Virtualization From Vendors

(Indexed 2009-11-05):

Im at PASS this year, and one of the things Ive learned this time is that I can learn from the vendors!  Most of the big names are here, like Idera, Redgate, Quest, and more, but I ran into some folks yesterday I hadnt spoken with before XKoto. Im actually surprised I hadnt run into them earlier. I talk with folks all the time about virtualizing, which should really be a conversation around consolidation, and the XKoto folks have an incredible product that handles scale-out in a new way...(truncated)...

DBA? No Data Professionals

(Indexed 2009-11-03):

Im here at the Professional Association for SQL Server (PASS) this week, and Im here in the keynote. A few days ago I ranted about the fact that DBA should be a Data Professional not an administrator. Ive heard a lot of response about that blog and all were in support. And now here at PASS, what is the theme? Taking a strategic approach, and being professional. w00t! Have we turned the corner? Are other folks realizing that you hold the keys to the kingdom when youre the DBA? I think so. Were...(truncated)...

Win7 Install SQL Server Native or go Virtual PC?

(Indexed 2009-11-02):

I have Windows 7 on my laptop, and I also teach, demo and use SQL Server 2005 and SQL server 2008. Should I install this native on my outside operating system or use the Virtual PC (VPC) software that comes with Windows 7? Well, there are arguments for each.Its far simpler to start up SQL Server Management Studio (SSMS) or Business Information Development Studio (BIDS) without having to do anything else on my system. Point: outside operating system. And I can install the developer edition of SQL...(truncated)...

Bonus Post: My PASS Schedule

(Indexed 2009-10-30):

Ill be speaking again at PASS this year, and Im heavily involved in the event when Im not speaking. I would love to meet you if youre a reader of this blog, please stop by and say hello! As of right now, heres wheres Ill be: Tuesday: 11:00 AM - 2:00 PM Ask the Experts Table (Manageability) in the main pavillion Tuesday: 6:00 10:00 PM Customer Appreciation at SQL PASS summit Tuesday: 9:00 11:55 PM The SQL Sentry MVP / VIP Party at PASS 2009 Wednesday: 7:00 - 8:30 AM (breakfast s...(truncated)...

Dont be a DBA Be a Data Professional

(Indexed 2009-10-29):

I get asked quite frequently now about the Cloud technology and how it will affect the job of the DBA. The answer to that is pretty easy: If youre just a DBA, then it will affect you a great deal. The fact that someone else will add users, take backups and worry about uptime might give you great pause. Wait a minute did I just say just a DBA?  Isnt that kind of insulting? Not really. Ive been in a DBA on one platform or another most of my adult life. Its been one of the most rewarding, ex...(truncated)...

Templates for New Query Window

(Indexed 2009-10-28):

When Im in SQL Server Management Studio and I click the New Query button, my Query Window comes up like this: /* .sql Purpose: Author: Last Edited: Instructions: References: */     /* End .sql */ Then I just press CTRL-SHIFT-M and enter the name of the script file, the purpose and so on. I was recently asked how I have that come up every time. Just locate the file called Sqlfile.SQL. Its normally in: C:Program Fil...(truncated)...

How Normal(ized) Should You Be?

(Indexed 2009-10-26):

Normalization is the process of removing repeating values from your data design, and ensuring that the values depend on the Primary Key in the table. OK, its a bit more complicated than that, but this definition will do for this post. So how far DO you normalize? Consider the following: Name, Address, City, State, Zip. You might normalize it to the following: FirstName, MiddleName, LastName, AddressLine1, AddressLine2, CityOrMunicipality, StateOrProvidence, PostalCode. That seems pretty we...(truncated)...

The Fourth Paradigm

(Indexed 2009-10-20):

Microsoft Research published a book based on Dr. Jim Grays research into Computer Science, called The Fourth Paradigm. Its about 300 pages long, and it fascinated me so much I stayed up reading it last night. I have so many notes to decompress it will take me weeks. If youre a data professional, I highly advise that you take some time to read it. The title refers to the Dr. Grays theory on scientific paradigms. The first, called the empirical paradigm, deals with what we can see, feel and touch...(truncated)...

Bonus Post See you in Orlando!

(Indexed 2009-10-15):

Ill be traveling all day tomorrow (Thursday), so no time for a post. I didnt want you to think Id gone offline permanently, so Ill post this tonight. Im flying down to the Holy Land Florida for a weekend of SQL Server. Ill be part of the Orlando SQL Saturday, and were having a special all-day session on Performance Tuning. If youre in town, look us up! http://www.sqlsaturday.com/eventhome.aspx?eventid=32[Image]

Color Your Connections

(Indexed 2009-10-14):

In cyber-space, no one can hear you scream. Have you ever connected to a server, thought it was a testing or development system, and only a split second after you pressed F5 to run that command, realized that it was the production server? Yeah.me neither :). To make sure thats never you, when you start SQL Server Management Studio (SSMS), stop for a moment on the connection dialog and press Options. Youll see that you can select a color for the connection I set mine to red for production serve...(truncated)...

Go To Find Combo

(Indexed 2009-10-13):

A friend of mine Bill Ramos showed me a little-used feature in Management Studio that Ive been using ever since. Its a combo box that works in the Query Window and it allows you to find text quickly in a script, but it also has a hidden benefit. To put this box on your toolbar, right-click anywhere in the grey area of a toolbar. Select Customize and then find the Edit item on the left and left-click that once. Find the Go To Find Combo item on the right, and then click and drag that into any ...(truncated)...

The Much-Maligned Database Designer

(Indexed 2009-10-06):

Theres a feature in SQL Server Management Studio called the Database Designer, and its a strange beast indeed. Its a mix between a graphical database design tool and an Entity Relationship Diagram (ERD). Unfortunately, I dont think it does either one of those things very well. You can find complaints galore on many blog sites, and also on http://connect.microsoft.com. There dont seem to be any plans to change it soon. But I still use it. Why? Well, for one thing its all Ive got!  Sure,...(truncated)...

SQL Saturday Redmond Wrap-Up

(Indexed 2009-10-05):

Wow we had a fantastic time at the Mixer building in Redmond this past Saturday. We had around 300 folks show up, straining the rooms to capacity. We had some top-notch presenters, and a very receptive audience, and we even made it through breakfast and lunch unscathed! Ive sent my two presentations (The DBA Checklist and Resource Governor) and materials to the SQL Saturday folks, so you should be getting an e-mail from them on the location. And look out, Orlando, Im coming your way next! If ...(truncated)...

The ERD as a Communication Tool

(Indexed 2009-10-01):

I teach a college course at the University of Washington, and as part of the class I include a series on creating a using an Entity Relationship Diagram, or ERD. An ERD, as Im sure most of you know, is a series of box and connector shapes that explain how a database is laid out. I was asked recently by a DBA as to whether I really used ERDs, and if they were really effective. I think that they are if they are used properly. An ERD is the way I talk to the business and the developers about the...(truncated)...

SQL Saturday in Redmond Stop By and Say Hello!

(Indexed 2009-10-01):

http://www.sqlsaturday.com/This Saturday Ill be speaking at the SQL Saturday event in my own backyard Redmond, Washington! I really enjoy these events enough to give up a Saturday to come out and join you. No, we dont get paid for these, its all volunteer. We do it because we want to. I have two presentations Ill be delivering. The first is a DBA 101 series talk, called the DBA Checklist. Working with my friend Brad McGeHee (they guy who writes all the DBA books at Redgate software) Ive devel...(truncated)...

Great new Whitepaper on High Availability

(Indexed 2009-09-29):

Paul Randal, he of SQLskills.com fame, has published a new whitepaper on High Availability with SQL Server. If you follow any SQL Server news or blog sites, youve probably already see the announcement for it, but I wanted to mention it here in case you havent seen it yet. There are certain references I save so that when I need to make sure I cover the bases with someone on a topic or I get a question on tell me everything there is to know about X, I can send the link to folks so they can do the...(truncated)...

SQL Server Best Practices: Guard the Backup Files

(Indexed 2009-09-28):

You probably heard me say (along with a lot of other folks) that you need a good recovery strategy, not just a good backup strategy. The thought here is that youre not backing up for its own sake, youre taking the backup in case you need to restore data. Since thats the ultimate goal, you should, as the golfers say, play the shot all the way through, or take your backup strategy to its final result a good restoration. As part of that strategy, you should make sure that you protect the backup f...(truncated)...

SQL Server Best Practices: Set a Fixed Memory Size

(Indexed 2009-09-24):

SQL Server allows you to set a bottom and top range for memory that the Instance will use. The memory will dynamically expand and contract based on Instance need and system pressure. But this flexibility and ease of configuration can (not always, mind you) come at a cost. While there are some caveats, its a good idea to set the memory SQL Server uses to a fixed size, and to set the bottom and top values to the same number. This releases SQL Server from the duty of having to manage the memory, a...(truncated)...

Remember to include the Standard Deviation

(Indexed 2009-09-23):

I do a lot of performance analysis on SQL Server Instances, and I normally start with a series of Performance Counters from both Windows and SQL Server. This gives me the ability to limit what I need to look at by seeing which Hardware and Windows components (and SQL Server-specific  features, such as locks and blocks) are experiencing the most pressure. It isnt the place I stop, but it is the place I start. After I collect the metrics, I put them into (what else) Excel for analysis. Ive a...(truncated)...

Book Review Windows Server 2008 How To

(Indexed 2009-09-21):

In the past couple of weeks Ive posted some articles on how to tune a Windows system for SQL Server. Microsoft SQL Server DBAs are a bit different than a DBA on, say, a mainframe, because we also have to know the operating system we run on pretty well. In fact, most of the DBAs I work with can install and manage a Windows server quite well, thank you. But when a new version of the Operating System comes out at the same time that the new version of SQL Server comes out, I have to prioritize my l...(truncated)...

Opening a Windows Perfmon File in Profiler Part Two

(Indexed 2009-09-16):

In a previous blog entry, I mentioned you could import a Windows Perfmon log into SQL Server Profiler. I received a note that someone was having a problem doing this, and when we looked over his situation, it turned out that he was using a Windows Perfmon binary log, a SQL Server 2008 trace file - and he was running the whole analysis in XP. Im not sure if thats the issue, since Ive converted my last XP machine. There is a fix, however. When you save the data in Perfmon, or at least before you...(truncated)...

Flying today and a discount for a conference

(Indexed 2009-09-15):

Well, Im flying today - down to Arizona to film at the SQL Server Worldwide User Group (SSWUG) online conference I mentioned a while back. And I got special permission to pass this discount code along to my blog readers, so if you want to attend the conference and save even more money, use this code: SPBWUVC09 Heres the link to find out more about the conference: http://vconferenceonline.com/shows/fall09/uvc/. Its only about 200.00 (U.S. dollars) or so, which makes it pretty do-able for mos...(truncated)...

Using Perfmon Data in Profiler

(Indexed 2009-09-14):

In SQL Server 2005 a great new feature was introduced into SQL Server Profiler the ability to import Perfmon data. If youre not familiar with one or both of those tools, SQL Server Profiler is a package included with SQL Server that can watch your systems activities. You add these activities into something called a trace, which is the recording of the activities you want to track. Perfmon is a tool in Windows and called the Windows System Monitor since Windows 2003 and the Reliability and Perf...(truncated)...

SQL Server Best Practices: Separate NIC for Maintenance and Transfers

(Indexed 2009-09-09):

Many people arent aware of how important the Network Interface Card (NIC) in their system really is. If you picture your database as a warehouse, the NIC devices are the doors and bays where stuff comes in and out. Imagine if you had one door, and everyone from employees to customers and deliveries came into that one door! thats whats happening on your SQL Server, right now. Of course, a modern NIC can handle a lot of traffic equivalent to a really big door. But do you really know if it is big...(truncated)...

What to do if your Conference Budget is Cut

(Indexed 2009-09-08):

Money is tight we all know that. One of the first places that companies cut in the budget is training. So what do you do if your boss wont let you go anywhere? You do have options. You can train on your own, or ask them to buy you books. But one of the best forms of learning is from lecture-style presentations, and you normally get those at PASS, TechEd and so on. If theres a user group nearby, you should make the time to go. Recently my friends over PASS held a virtual event. These are becom...(truncated)...

SQL Server Desktop Screen Background

(Indexed 2009-09-07):

I was doing a presentation recently and was asked about the image I use as my background on my desktop. Im happy to share it! Click below for the full size: [Image][Image]

Who do I talk to in Microsoft about

(Indexed 2009-09-03):

Have you ever seen one of us Softies (Microsoft Professionals) at a conference, briefing or on Mount Rainier on a motorcycle and said You work for Microsoft? I need your help on fixing.? Perhaps you heard back from that person, or perhaps you lost touch. Perhaps they knew who you should talk to about the issue, or in some cases their job made finding the right person a lot of work, and the chain broke between your initial conversation and someone closing the loop with you. No, Im not talking abo...(truncated)...

T-SQL Prettifiers

(Indexed 2009-09-02):

OK, I don't think that's even a word, but the synonyms (can a non-word have a synonym?) are code beautifiers, sql formatters, etc. The basic idea is that it's a program that re-formats the text in a Transact-SQL statement using a standard set of rules. They do things like capitalizing keywords or breaking lines and adding tabs. For instance, they turn this:select name, address, city, state, zip, employer fromemployee inner join employers on employee.id = employer.employeeid where name like 'Buck...(truncated)...

Directly Opening a Solution in SQL Server Management Studio

(Indexed 2009-08-31):

I use Solutions a lot in SQL Server Management Studio (SSMS), and you should be aware of this simple tip: If you want to open a Solution without having to open SSMS and then jumping through all the menus, just double-click the solution name in the Windows folder. It will automatically open the Solution and SSMS at the same time.This tip, by the way, also works with any .SQL files, unless you've installed another product that has that file associates with the .SQL extension.[Image]

SQL Server Best Practices: Disk Partition Alignment

(Indexed 2009-08-26):

OK - this one is a little more involved than the other best practices I've posted here. In fact, I'm going to have to send you off to another document at the end of this post to explain the technical background and the exact steps you need to perform, but I still wanted to bring the topic up here for completeness.This has less to do with setting up or altering SQL Server, because it deals with your initial disk setup. Yes, this has to be done when you're prepping the drives, or you'll have to bu...(truncated)...

SQL Server Best Practices: Check and Install the "Best" Drivers

(Indexed 2009-08-24):

Between the hardware in your system and the interaction with the operating system are a set of software programs and code called "drivers". These bits of information have the most impact on how well a particular hardware device works with your system. In fact, drivers are one of the leading causes of a "Blue Screen" error in the Windows operating system. For your SQL Server systems, every driver from the I/O subsystem to the network card can impact functionality or even performance. I've even ha...(truncated)...

Don't PASS on PASS

(Indexed 2009-08-20):

Im speaking at quite a few places in the next few months, from here in Seattle to Florida and Berlin, Germany. I did want to point out the PASS event, although Im sure youve been alerted to it already. Ill be speaking at PASS on everything from Performance Tuning to PowerShell and SQL Server. Its tough to get money for conferences, especially now. But you should try to go meet with your peers, learn new skills, and find out how to make your systems faster and better. But that doesnt always sway...(truncated)...

R2 Installation - Firewall Error Message

(Indexed 2009-08-19):

Well, I started my evaluation yesterday on SQL Server 2008 R2. The install went pretty smoothly on my Virtual Machine, with one minor irritation. I thoughtI would post it here in case you run into it.As I started the process, the Installation Validation step found one issue. When I clicked on that, I got the following error message:[Image]That's great, since I now know there was an issue, but there are two problems here. The first is that there is a link here that I can't click on. C'mon, guys, ...(truncated)...

SQL Server Best Practices: Auto-Create and Auto-Update Statistics Should Be On - Most of the Time

(Indexed 2009-08-18):

SQL Server uses something called the "Query Optimizer" to find the data you're looking for. To do that, there is another mechanism, called "Statistics", which can be created and maintained on columns in a table - even tables that don't have an Index. You can read more about Statistics here, and in general they are a very good thing for performance. You could try to figure out which columns need statistics, but it's often better to let SQL Server do that for you. You can turn on the AUTO_CREATE_S...(truncated)...

The Plan for Evaluating a New Release

(Indexed 2009-08-17):

SQL Server 2008 R2 is now out, and you can get the "Customer Technical Preview" or CTP here: http://technet.microsoft.com/en-us/evalcenter/ee315247.aspxBut it seems like SQL Server 2008 just came out! I'm still installing and upgrading it on the systems I manage. I tend not to focus too much on a new release, but it's foolish, I think, to ignore them until they are out. The organizations I serve depend on me to be aware of the technology in my field, and how it can (or can't) help them do their ...(truncated)...

SQL Server R2's Killer Feature

(Indexed 2009-08-13):

Donald Farmer and I are headed for a smackdown. If you don't know who Donald Farmer is, you probably haven't been to any technical conferences where BI was discussed. He's simply one of the finest minds on the topic that there is. Hius demos are flawless, he is engaging, interesting, and an all around bright guy. But this time, he's gone too far. In a recent Facebook Post, Donald had teh unmittigated gall to insinuate that the BI features are the best new thing in SQL Server 2008 R2, the upcomin...(truncated)...

Table Partitioning isn't the only way to Partition Data

(Indexed 2009-08-12):

When you have very large data sets that reside in a single table, you can use SQL Server's Table Partitioning to "break" the table on logical boundaries like time, and the system handles "putting the data back together" when you query it. It's a great way to gain performance, do maintenance and more.But there are other methods of partitioning the data than just using this feature. Before we had this function I used different tables and programming code to make inserts and reads across multiple t...(truncated)...

SQL Server Blogs Around the World

(Indexed 2009-08-11):

As part of my professional "morning ritual", I always parse through various SQL Server Blogs, so I thought I would share a list of those that I follow regularly. Of course, you need to put THIS blog at the top of the list :) - but these are pretty good too:http://msdn.microsoft.com/en-us/sqlserver/bb671054.aspx [Image]

The Mythical Silver Bullet

(Indexed 2009-08-06):

Have you ever sat in a meeting where you either got the distinct impression, or were told directly that you should just use product X or Feature Y to solve a problem?  Sure, we have a lot of features in SQL Server, but I don't think any of them is a complete solution by themselves. Let's take a few examples that I've run into: High Availability /Disaster Recovery: This is probably the biggest one. All you have to do to have a great HA/DR strategy is to have backups and install a cluster, ...(truncated)...

SQL Server Management Studio Projects and Solutions

(Indexed 2009-08-04):

Inside SQL Server Management Studio (SSMS), there's a feature I don't see used a great deal. It's called a "Project" and in effect it's just a way to bundle files, connections and scripts into a single package that you can navigate within SSMS. This is actually an artifact from the base tool that SSMS uses - Visual Studio. When the product team came out with SQL Server 2005, they decided to use the "shell" from Visual Studio to build a single environment for management tasks and writing code. B...(truncated)...

The SQL Server 2008 Developer Training Kit

(Indexed 2009-08-03):

Want some free training? I think Microsoft does an amazing job of getting training out for free. If you need a boost for your career, are just starting out, or need to re-tool after a layoff, or if you just want to become more familiar with the latest version, you can access these resources to learn more. Here's one that I really like - you get examples, setup for hands-on-labs and more, all free. It also works with the Express Editions of SQL Server 2008 and Visual Studio. Get it here: http:/...(truncated)...

Code Collapse and Expand Feature in SQL Server Management Studio 2008

(Indexed 2009-07-30):

In SQL Server Management Studio 2008, whenever you have a "block" of code (BEGIN....END, etc) SSMS will put a little plus-sign in the margin and allow you to "collapse" that block of code so you only see the BEGIN, not stuff underneath. If you're working with a really large set of code, that's helpful. But I got an e-mail from a friend that said her SSMS didn't do that. I was kind of surprised, since I never had to do anything to make it work. We did a little digging, and come to find out it is...(truncated)...

Commenting your Code

(Indexed 2009-07-29):

You should always add descriptive comments to any code that will live longer than a single event. It's useful not only for the other unfortunate souls blessed with your code, but for yourself, years later, when you say "what was I thinking here?" I've recently had to debug some code and it is IRRITATING to follow without knowing what on earth this person was thinking. Not that I'm upset or anything. I'm just sayin'. At the top of your code, you should have a "Comment Block" that describes what ...(truncated)...

Should I stay or Should I Go?

(Indexed 2009-07-28):

I'm writing this post on a bus headed towards the Seattle Convention Center, where the technical folks in the field meet once a year to hear from the product teams about all the new features coming in the future. I used to present at this conference (and in fact I still do), but today I'll be attending and learning, since I'm not on the product team any more. Which might lead you to ask - "SQL Server 2008 just came out - you're already talking about the next thing?" or, "There's a ton of new st...(truncated)...

Complaining versus Negotiating

(Indexed 2009-07-27):

If you've worked in the IT industry for any length of time, you've probably had someone that is unhappy with your (or your team or company's) services or products. Begin at Microsoft, my audience is pretty wide, so I hear back from a lot of folks that use our products. Some of them tell us they are happy with our work; others are less impressed. It's this last group I want to address, and offer a little advice. If you've faced this before and you're trying to find the best way to deal with an ir...(truncated)...

SQL Server Management Studio Tips and Tricks

(Indexed 2009-07-23):

Are you getting everything out of your SQL Server tools that you can? I’ve given you lots of tricks and tips here on my blog, but nothing beats an on-screen demonstration. My good friend and former teammate Bill Ramos did a session on SQL Server Management Studio at TechEd this year – and you can check it out here: http://blogs.msdn.com/billramo/archive/2009/05/15/dat315-ses...(truncated)...

Latest Updates for SQL Server 2008 are Out

(Indexed 2009-07-22):

There are two new updates that I need to pass along. Hopefully, you have another source for learning about updates, but it never hurts to point out new updates. The first is a Cumulative Update for SQL server 2008 if you have SP1 installed: http://blogs.msdn.com/sqlreleaseservices/archive/2009/07/20/...(truncated)... The second is the Cumulative Update if you have only the RTM (Released to Manufacturing – the initial release of the product): http://blogs.msdn.com/sqlreleaseservices/arc...(truncated)...

Moving the "Debug" Button

(Indexed 2009-07-16):

We made a tremendous amount of improvements in SQL Server Management Studio (SSMS) for SQL Server 2008. Some of them you can see, such as being able to add different columns in Object Explorer Details, and others you can't, such as the speed improvements for loading large sets of objects. One of the features we added was a new T-SQL Debugger. I really like this tool and use it a lot - at first, I ended up using it a lot more than I wanted to. When we made this tool, we assigned it an icon. A gr...(truncated)...

SQL Server Licensing De-Mystified (Sort Of)

(Indexed 2009-07-15):

I once heard that "sort of" is the worst thing you could possibly hear: "you're going to live....sort of". Anyway, many of us struggle with licensing, so I wanted to point you to a resource that has really helped me out - it's a new guide to SQL Server Licensing. You can find it here: http://download.microsoft.com/download/6/F/8/6F84A9FE-1E5C-4...(truncated)...

SQL Server Best Practices: Install the Latest Service Pack after Testing

(Indexed 2009-07-14):

Although this one might seem obvious, I've come into quite a few shops lately that don't have the latest service packs. No software goes out the door flawless - regardless of what a vendor tells you!  Microsoft comes out with three or four kinds of software upgrades to the product: Hotfix - used when you have a specific problem and the support technician tells you to install it Cumulative Update - changes to the product with fixes, security patches and speed enhancements Service...(truncated)...

Read a Web Page from PowerShell, Make a Web Page from a Database Query From PowerShell

(Indexed 2009-07-09):

I presented at the Pacific Northwest SQL Server User Group here in the Seattle area last night, and I was asked at the break about reading a web page in PowerShell, and being able to do something with the data it has. There are actually a few ways to do that, but you should probably start with the net.webclient call - there's a quick overview of that here: http://www.johndcook.com/PowerShellCookbook.html#a20 and more info on it here: http://msdn.microsoft.com/en-us/library/system.net.webclient.....(truncated)...

ODBC Drivers Are Updated in Windows 7

(Indexed 2009-07-08):

One of the first exposures I had to an ODBC driver was NOT a good one - the particular behavior for ANSI NULLS ended up causing some issues in a database application that I had inherited, so I tend to keep my eye on these things. Mind you, it was back in the SQL Server 6.5 days, and I haven't had an issue since then, but I keep an eye on it nonetheless. Windows 7, which is set to release soon, includes a new ODBC driver - 3.80. It gives you several new enhancements which you can read about her...(truncated)...

SQL Server 2008 Books Online Update is Released

(Indexed 2009-07-07):

I try not to post things that others have already discussed, but I make a few exceptions. I'll (re)tell you about security issues and service packs, and I'll also mention when Books Online is updated. I run into so many issues that are already covered in BOL, so I think it's important to track when new content is added or altered. You can find the On-Line version (which is always the latest) here: http://msdn.microsoft.com/en-us/library/ms130214.aspx  You can find what has changed for th...(truncated)...

Consolidation Strategies: Profiling Your Systems

(Indexed 2009-07-06):

Many shops are considering consolidating their SQL Server Instances onto fewer boxes, as I've blogged about before. There are lots of ways to think about consolidation, from placing more databases on one Instance (Database Stacking), putting multiple Instances on one server (Instance Stacking), and using a Hypervisor (Virtualization). But before you do any of that, you should profile the servers and Instances you currently have so that you know what applications are candidates for consolidation....(truncated)...

PowerShell and SQL Server: Script all Tables

(Indexed 2009-07-02):

This is a script that I found/put together/re-arranged that will script out all of the tables from a database - in this case, Adventureworks2008. You need to change the BWOODY1 part to the name of your server, and the SQL2K8 part to your Instance name. You can change the database name as well, of course, and a better method would be to make a function out of this and feed those variables in. There are other ways to do this, but this script illustrates setting some of the scripting options. You...(truncated)...

SQL Server Best Practices: Auto-Shrink Should Be Off

(Indexed 2009-07-01):

SQL Server is one of the easiest databases to maintain because of all of the automatic settings it has, but as I mentioned with Auto-Close, some of them should be left off. The Auto-Shrink setting is another. That might surprise a few people. You might think from the name of this operation, that you would want your databases to automatically reclaim any space they take whenever they can. And if SQL Server will handle that for you, all the more wonderful. But in fact, shrinking a database can ca...(truncated)...

Grid Painting and Database Projects

(Indexed 2009-06-29):

I'm not very good at drawing. My drawings of people and objects with depth usually looks like what your 5th grader does, but not the talented 5th grader. But I found a trick that helps me draw not only depth, but complex things as well. It involves taking a picture you want to duplicate and laying out a grid of lines over it. Then you just draw the same grid on another sheet of paper, and instead of trying to draw the entire person, landscape or horse, you focus on one little box in the origina...(truncated)...

Use PowerShell to Backup All User Databases

(Indexed 2009-06-25):

This script will back up all user databases - you need to change the BWOODY1SQL2K8 part to your serverinstance name, and of course, you should only run this on a test system until you completely understand it. Unfortunately, the SQL Server PowerShell Provider doesn't make this very easy - and there may be a better way to do this once I research it more. Also - this script was adapted from one given to me earlier, and unfortunately I don't recall the source. If this is partly your work, please ...(truncated)...

SQL Server Best Practices: AutoClose Should be Off

(Indexed 2009-06-24):

When SQL Server "opens" a database, resources are assigned to maintaining that state. Memory for locks, buffers, security tokens and so on is assigned, and there is associated CPU and even a little I/O. When connections are made to the database, these resources are ready and waiting. The AutoClose setting dictates how these resources are handled. If this setting is ON, then when the last user connection that disconnects, SQL Server releases all of those resources back to the server. That might s...(truncated)...

Is Your Infrastructure Crumbling?

(Indexed 2009-06-23):

I watched a documentary (of sorts) last night called "Crumbling America" that talked about America's infrastructure issues. They detailed how roads, bridges, electrical grids and water systems are having serious issues and are basically falling apart. I think they were sensationalizing a bit, but it was interesting to note that America spends only 4% of it's income on infrastructure now, down from a high over over three times that amount. Even the "stimulus" plan containing almost a trillion dol...(truncated)...

Help Me Help You

(Indexed 2009-06-22):

Before I start this post, let me say that it is not pointed at any one person - if you've asked me for help recently, this isn't about you! The programming and administration computer disciplines are fairly unique, in my mind, in one interesting way: we help each other a lot. Since the beginning of the DARPANET, which eventually became the Internet, administrators have used e-mail, newsgroups and other methods to chat with each other - "hey, I'm having this issue....anyone know how to handle th...(truncated)...

SQL Server Best Practices: User-Defined Schemas

(Indexed 2009-06-18):

Schemas are a feature that gained wide use in SQL Server 2005. They are essentially containers that allow some objects to be grouped together. Actually, there have always been schemas in SQL Server, it's just that we only exposed one - called "dbo" (short for Database Owner). In SQL Server 2005 and higher, you can now create your own schemas and then put objects there - and you should. Leaving everything in the dbo schema is like putting everything in the root directory of your hard drive. We us...(truncated)...

SQL Server Management Studio - Copy With Headers

(Indexed 2009-06-16):

I'm writing this on the train to Seattle to visit with a customer this morning - I'm thankful for Wireless! Although setting up a wireless network for a train is a really big thing, sometimes it's the smaller things that make us happy. In SQL Server Management Studio (SSMS), you can open a query window and have the results sent to a grid output. Sometimes you want to copy some (or all) of those rows out to another application like Excel or Word. And one of the most frustrating things is that yo...(truncated)...

Moving Tables Between Filegroups

(Indexed 2009-06-15):

I got an e-mail in response to the Best Practices blogs on Filegroups. I've gotten other e-mails similar to this one, so I thought I would answer (or try to) all of them here. Here's the e-mail:   "I recently started a new DBA job.  When I started the company had recently purchased new hardware and had moved their existing SS 2000 db to SS 2008.  But they did not change the file structure (location of data, index, log files) they kept them on the same drive except for the tem...(truncated)...

Bonus Post: Quick, Painless Survey (honest)

(Indexed 2009-06-11):

The SQL Server team has a quick survey they would like you to take. You don't have to sign up, enter your e-mail or anything else - and it's just sever questions!  It's on new features for web database developers, so if you're one of those, bop on over to the link: http://www.surveymonkey.com/s.aspx?sm=9s5sHwoJ97OV0OWt60idJA_3d_3d   [Image]

SQL Server Best Practices: File Layouts (Revisited)

(Indexed 2009-06-11):

In a previous "SQL Server Best Practice" post , I made reference to file layouts and where various file types should go. I got a comment there that I felt deserved another post. The question was: "...7 individual drives? If this is correct do you also...(read more)[Image]

Read the SQL Server Error Log with PowerShell

(Indexed 2009-06-10):

This script uses a native client call, so you can use it on any machine that has PowerShell installed along with the SQL Server client software. As always, only run this script on a test system until you understand what it does, and of course you'll need...(read more)[Image]

Compressed Backups Restore - Faster?

(Indexed 2009-06-09):

There's been some chatter on a few of the newsgroups I subscribe to about backup compression in SQL Server 2008. It seems that some folks are noticing that restoring from a compressed backup actually restores faster than an uncompressed one. This might...(read more)[Image]

Performance Tuning using SQL Server 2008 from SQL Saturday 12

(Indexed 2009-06-08):

I had a great time in Portland, Oregon on Saturday, where I spoke on using the new features in SQL Server 2008 for Performance Tuning. We covered the new Performance Tracking and Tuning tools in the product, but I focused more on the process for performance...(read more)[Image]

SQL Server Saturday #12

(Indexed 2009-06-04):

I'll be speaking in Portland, Oregon this Saturday (the 6th) on Using SQL Server 2008 Tools to Tune SQL Server. If you have a chance, definitely stop by. If you're in another state here in the U.S., make sure you check out this site to find the SQL Saturday...(read more)[Image]

The "Clicks" Paradigm

(Indexed 2009-06-03):

I've been doing a couple of BI discussions lately, and I used a term that I thought was more widely spread - the "clicks" paradigm. It has to do with the way data is pushed out to users - whether that is based on OLAP or OLTP data. A "three...(read more)[Image]

SQL Server Best Practices: File Layouts

(Indexed 2009-06-01):

In a previous post, I mentioned that you should use Filegroups and I talked a little about those. In this post, the best practice I'll point out is where those files should live. As a general rule of thumb, more "spindles" in a database are...(read more)[Image]

SQL Server Best Practices: Create a Recovery Plan

(Indexed 2009-05-27):

That's right - not a "Backup Plan", which most DBA's already have, but a "Recovery Plan". What that means is that you are working towards the end-goal - a recovered system. Your Recovery Plan will certainly contain your backup plan,...(read more)[Image]

Why Use PowerShell with SQL Server?

(Indexed 2009-05-26):

If you've never read the "scripting guys" at Microsoft, they are a hoot. Ed Wilson and Craig Liebendorfer do a fantastic job of writing each week and helping with scripting of all kinds, including PowerShell. Well, this week they appointed me...(read more)[Image]

SQL Server Best Practices: Setting a Default Filegroup

(Indexed 2009-05-21):

When you create a database, you should always create Filegroups to store the data on multiple drives. Don't confuse this with just creating multiple files in a database - that is useful, but not the same. Let's take an example to explain what you want...(read more)[Image]

A Template for Setting and Reading Extended Properties

(Indexed 2009-05-20):

In the SQLCMS project, I show an example of some "base tables" that you can create for the system. Whenever I create a database, or database object, I make use of a feature that's been around for quite awhile - Extended Properties. You can set and read...(read more)[Image]

Creating Reports? I'm a DBA, Jim, not a moon-shuttle pilot!

(Indexed 2009-05-19):

For the SQL Server Central Management System I've been developing , I needed to create a few "dashboard" type reports. I could use just plain old HTML/ASP/ASPX/PHP, but in keeping with the theme of using what I already have available in SQL...(read more)[Image]

Microsoft Excel Functions for SQL Server

(Indexed 2009-05-18):

This is really neat - I read in SQL Server Magazine that a company called WestClinTech created a product that loads in tons of Excel functions into T-SQL. It's basically composed of SQLCLR functions that you load in, covering math, statistics, business...(read more)[Image]

Who is Active - a replacement for sp_who2

(Indexed 2009-05-13):

Most every DBA I know uses sp_who2, an undocumented stored procedure that shows locking, blocking, who is on, what they are doing and so on. At TechEd on Monday (I could only go for one day this time) I met up with my friend Adam Mechanic - a SQL Server...(read more)[Image]

Does the SQL Server Central Management System Replace My "X"?

(Indexed 2009-05-12):

I've gotten a couple of questions on the SQL Server Central Management System ( http://sqlcms.codeplex.com ) that I blogged about yesterday. It's simply a methodology you can use to manage and monitor your SQL Servers using the tools you already have...(read more)[Image]

The SQL Server Central Management System

(Indexed 2009-05-11):

Yesterday I delivered a pre-conference session at TechEd US in Los Angeles. I covered lots of SQL Server 2008 features, but the key was that we ended up putting them all together for a single purpose. Most of us have a way of aggregating the data about...(read more)[Image]

SQL Server Best Practices: Following Best Practices

(Indexed 2009-05-07):

I've worked on several platforms, from Oracle and DB/2 to Sybase and SQL Server. Perhaps it's because SQL Server is so simple to set up, use and manage, but it seems that in this platform I see a lot of problems with people following best practices. In...(read more)[Image]

Exam 70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance - My Notes as of 5/3/09

(Indexed 2009-05-04):

Well, here's where I'm at so far: Requirements: 1. Install SQL Server 2008 and related services - file locations; default paths; service accounts http://msdn.microsoft.com/en-us/library/ms143547.aspx NTFS is required for installation, but if you upgrade...(read more)[Image]

And the Winner is - Get SQL Server Error Messages from PowerShell

(Indexed 2009-04-30):

I spoke yesterday at a large user group meeting, and we had a prize offered for the best PowerShell script. One question that was asked is how to script out Replication Objects using PowerShell - but that question was answered here already, on our very...(read more)[Image]

Master Agent Servers

(Indexed 2009-04-29):

SQL Server has the ability to create a central location where you can push out certain SQL Agent Jobs to run on other servers. This feature is called a MSX/TSX arrangement, and it's been around for a while. In SQL Server 2005, you didn't want to use SQL...(read more)[Image]

Should You Auto-Start SQL Server Services?

(Indexed 2009-04-28):

It's a fairly common practice to set the SQL Server Services (Engine, Agent, OLAP, RS, etc.) to automatically start when the server does. But is this a good idea? I don't set my services to start automatically. If the server is unexpectedly rebooted,...(read more)[Image]

Quickly Locate Text in a Query Window in SSMS

(Indexed 2009-04-27):

Wow - long title.   Anyway, my good friend Bill Ramos and I were chatting just the other day. He's a the guy at Microsoft that took my old responsibilities for SQL Server Management Studio (SSMS), and we were swapping tips and tricks. He showed me...(read more)[Image]

Exam 70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance - My Notes as of 4/21/09

(Indexed 2009-04-21):

In a previous post , I started the process of my study for the 70-432 exam. I'm working on that (as I have time) and I promised to post my notes here. My notes aren't totally inclusive, they are just things I found interesting as I was doing my reading....(read more)[Image]

Listing the Central Management Servers Programmatically

(Indexed 2009-04-20):

I don't normally advocate hitting the system tables in any database with direct queries, preferring instead to use views or functions that the vendor provides. But so far I haven't been able to find the view or function that would return the list of servers...(read more)[Image]

You have a Voice at Microsoft

(Indexed 2009-04-16):

When I was on the product team, I was asked by a few of you from time to time to make a change in SQL Server. Now that I'm in the field, I get asked to "chat with the development team" to make one change or another. But you actually have more...(read more)[Image]

Back to basics - Using another Default Filegroup

(Indexed 2009-04-15):

SQL Server uses the file system on a computer, just like all database platforms. Whenever you create a database, there are always at least two files that are created - one for the data (MDF files) and another for the logging function (LDF files). But...(read more)[Image]

Run a SQL Server Command from PowerShell without the SQL Server Provider

(Indexed 2009-04-13):

Some folks don't have SQL Server 2008 installed - shame on you! If you're in that sad state, you can still run a query against a SQL Server. You will still need the client connection software installed on your system - you'll have that with any 2005 edition...(read more)[Image]

Slipstreaming SQL Server Installations and the SP1 Controversy

(Indexed 2009-04-09):

Yesterday, along with several hundred other folks, I told you that SQL Server 2008 Service Pack 1 was out, and where to download it. I also mentioned that one of the big advantages in it was that you could now do "slipstream" installations....(read more)[Image]

SQL Server 2008 SP1 is Now Available

(Indexed 2009-04-08):

SQL Server 2008 Service Pack 1 is now available for download at the following location: http://www.microsoft.com/downloads/details.aspx?FamilyID=66a...(truncated)... As always, read the release notes before you install...(read more)[Image]

A Web Based Search for Books Online

(Indexed 2009-04-06):

As many of you know, I used to work on documentation like whitepapers and Books Online for SQL Server. One of the main problems I've found with our documentation isn't that there is not enough content - we have over 65,000 pages in BOL alone - but that...(read more)[Image]

Software Alert: ClearTrace

(Indexed 2009-04-02):

I ran across this package today: http://www.cleardata.biz/cleartrace/default.aspx   I'm playing with it now. It basically reads a SQL Server Trace file (from and shows you the longer running processes so you can do more performance tuning. It's a...(read more)[Image]

Exam 70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance - My Notes So Far

(Indexed 2009-03-31):

In a previous post , I started the process of my study for the 70-432 exam. I'm working on that (as I have time) and I promised to post my notes here. My notes aren't totally inclusive, they are just things I found interesting as I was doing my reading....(read more)[Image]

SQL Server Security Links

(Indexed 2009-03-26):

I was asked yesterday about sharing my security links for SQL Server, so I thought I would post those here: Microsoft Security Bulletin Summaries and Webcasts SQL Server 2000 security tools Security checklists – SQL Server 2000 (can be used as a...(read more)[Image]

The Effect of Hardware on SQL Server Performance

(Indexed 2009-03-25):

Many DBAs begin to tune a poorly performing database system at the hardware level. This is not always the best idea - most of the issues I've found in performance are in code, not in hardware components. For instance, if you add a good index, the system...(read more)[Image]

Best Practices Policy Based Management from PowerShell

(Indexed 2009-03-24):

I'm working on my Policies (SQL Server 2008 Policy Based Management) for my environments that I manage. I use them to check the maintenance, show system state, and in some cases, to prevent actions from occurring when I don't want them to. I was asked...(read more)[Image]

Which Edition of SQL Server is Right For Me?

(Indexed 2009-03-23):

If you're planning an installation or an upgrade of your systems, make sure you think about the right edition while you're in the planning process. This includes planning for something that uses SQL Server, such as Sharepoint. As you're aware, there are...(read more)[Image]

Using PowerShell and SMO to list Databases (and other stuff)

(Indexed 2009-03-19):

You don't have to use the new PowerShell Provider for SQL Server 2008 to talk to SQL Server, even for versions from 2000 up. To do that, you'll need the Server Management Objects (SMO) libraries. You can install those separately, but you'll already have...(read more)[Image]

Simplified SQL Server Licensing

(Indexed 2009-03-18):

I run into people all the time that are confused about SQL Server licensing. Now I'm no licensing expert - not by a long shot - and you SHOULD NOT take this post as authoritative in any way. I don't own the licensing story for Microsoft, not even a little....(read more)[Image]

Article on SQL Server Chargeback

(Indexed 2009-03-16):

I posted the first part of the article on SQL Server Chargeback strategies over on InformIT: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=311 The big point to make is that you will have to develop this system yourself, since there...(read more)[Image]

... and yet another way to find SQL Server Instances with PowerShell ...

(Indexed 2009-03-13):

I got another great comment today on the last two posts:   Good article today on searching out SQL servers with PS. The combination of your and Ben's methods work best though. In Ben's example, you have to pull back all the Win32_Service objects...(read more)[Image]

Finding SQL Servers with PowerShell, Part Deux

(Indexed 2009-03-12):

A reader named "Ben" wrote me and said that he looked at my script in PowerShell that finds SQL Server Instances using the WMI provider yesterday and he came up with another method. Here's what he wrote: Hi there, I wasn't able to find a way...(read more)[Image]

Find those rogue SQL Servers in your Enterprise with PowerShell

(Indexed 2009-03-11):

I found a great way to check and see if a server has SQL Server installed on it. It uses “WMI”, or Windows Management Instrumentation and PowerShell. It simply interrogates the Services on a system to see if SQL Server is there, whether it...(read more)[Image]

Logging in to another SQL Server from a PowerShell session

(Indexed 2009-03-09):

Whenever you run the command SQLPS.EXE on a SQL Server 2008 system, or right-click an object and select "Start PowerShell Here" from the menu, you're dropped into the environment on that server. But sometimes you want to connect to a different...(read more)[Image]

Migrations and ROI

(Indexed 2009-03-05):

We've had a lot of folks switching to SQL Server from other platforms lately. They start looking at SQL Server because many of the other vendors are prohibitively expensive, and they also charge for things that come with SQL Server out of the box, like...(read more)[Image]

Real World DBA Episode 20 Help! Im a new DBA (part two)

(Indexed 2009-03-04):

http://edge.technet.com/Media/Real-World-DBA-Episode-20--Hel...(truncated)... This week: In the news, The SQL Server site has been redesigned. In this week’s feature, I’ll give you the final part of a fast introduction to being a DBA....(read more)[Image]

Object Search - Search Only What You Need

(Indexed 2009-02-27):

At the top of the Object Explorer Details (OED) pane in SQL Server Management Studio 2008 there is a box to type in a search term for database objects. You can use the exact name, or use standard T-SQL wildcards to find an object such as a table or a...(read more)[Image]

Exam 70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance

(Indexed 2009-02-25):

Microsoft likes for its people to be certified in as many technologies as possible. Personally, I find certifications useful since they help me focus my study, but I don't think that certification = expert. It's just one part of the puzzle. To that end,...(read more)[Image]

Using PowerShell to Discover Login Failures on SQL Server

(Indexed 2009-02-24):

If you have PowerShell installed (even if it isnt the SQL Server 2008 provider), you can and should check your servers each day to see if you have login failures. This is very useful to help ensure that your server is safe and secure. Whenever someone...(read more)[Image]

SQL Server Chargeback?

(Indexed 2009-02-23):

Whenever economic situations change, the IT department has to adapt. The situation in many shops is that IT is a recurring cost, carried by the entire business. What that means is that your IT department is asked to service the entire organization, no...(read more)[Image]

SQL Server Background Picture

(Indexed 2009-02-19):

I use a particular background in my presentations that I've been asked about a lot. I thought I would share that here: http://blogs.msdn.com/photos/buck_woody_-_msft/images/928675...(truncated)... more)[Image]

Portland User Group

(Indexed 2009-02-12):

I spoke at the Portland User Group in Oregon last night, and what a great group of folks! I spoke for over two hours on the improvements in SQL Server 2008 for the Developer. As I spoke it reminded me that all over the world, people meet to talk about...(read more)[Image]

SQL Server 2008 System Views Poster

(Indexed 2009-01-29):

The new poster is here! The new Poster is here!  Get your's while they are hot: http://www.microsoft.com/downloads/details.aspx?FamilyID=531...(truncated)... more)[Image]

Which Edition is Right for Me?

(Indexed 2009-01-26):

I had a discussion the other day with someone who had some questions about Enterprise Edition of SQL Server 2008. In many cases, I've seen someone not using the "right size" of SQL Server. Many installations really call for Enterprise Edition...(read more)[Image]

Quote of the Day - The Inevitability of Change

(Indexed 2009-01-23):

The last (incredibly long) election was touted as being all about "change". I think with all the news today, people are finding out that not all change is good change - but it is in fact inevitable: "Change is the price of survival"...(read more)[Image]

Free T-SQL Beautifier

(Indexed 2009-01-19):

I do love me some good-looking code. Sure, the syntax has to be there, and the code has to be correct and all that, but I'm old-school, and I think pretty code is easier to read. I'd love to have a "code beautifier" built right in to SQL Server...(read more)[Image]

Top Queries DMV

(Indexed 2009-01-15):

I spoke at the Pacific Northwest SQL Server User Group last night, and we covered Performance tuning. I promised to blog a script that uses the "query_stats" DMV and a CROSS APPLY to find the top queries - there are lots of these on the web, so here's...(read more)[Image]

Service Accounts Redux

(Indexed 2009-01-13):

The other day I made a post that mixed a couple of concepts. I mentioned that you should always use a separate set of Windows accounts for the SQL Server Engine and Agent services. I also mentioned security ramifications. The fact that the SQL Server...(read more)[Image]

Set The Owner Of all Objects Using SQL Server PowerShell Provider

(Indexed 2009-01-12):

I'm going to standardize a bit - or at least try - in this blog. On Monday's I'll share something new that I've learned to do with PowerShell - sometimes with the PowerShell Provider for SQL Server 2008, and sometimes just using PowerShell and the SMO...(read more)[Image]

PowerShell Provider for SQL Server Script of the Day - Script all objects

(Indexed 2009-01-08):

If you're using the PowerShell provider for SQL Server, you can quickly script out all of the objects in a "directory" using a simple method. Just navigate to the directory of objects you're interested in, say the "Views" of a certain...(read more)[Image]

Another Reason to Use A Special Service Account

(Indexed 2009-01-05):

When I'm asked what the least-used feature of SQL Server is, I often have to reply that it is "good security". Many installations take all the default settings, and most use programmatic security rather than the features built in to SQL Server....(read more)[Image]

Using PowerShell And SSMS Registered Servers

(Indexed 2008-12-31):

One of the interesting things about the PowerShell provider for SQL Server is that it is integrated so well into the entire environment. As a case-in-point, you can use the PowerShell provider for SQL Server 2008 to read the registered servers in SQL...(read more)[Image]

Consolidation Strategies

(Indexed 2008-12-25):

Can you run SQL Server on a Virtual Machine? Sure. Should you? Well..that's another story. You most certainly can run SQL Server in a virtualized environment - but in most of the cases that I deal with, simply having more Instances of SQL Server installed...(read more)[Image]

Security Warning for sp_replwritetovarbin

(Indexed 2008-12-23):

There's a new Microsoft security bulletin you should be aware of before you take off on vacation. It isn't an open exploit; you have to be an authenticated user to try it. Not only that, if you have all the latest service packs or SQL Server 2008 you...(read more)[Image]

SQL Server Management Studio Startup Options

(Indexed 2008-12-18):

I get asked all the time if there is a way to start up SQL Server Management Studio (SSMS) using a certain connection, or to go directly to a certain script file. There are many options you can set in the "Tools | Options" menu for startup, but you can...(read more)[Image]

Compliance Whitepaper for SQL Server

(Indexed 2008-12-17):

If you have to deal with any kind of compliance regulattions, you should check out this whitepaper, authored by my good friend JC Cannon. It's a "keeper bookmark": Microsoft SQL Server 2008 Compliance Guide Whitepaper It covers almost as much as the compliance...(read more)[Image]

SQL Server 2005 Service Pack Three is Available

(Indexed 2008-12-16):

Load this after you thoroughly read the release notes - no, seriously, take a little time and do that. And as always, test, test, test! SP3 (Std, Dev, Ent) SP3 Express SP3 Express Advanced SP3 express Toolkit SQL 2005 Feature Pack Refresh December 2008...(read more)[Image]

SQL Server 2005 SP2 Gains CEC EAL4+

(Indexed 2008-12-15):

No, that's not a mis-print - it's SQL Server 2005 SP2 I'm talking about. This criteria is increadibly exacting, and takes a huge investment in time and money from a company (like us) to reach. If you're in an installation that requires this type of certification,...(read more)[Image]

SQL Server Compliance Tools

(Indexed 2008-12-10):

I've been in a couple of firms that were suject to government regulations such as "Sarbanes Oxley" and HIPPA - and it can be a real challenge to put the safeguards in place that they require. In SQL Server 2008, we've included several new features such...(read more)[Image]

Enterprise Policy Management Framework

(Indexed 2008-12-08):

A good friend of mine, Lara Rubbelke, created a great framework for Policy Based Management. It uses a combination of PowerShell and Reporting Services along with PBM to help you track multiple servers, and keep them in compliance. She's posted the whole...(read more)[Image]

SQL Server 2008 Whitepaper Access Point

(Indexed 2008-12-04):

A special site is available to get at the whitepapers for SQL Server 2008. You can find that here: http://www.microsoft.com/sql/2008/learning/whitepapers.mspx . And here are a few of my personal favorites: Performance and Scale http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_p...(truncated)... more)[Image]

Moving from SQL Server 2000 to 2008

(Indexed 2008-12-01):

I've spoken with some folks that are planning to move their enterprise from SQL Server 2000 to 2008. The first question they have is "Is that supported?" - the answer is yes! There is a lot of documentation you can find on the new version, and of course...(read more)[Image]

Quote of the Day - Things of Value

(Indexed 2008-11-28):

We've had a great Thanksgiving data, and in our family we always go around the table and mention what we are thankful for. I thought of this quote Goethe: Nothing should be prized more highly than the value of each day. - Goethe...(read more)[Image]

PowerShell and SQL Server - Use a Text File to Drive A Script

(Indexed 2008-11-27):

Happy Thanksgiving! If you're in the US, we give one day a year (although I'm thankful year-round) to giving thanks for all our blessings. Today I thought I might share a little technique I use to perform an action across a set of objects, using a text...(read more)[Image]

Secure those Laptops

(Indexed 2008-11-25):

If your company uses laptops (and of course they do) make sure that the data on them is secure, especially if you're using replication in SQL Server to store data on them. Here at Microsoft I use Vista on my laptop with Bitlocker - a free, easy-to-use...(read more)[Image]

PASS 2008 Conference - Day Five

(Indexed 2008-11-22):

Today was the final day of the conference, but I had a packed house for our discussion on PowerShell and SQL Server. I wanted to blog about one concept I covered in the demo: Why would I use PowerShell when I have SSIS, stored procs, batch files and other...(read more)[Image]

PASS 2008 Conference - Day Four

(Indexed 2008-11-21):

This morning started bright and early for the Manageability Team (MPU) that I'm a part of. We hosted a breakfast for a select group of Most Valuable Professionals (MVPs) for SQL Server to go over some of the information we presented yesterday in the keynote,...(read more)[Image]

PASS 2008 Conference - Day Three

(Indexed 2008-11-20):

Well, the "official" start of PASS was today, and it opened with a bang! Ted Kummert, the "head guy" of the entire SQL Server product, gave the keynote and he introduced a lot of the new technology we're working towards. First things...(read more)[Image]

PASS 2008 Conference Day One

(Indexed 2008-11-18):

Yeah, Im at another conference but I didnt have to travel far for this one! Im at the Professional Association for SQL Server (PASS) conference here in Seattle, Washington. OK, I did have to take a train and a bus to get to the convention center,...(read more)[Image]

SQL Server Management Studio Tutorial

(Indexed 2008-11-12):

I've had a lot of folks ask me about inlcuding a new feature for SQL Server Management Studio, only to point out that we have it already. That's understandable - SSMS has a lot of features, and if you're not using it all day, it's impossible to learn...(read more)[Image]

SQL Server Express Installation Changes

(Indexed 2008-11-11):

There's a new, streamlined installation package for SQL Server Express that makes it a lot easier to push out to your systems. Make sure you bookmark this link: http://blogs.msdn.com/sqlexpress/archive/2008/11/05/announci...(truncated)... more)[Image]

TechEd Europe 2008 Day Five Policy Based Management

(Indexed 2008-11-10):

Bringing all the concepts I've described in the last few posts into place, you can see the results of what Alexi wants in a Policy. Navigating to the Management node in SQL Server Management Studio, he can create a new Policy, give it a name, and since...(read more)[Image]

SQL Serer 2005 - Script Tables and Data

(Indexed 2008-10-30):

I was asked about the "DUMP" command in MySQL yesterday. It will create a script (in order) of all the database objects and the data they contain. The question was, does SQL Server 2005 have anything like that? Why yes it does! Check out this...(read more)[Image]

Mandelbrots and SQL Server

(Indexed 2008-10-29):

I was watching Nova last night and they talked to Benoit Mandelbrot, the man who codified some of the math that bears his name. This math branch deals with "rough" or repeating surfaces. You probably already know that Transact-SQL can be used...(read more)[Image]

Opening SQL Server Profiler from SSMS

(Indexed 2008-10-28):

The new Activity Monitor has another trick up its sleeve: If you open it and then expand the first band of information just below the four graphs, you'll see a list of processes that you can order, sort and filter. If you right-click any process, you'll...(read more)[Image]

More SQL Server Videos

(Indexed 2008-10-27):

I was sent this link by a reader: http://www.sqlservervideos.com/series/ Not sure about the content, but I thought I would pass these along. If you review them, let us know what you think....(read more)[Image]

SQLH2 on Codeplex

(Indexed 2008-10-22):

Recently someone asked me if they could get the source to SQLH2, the "health" monitoring solution for SQL Server. I told them that I couldn't give out source code, but Microsoft can. I said "make the suggestion on http://connect.microsoft.com and see...(read more)[Image]

SQL Server 2008 Feature Pack

(Indexed 2008-10-21):

This is to let you know that the SQL Server 2008 Feature Pack for October is now available. Whats in this release? Find more details here : ADOMD.Net AMO DMViewer PowerShell extensions for SQL Server Replication Management Objects Report Builder...(read more)[Image]

Free SQL Server Videos

(Indexed 2008-10-16):

Microsoft is now including video content along with Books Online for SQL Server. Check them out here: http://msdn.microsoft.com/en-us/library/cc952928.aspx...(read more)[Image]

SQL Server Grows Up

(Indexed 2008-10-13):

I spoke at the SQL Saturday event in Olympia, Washington, and I noticed that the questions I got were different than those I used to get for SQL Server. In my former life I managed not only SQL Server but other platforms as well. When I would speak to...(read more)[Image]

Editing Data from SSMS

(Indexed 2008-10-09):

Sometimes you want to edit data directly in a table (often a very bad idea!). In Enterprise Manager (SQL2K) and SQL Server Management Studio (SQL2K5) you right-click a table and select "Open Table" from the menu. That brings back ALL of the rows in the...(read more)[Image]

Spreading the Security Wealth

(Indexed 2008-10-08):

When I first started at Microsoft, I worked a couple of projects with the SQL Server Security team - and I really enjoyed that group. They are a very smart, fun group of folks to hang around with. Plus, they are really good at math! Anyway, they wrote...(read more)[Image]

SQL PASS Pre-Conference Presentation

(Indexed 2008-10-07):

I'll be presenting at lots of conferences soon - SQL Saturday in Olympia Washington, several webcasts, TechEd Brazil and then TechEd Europe in Barcelona. But I'll also be presenting at one of the biggest SQL-Geekfests there is - SQL PASS. I'll be doing...(read more)[Image]

Is PowerShell Necessary?

(Indexed 2008-10-03):

We got an e-mail the other day from a DBA who said he didn’t see the need for PowerShell for SQL Server. He thought our time might have been spent better elsewhere. I’ll restate the reply I gave him below, but it brings up an interesting point...(read more)[Image]

Books Online is updated for SQL Server 2008

(Indexed 2008-10-01):

Wow - out only a couple of months and already the Books are updated! Here you go: http://www.microsoft.com/downloads/details.aspx?FamilyId=765...(truncated)... more)[Image]

Great new site for SQL Server 2008

(Indexed 2008-09-30):

We had our “SQL Server 2008 Experience” party yesterday, and this evening I saw that they’ve launched a new site for the product – check it out here: http://www.microsoft.com/sql/experience/...(read more)[Image]

The Quickest Route to a Custom Collector

(Indexed 2008-09-24):

The Data Collector feature in SQL Server 2008 is a new way to automate the gathering of any data from SQL Server you want to store historically. Combine that with the Management Data Warehouse and Custom reports, and you have a powerful monitoring and...(read more)[Image]

CU1 for SQL Server 2008 is now Available!

(Indexed 2008-09-23):

Many people wait for the “first” service pack of a product to work with it. Well, the CU#1 for SQL Server 2008 is now available. This is a new service model where we get out fixes far faster than a full Service Pack. You should read the article...(read more)[Image]

What is SQL Server 2008 Web edition?

(Indexed 2008-09-18):

You may have see a new SKU of SQL Server 2008 called the Web Edition. Heres the scoop on it: SQL Server 2008 Web Edition enables hosting companies to provide low cost , highly scalable hosting for developers, SMBs, and consumers, all with a low monthly...(read more)[Image]

PowerShell for SQL Server Sample 1

(Indexed 2008-09-16):

I'm experimenting more with the PowerShell Provider for SQL Server (SQLPS) and I wanted to make sure you knew that there are actually two ways to get to it: 1. Type SQLPS.EXE at a command prompt, or right-click any object in SSMS and select "Start...(read more)[Image]

Virtual Conferences and LiveMeeting, well, live meetings

(Indexed 2008-09-15):

I've been doing a few "virtual" presentations lately. We have a lot of requests to present the new features of SQL Server 2008, and I've found that I can go to more places if I do them over the web, using LiveMeeting.  It's actually working...(read more)[Image]

A move with a view

(Indexed 2008-09-11):

With new features, there are new things to consider when you’re moving from SQL Server Edition to SQL Server Edition. For instance, certain database operations (such as Transparent Data Encryption)  make changes to the file layouts that make...(read more)[Image]

New Options in SQL Server Management Studio

(Indexed 2008-09-10):

If you open the "Tools | Options" menu bar item, you'll find that we've made a few changes. First, you can set new Startup options in the "General" section. My favorite is to start with the new Activity Monitor showing. Another change is in the "Text...(read more)[Image]

Music and Technology

(Indexed 2008-09-09):

I've noticed something interesting since I've been working at Microsoft, specifically at the SQL Server organization. All of the folks here are incredibly smart - you'd probably expect that - but they have two other traits that I've found. Many of them...(read more)[Image]

SQL Server 2008 Object Search

(Indexed 2008-09-04):

At the top of the Object Explorer Details (OED) panel, which you can show by pressing F7 in SQL Server Management Studio, you'll find a "Search" text box. If you want to find an object in a particular database, just click on any database object...(read more)[Image]

Selecting Columns to Display in SSMS

(Indexed 2008-09-02):

In SQL Server Management Studio (SSMS) 2008, you can now select columns of properties that you want to display in the Object Explorer Details (OED) panel. You can show the Object Explorer Details by pressing F7, and then you can right-click in the headers...(read more)[Image]

Drag and Drop

(Indexed 2008-09-01):

There's a feature that has been in SQL Server Management Studio since 2005, but I've spoken with many people who didn't know it was there. If you show the Object Explorer (OE), and then open a Query Window, you can left-click and hold on an object name...(read more)[Image]

Copy with Headers

(Indexed 2008-08-29):

When you run a query in SQL Server Management Studio (SSMS), by default the results are sent to a grid. You can select the rows in this grid by clicking and dragging on the rows themselves and copy them to the clipboard with CTRL-C or using a right-click...(read more)[Image]

Backup those Keys

(Indexed 2008-08-28):

I'm working on a Policy that will expose a particularly thorny issue. In SQL Server 2008, you can use a new feature called Transparent Data Encryption (TDE). This feature encrypts the entire database, so you don't have to change your application at all....(read more)[Image]

Support for SQL Server on Hyper-V

(Indexed 2008-08-27):

Many of us use SQL Server in a virtual environment, but as you know it's been difficult to find out what the support policy is for that, depending on the Virtual Environment. Well, for Hyper-V, there's no question: It has support. To find out exactly...(read more)[Image]

MOM 2005 Management Pack for SQL Server 2008 is Available

(Indexed 2008-08-25):

We've completed the work on the Microsoft Operations Manager (MOM) "Management Pack" for SQL Server 2008. MOM allows you to monitor and manage Microsoft and even other vendor's servers, so if you have this software in your shop you can download...(read more)[Image]

"Katmai" test statistics:

(Indexed 2008-08-21):

I thought you might be interested in a few statistics about the latest release of SQL Server 2008. We still refer to our products with the code names, so you'll still hear us refer to 2K8 as "Katmai". Every day we used over 4000 lab machines to execute...(read more)[Image]

More SQL Server 2008 and Visual Studio Service Pack 1

(Indexed 2008-08-20):

OK - I got even more questions about the Visual Studio SP1/SQL Server 2008 installation that I mentioned yesterday. To help clear that up, I'll just point you to a great rundown on the entire situation: http://blogs.msdn.com/psssql/archive/2008/08/11/sql-server-2...(truncated)... more)[Image]

Visual Studio and SQL Server 2008

(Indexed 2008-08-19):

There have been some questions from folks who have pre-released versions of Visual Studio 2008 Service Pack 1. SQL Server 2008 requires the RTM version, so if you have the pre-released version, you can go here to update it:  http://www.microsoft.com/downloads/details.aspx?FamilyId=FBE...(truncated)... more)[Image]

Importing SQL Server 2005 Registered Servers to SQL Server 2008

(Indexed 2008-08-18):

The team here discovered something that I thought we should share. I'll get a Knowledge Base (KB) article out soon so that you have something more "official", but I wanted to get this out as soon as I could. A user wrote us and said that when...(read more)[Image]

SQL Server 2008 Postage Stamps

(Indexed 2008-08-14):

One of the hottest items at the U.S, TechEd 2008 conference was the set of official U.S. postage stamps that have the new SQL Server 2008 logo on them. If you weren't there to get yours, you can now order some, for a limited time. Here's the link, along...(read more)[Image]

The hidden browser

(Indexed 2008-08-13):

Did you know there is a full IE browser inside SQL Server Management Studio? Just click on "View", then "Other Windows" then "Web Browser". I use this to hit a server that has a report of various server's status....(read more)[Image]

Express Basic (2008) and Compact are here!

(Indexed 2008-08-12):

SQL Server 2008 Express (basic) and  SQL Server Compact 3.5 SP1 are now live on the Microsoft Download Center. The English link for SQL Server 2008 Express (basic) is: http://www.microsoft.com/downloads/details.aspx?FamilyId=58C...(truncated)... more)[Image]

Checking the "Feedback" Box

(Indexed 2008-08-11):

If you're installing SQL Server 2008, you'll get a panel near the end of the installation process that asks you if we can collect data on how you use the product. Check those boxes! I was with someone one time during their installation, and when that...(read more)[Image]

Quick Ramp-Up on SQL Server 2008 for the DBA

(Indexed 2008-08-08):

Need to find out what's new in SQL Server 2008 for the DBA, even before you install it? We have a free on-line lab you can register for that will get you started quickly: http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?E...(truncated)... more)[Image]

Learn to Install and Upgrade to SQL Server 2008

(Indexed 2008-08-07):

As I mentioned yesterday, we've shipped SQL Server 2008. I highly recommend you get it and install it on your testing machines right away. But before you do anything on your production systems, make sure you check out this free webcast: http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?E...(truncated)... more)[Image]

News Flash: SQL Server 2008 is here

(Indexed 2008-08-06):

You can find it on MSDN Subscriber Downloads and TechNet Subscriber Downloads . Either search for SQL Server 2008 or navigate under Servers then to SQL Server 2008. http://msdn.microsoft.com/subscriptions/downloads/default.aspx http://technet.microsoft.com/subscriptions/downloads/default...(truncated)... more)[Image]

Managing Other Platforms

(Indexed 2008-08-05):

We often get asked if we will ever create a tool that will manage not only SQL Server but other vendor's systems. I understand the desire - when I was a DBA I had several systems that I managed, and I wanted a tool that would do it all as well. I even...(read more)[Image]

SQL Server 2008 Improvements Practicals Y Mucho Mas

(Indexed 2008-08-04):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for midnight - "Eine kleine Nachtmusik ": I've shown you lots of new features that save you precious time at the office....(read more)[Image]

PHP Driver Update

(Indexed 2008-07-31):

I've mentioned this driver for SQL Server before, but in case you have any boxes out there that you're using PHP on, make sure you check out this update. Here's the announcement, straight from the team: Version 1.0 RTM of the SQL Server 2005 Driver for...(read more)[Image]

DATAllegro Acquisition

(Indexed 2008-07-25):

You may have seen the announcements yesterday, but in case you haven’t, we had a pretty big acquisition announced at Microsoft yesterday that is great news for SQL Server. DATAllegro Inc. , a provider of data warehouse appliances. DATAllegro specializes...(read more)[Image]

SQL Server 2008 Improvements Practicals The Data Collector and the Management data Warehouse

(Indexed 2008-07-24):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 4:00pm - "Need for Speed": Now its time to tune my systems. Before I can tune them, I need to track them. Sure, I...(read more)[Image]

SQL Server 2008 Improvements Practicals New Wizards and Dialogs

(Indexed 2008-07-23):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 1:00pm - "New and Shiny": There are a few features in SQL Server that can be complex to implement....(read more)[Image]

SQL Server 2008 Improvements Practicals Object Search

(Indexed 2008-07-22):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 1:00pm - "New and Shiny": Sometimes it can be, well, a bit challenging to locate the objects you want to work on in...(read more)[Image]

Velocity

(Indexed 2008-07-21):

If you attended TechEd Developers week you may have encountered the of the SQL Server Data Programmability team were announcing the Microsoft Sync Framework , or Project “Velocity” .  As a DBA or developer, you’re likely to run...(read more)[Image]

Free "Basic Training" for SQL Server

(Indexed 2008-07-17):

I answer a lot of questions about SQL Server - on forums, in newsgroups, in presentations and so on - and many are "101", or basic questions. That's great! I don't have a problem with that at all - I enjoy helping someone that is new to SQL Server learn...(read more)[Image]

Is Multi-Server Query Parallelized? Yes and no...

(Indexed 2008-07-16):

I was asked about the post I did on the multi-server query feature we put in SQL Server Management Studio 2008. Because it seems to render on the screen in a sequential fashion, the user thought the queries were issued one at a time from the Registered...(read more)[Image]

SQL Server 2008 Improvements Practicals - T-SQL Debugger

(Indexed 2008-07-15):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 1:00pm - "New and Shiny": With the stored procedure I created ready to go, I need to test it - in case it's long or...(read more)[Image]

SQL Server 2008 Improvements Practicals - Intellisense

(Indexed 2008-07-14):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 1:00pm - "New and Shiny": In my DBA day I finally get a break to do something I really enjoy - new development. I decide...(read more)[Image]

Real World DBA Episode 18 Connecting to SQL Server

(Indexed 2008-07-10):

http://edge.technet.com/Media/Real-World-DBA-Episode-18-Conn...(truncated)... This week: In the news, the SQL Server 2008 Feature Pack for RC0 is now available. In this weeks feature, well talk about accessing SQL Server. The web link is on deciding...(read more)[Image]

Security Bulletin for SQL Server 2005

(Indexed 2008-07-09):

You might notice that I blog quite often about security here, even though I'm on the management team. The reason is that over the years as a DBA I've seen so many shops that don't take care of the basics, so I think you just can't talk about it enough....(read more)[Image]

SQL Server 2008 Improvements Practicals - Resource Governor

(Indexed 2008-07-08):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 10:00am - "Panic Button": While I'm examining the state of performance on my system, I discover that one set of queries...(read more)[Image]

The Surface Area Configuration Tool is dead - Long Live PBM!

(Indexed 2008-07-07):

If you're installing the latest release of SQL Server 2008, you'll probably immediately start looking for the Surface Area Configuration (SAC) tool. It's been removed, and replaced with new Policies that we ship with the Policy Based Management feature....(read more)[Image]

SQL Server 2008 Improvements Practicals - PowerShell - Activity Monitor

(Indexed 2008-07-03):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 10:00am - "Panic Button": All of us get called from our cubes to come take a look at a performance problem. You've...(read more)[Image]

SQL Server 2008 Feature Pack RC0

(Indexed 2008-07-02):

The SQL Server 2008 Feature Pack for RC0 is now live - if you haven't downloaded this and played with it yet, shame on you! :) OK - just kidding - it's not like you don't have a day job. However, if you'll take a few moments and look this download over...(read more)[Image]

SQL Server 2008 Improvements Practicals - PowerShell - Run a "Panic" Backup

(Indexed 2008-06-30):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 10:00am - "Panic Button": Now that I've found there are a few databases that haven't been backed up, I want to run...(read more)[Image]

Real World DBA Episode 17 - SQL Server Features Encryption

(Indexed 2008-06-27):

http://edge.technet.com/Media/Real-World-DBA-Episode-16-SQL-...(truncated)... This week: In the news, CU 8 is now available for SQL Server 2005. In this weeks feature, well continue the introduction to SQL Server Features and talk about SQL...(read more)[Image]

Stopping SQL Injection in it's Tracks

(Indexed 2008-06-25):

I will be getting back to the "Day in the Life of the DBA" series of posts, but I got this from the security folks today and thought I would share it: Today the MSRC in collaboration with SQL Server, IIS, and Hewlett Packard published Microsoft Security...(read more)[Image]

Stopping SQL Injection in its Tracks

(Indexed 2008-06-25):

I will be getting back to the "Day in the Life of the DBA" series of posts, but I got this from the security folks today and thought I would share it: Today the MSRC in collaboration with SQL Server, IIS, and Hewlett Packard published Microsoft Security...(read more)[Image]

Real World DBA Episode 16 - SQL Server Features Web Services

(Indexed 2008-06-23):

http://edge.technet.com/Media/Real-World-DBA-Episode-16-SQL-...(truncated)... This week: In the news, the SQL Server 2008 Release Candidate is now available. In this weeks feature, well continue the introduction to SQL Server Features and...(read more)[Image]

SQL Server 2008 Improvements Practicals - Policy based Management - Check Database Backups

(Indexed 2008-06-20):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 10:00am - "Panic Button": Now that I've run my morning checks, I found a couple of databases that were in Simple recovery...(read more)[Image]

SQL Server 2008 Improvements Practicals - New Performance Monitor Counters

(Indexed 2008-06-19):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 8:00am - "Good Morning Sunshine": We've included several new Performance Monitor (more accurately called System Monitor)...(read more)[Image]

SQL Server 2008 Improvements Practicals - PowerShell Log Checks

(Indexed 2008-06-18):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 8:00am - "Good Morning Sunshine": With the new PowerShell provider, I have access to lots of things in SQL Server natively....(read more)[Image]

SQL Server 2008 Improvements Practicals - Object Explorer Details

(Indexed 2008-06-17):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 8:00am - "Good Morning Sunshine": In Object Explorer Details (OED), we've completely re-written the panel that shows...(read more)[Image]

SQL Server 2008 Improvements Practicals - Connection Dialogs, Central Management Servers, and Multi-Server Queries

(Indexed 2008-06-16):

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's what I showed for 8:00am - "Good Morning Sunshine": The first enhancement is the new options for strings that you can add in the connection dialogs....(read more)[Image]

TechEd 2008 Day Three

(Indexed 2008-06-12):

This morning I sat in on a panel discussing automation for SQL Server. Taking what we've learned about DBA's doing multiple jobs, we had a great set of questions around the best way to automate manually created maintenance plans and integrate them with...(read more)[Image]

TechEd Day One (part deux)

(Indexed 2008-06-10):

Well, I'm here at TechEd after a restful night's sleep and I've already been able to sepak with tons of people about SQL Server. The primary questions I'm getting are: I've got SQL Server 2000 installed - can I upgrade that to 2008? Why would I upgrade...(read more)[Image]

SQL Server 2008 RC0 is Available!

(Indexed 2008-06-09):

If you're an MSDN member, you can download the Release Candidate for SQL Server 2008 - this is a feature-complete release. Learn more and download it at: http://blogs.technet.com/dataplatforminsider/archive/2008/06...(truncated)... more)[Image]

Real World DBA Episode 15 - SQL Server Features Service Broker

(Indexed 2008-06-06):

http://edge.technet.com/Media/Real-World-DBA-Episode-15-SQL-...(truncated)... This week: In the news, SQL Injection Attacks are on the rise. Learn what you can do to protect your data. In this weeks feature, well continue the introduction...(read more)[Image]

Project Velocity

(Indexed 2008-06-05):

The SQL Server Data Programmability team are at TechEd Developers this week announcing news around the Microsoft Sync Framework and what we calls Project Velocity . Velocity is a distributed in-memory application cache platform for developing scalable,...(read more)[Image]

PHP Driver for 2005 is available

(Indexed 2008-06-03):

You can now download our CTP release of the new PHP driver for SQL Server and explore the sample application described in the .chm file. If you'd prefer to access the documentation on-line, it's available on the MSDN site . We welcome your feedback at...(read more)[Image]

Real World DBA Episode 14 - SQL Server Features Full Text Indexing

(Indexed 2008-06-02):

http://edge.technet.com/Media/Real-World-DBA-Episode-14-SQL-...(truncated)... This week: In the news, The TechNet site has some new updates. In this weeks feature, well continue the introduction to SQL Server Features and talk about...(read more)[Image]

SQL Injection Attacks

(Indexed 2008-05-30):

You might have read recently that there have been ongoing SQL injection attacks against vulnerable web applications occurring over the last few months. These attacks have received recurring attention in the press as they pop up in various geographies...(read more)[Image]

Working with other RDBMS's

(Indexed 2008-05-29):

From time to time we get asked if we could extend SQL Server Management Studio (SSMS) to manage other database platforms. In fact, it's come up here among ourselves, and is the subject of no small debate. As you know, I was a DBA for a long time, and...(read more)[Image]

Watching the Little Things

(Indexed 2008-05-28):

There's a new option in in the Tools | Options | Designers area of SQL Server Management Studio in 2008, called Prevent saving changes that require table recreation . What this does is to keep the Database Designer from committing changes to your actual...(read more)[Image]

Changes on the Developer and TechCenters for MSDN

(Indexed 2008-05-27):

I'm not sure if you've noticed the changes on the SQL Server Developer Center on MSDN or SQL Server TechCenter on TechNet. If not, you should definitely check them out. Here are a few highlights: On the home page of both MSDN and TechNet, youll see...(read more)[Image]

Real World DBA Episode 13 SQL Server Features XML

(Indexed 2008-05-23):

http://edge.technet.com/Media/Real-World-DBA-Episode-13--SQL...(truncated)... This week: In the news, you could win a Windows Home Server, just for evaluating Microsoft products. In this weeks feature, well continue the introduction to SQL Server...(read more)[Image]

Sending Feedback from SSMS

(Indexed 2008-05-22):

I've pointed out the "Connect.Microsoft.Com" site before, where you can enter feature requests and bugs for SQL Server. Did you know you can hit that site from within SSMS? Just click the "Community" menu item in SSMS and then "Send Feedback". That will...(read more)[Image]

Getting Bit by Forgetting

(Indexed 2008-05-20):

I was working on a SQL Server 2005 system yesterday and I had really awful performance on one of my queries. I just couldn't figure out why it was running slow. I decided to use the new SQL Server 2008 T-SQL Debugger on the code, and sure enough - I found...(read more)[Image]

Simulating Multiple Connections

(Indexed 2008-05-19):

As I was giving a demo of hte new multi-query capabilities the other day, I was asked how I had four Instances installed on one small, underpowered laptop. In fact, I don't - I have one instance of SQL Server 2005 and one Instance of SQL Server 2008 installed....(read more)[Image]

Real World DBA Episode 12 SQL Server Features SSIS

(Indexed 2008-05-16):

http://edge.technet.com/Media/Real-World-DBA-Episode-12--SQL...(truncated)... This week: In the news, Microsoft and Dell have teamed up to provide out of the box Business Intelligence systems. In this weeks feature, well continue the introduction...(read more)[Image]

Real World DBA Episode 11 SQL Server Features Reporting Services

(Indexed 2008-05-09):

http://edge.technet.com/Media/Real-World-DBA-Episode-11--SQL...(truncated)... This week: In the news, Microsoft updates the Script Center, In this weeks feature, well continue the introduction to SQL Server Features and talk about...(read more)[Image]

Top Ten Troubles with Management Studio

(Indexed 2008-05-08):

"Hey - don't forget about those of us just now switching to SQL Server Management Studio 2005 from SQL Server 2000!" I heard this the other day from a reader of this blog. Don't worry, even though I've been talking about the new improvements we've made...(read more)[Image]

SQL Server 2008 Management Improvements SQL Server Management Studio Enhancements

(Indexed 2008-05-07):

At the SQL Connections conference in April of 2008 I gave a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post Ill talk about several improvements for SQL Server management Studio (SSMS). This isnt one...(read more)[Image]

Why you should upgrade to SQL Server 2008

(Indexed 2008-05-06):

One of the readers recently asked about the blog post I made regarding the performance of replication being so improved in SQL Server 2008. We dont have a formal study posted on that particular result yet, but we do have others that talk about speed...(read more)[Image]

Windows 2008 and Replication

(Indexed 2008-05-05):

I mentioned in a previous post that I would point out where SQL Server 2008 takes advantage of improvments in Windows 2008 (Check out the "Windows 2008" tag below for that). I just had to share something I got today from the replication team in an e-mail:...(read more)[Image]

Episode 10 - SQL Server Features- Analysis Services

(Indexed 2008-05-02):

http://edge.technet.com/Media/Episode-10-SQL-Server-Features...(truncated)... This week: In the news, Microsoft has announced that the Incremental Service Model will include Service Packs. In this weeks feature, well continue the introduction to SQL...(read more)[Image]

SQL Server 2008 Management Improvements T-SQL Intellisense

(Indexed 2008-05-01):

At the SQL Connections conference in April of 2008 I gave a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post Ill talk about a New improvement for SQL Server management Studio (ssms). This feature works...(read more)[Image]

SQL Server 2008 Management Improvements SQL Server Provider for PowerShell (Part 3)

(Indexed 2008-04-30):

At the SQL Connections conference in April of 2008 I gave a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post Ill talk about one of those new improvements our new PowerShell provider for SQL Server. For...(read more)[Image]

SQL Server 2008 Management Improvements SQL Server Provider for PowerShell (Part 2)

(Indexed 2008-04-29):

At the SQL Connections conference in April of 2008 I gave a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post Ill talk about one of those new improvements our new PowerShell provider for SQL Server. Yesterday...(read more)[Image]

SQL Server 2008 Management Improvements SQL Server Provider for PowerShell (Part 1)

(Indexed 2008-04-28):

At the SQL Connections conference in April of 2008 I gave a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post Ill talk about one of those new improvements our new PowerShell provider for SQL Server. Im...(read more)[Image]

Real World DBA Episode 9 SQL Server Features SQL Server Replication

(Indexed 2008-04-27):

http://edge.technet.com/Media/Real-World-DBA-Episode-9--SQL-...(truncated)... This week: In the news, theres a new way to read SQL Server Documentation. In this weeks feature, well continue the introduction to SQL Server Features...(read more)[Image]

SQL Server 2008 Management Improvements T-SQL Debugger

(Indexed 2008-04-25):

Im here at the SQL Connections conference in April of 2008 and Im giving a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post Ill talk about the Transact-SQL Debugger tool. Those of you who have been using...(read more)[Image]

SQL Server 2008 Management Improvements SQL Server Management Studio Improvements

(Indexed 2008-04-24):

Im here at the SQL Connections conference in April of 2008 and Im giving a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post Ill talk about a few of the enhancements weve made to SQL Server Management...(read more)[Image]

SQL Server 2008 Management Improvements Data Collectors, the Management Data Warehouse, and Performance Reports

(Indexed 2008-04-23):

Im here at the SQL Connections conference in April of 2008 and Im giving a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post Ill talk about one three of the new performance troubleshooting features in...(read more)[Image]

SQL Server 2008 Management Improvements Policy Based Management

(Indexed 2008-04-22):

Im here at the SQL Connections conference in April of 2008 and Im giving a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post Ill talk about one of the new features in SQL Server 2008, Policy Based Management....(read more)[Image]

SQL Server 2008 Management Improvements - Introduction

(Indexed 2008-04-21):

Well, its the first day of the SQL Connections conference in April of 2008 (yes, on a Sunday, although this will post on Monday) and my booth duties are pretty light. There are some great presenters, among them Kimberly Tripp and Paul Randall, who I...(read more)[Image]

Real World DBA Episode 8 SQL Server Features Database Mirroring

(Indexed 2008-04-20):

http://edge.technet.com/Media/Episode-08-SQL-Server-Features...(truncated)... This week: In the news, theres a new Service Pack in the works for SQL Server 2005. In this weeks feature, well continue the introduction to SQL Server Features and talk about...(read more)[Image]

SQL Server Management Studio Standard Reports The Full List

(Indexed 2008-04-17):

I've completed documenting all of the Standard Reports in SQL Server Management Studio. You can get to them from the tags, but I thought I would post a list of them all here with the links. I hope that you've been able to put some of this information...(read more)[Image]

PowerShell - Where is It?

(Indexed 2008-04-16):

As you may know, weve created a SQL Server 2008 provider for PowerShell. With this very cool feature youre able to navigate SQL Server and its objects as easily as you could the file system on your hard drive. You also have access to all of the properties...(read more)[Image]

SQL Server Management Studio Standard Reports Top Jobs

(Indexed 2008-04-14):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the management reports. You can find these reports by starting SQL Server Management Studio and right-clicking the SQL Server Agent node. From the menu...(read more)[Image]

Real World DBA Episode 7 SQL Server Features - Clustering

(Indexed 2008-04-11):

http://edge.technet.com/Media/Real-World-DBA-Episode-7--SQL-...(truncated)... This week: In the news, SQL Server is now out of Mainstream Support and Ill show you where to find out what support options you have. In this weeks feature, Ill...(read more)[Image]

But is it really new?

(Indexed 2008-04-10):

I was showing a visitor some of the new improvements we have in SQL Server 2008 and when I started SQL Server Management Studio (SSMS), he said "oh, I thought you had something new." He expected to see a new user interface for SQL Server 2008. I dont...(read more)[Image]

Policy Based Management

(Indexed 2008-04-09):

One of the biggest new features in SQL server 2008 is Policy Based Management (PBM), which used to be called Declarative Management Framework (DMF). This feature can be compared to a state machine that means it can set a state for you and either enforce...(read more)[Image]

Support for SQL Server 2000 is ending - make your plans!

(Indexed 2008-04-08):

We're at the end of "Mainstream" support for SQL Server 2000, which is April 8th, 2008. If you're still using this version in your enterprise, you need to plan to move off of that version platform starting now. Many folks are planning to "jump" from SQL...(read more)[Image]

Real World DBA Episode 6 Upgrading SQL Server

(Indexed 2008-04-07):

http://edge.technet.com/Media/Real-World-DBA-Episode-6--Upgr...(truncated)... This week: In the news, Microsoft updates SQL Server Webcasts, in this weeks feature, well cover the processes and procedures you need to follow for an upgrade. The web link...(read more)[Image]

Windows 2008 Terminal services and SQL SSMS

(Indexed 2008-04-06):

One of our Principal Architects, Eddy Bell, shared a great writeup with me that I think is a great way to leverage Windows 2008 and SQL Server 2008. It answers a need that I've seen over and over again - the ability to connect to SQL Server Management...(read more)[Image]

Checking Your System

(Indexed 2008-04-01):

In most checklists for SQL Server, including the one I explained on my last Podcast , you're told to "check your system". In fact, most instructors and DBA Experts will not look kindly on you if you dont. But what does that mean? Well, Ill probably...(read more)[Image]

SQL Server Management Studio Standard Reports Job Steps Execution History

(Indexed 2008-03-31):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the management reports. You can find these reports by starting SQL Server Management Studio and right-clicking the SQL Server Agent node. From the menu...(read more)[Image]

A Match Made in Heaven: SQL 2K8 and Windows 2K8

(Indexed 2008-03-26):

You may already know that you get some real benefits by pairing up SQL Server 2005 on Windows server 2003. You get to tie out SQL Server accounts to some of the Windows Security Policies, for instance. This trend continues with SQL Server 2008 on Windows...(read more)[Image]

SQL Server Management Studio Standard Reports Notification Services - General

(Indexed 2008-03-24):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the management reports. You can find these reports by starting SQL Server Management Studio and right-clicking the Management node. From the menu that...(read more)[Image]

Real World DBA: Episode 4 - Prepping for Install

(Indexed 2008-03-22):

http://edge.technet.com/Media/Episode-4-Prepping-for-Install/ This week: In the news, Microsoft has updated the community links for SQL Server. In this weeks feature, Ill explain the big deal about preparing for an installation, the web link shows...(read more)[Image]

SQL Server Management Studio Standard Reports Number of Errors

(Indexed 2008-03-20):

I'm continuing my series on the Standard Reports in SQL Server Management Studio and today were in the management reports. You can find these reports by starting SQL Server Management Studio and right-clicking the Management node. From the menu that...(read more)[Image]

SQL Server Management Studio Standard Reports Tasks

(Indexed 2008-03-17):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the Management reports. You can find these reports by starting SQL Server Management Studio and right-clicking the Management node. From the menu that...(read more)[Image]

Time-based Optimization

(Indexed 2008-03-14):

One of the primary jobs of both developers and DBAs is optimization, or its more common name, performance tuning. Lots of the management tools improvements for SQL Server 2008 are aimed at performance, such as the new activity Monitor. Using the tools...(read more)[Image]

SQL Server Management Studio Standard Reports Resource Locking Statistics by Logins

(Indexed 2008-03-12):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the Security reports. You can find these reports by starting SQL Server Management Studio and right-clicking the Logins node underneath the Security...(read more)[Image]

SQL Server Management Studio Standard Reports Login Failures

(Indexed 2008-03-10):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the Security reports. You can find these reports by starting SQL Server Management Studio and right-clicking the Logins node underneath the Security...(read more)[Image]

Excited about the Improvements

(Indexed 2008-03-06):

I started working in this team last August, so I really came in near the end of the new work on SQL Server 2008. Thankfully I was still able to get quite a few improvements and changes into a few of the management tools in the last few months. I am compiling...(read more)[Image]

SQL Server Management Studio Standard Reports Login Statistics

(Indexed 2008-03-05):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the Security reports. You can find these reports by starting SQL Server Management Studio and right-clicking the Logins node underneath the Security...(read more)[Image]

SQL Server Management Studio Standard Reports Active Full Text Catalogs: [DatabaseName]

(Indexed 2008-03-03):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

Real-World DBA Podcast Show Notes Episode 1: SQL Server Editions

(Indexed 2008-03-01):

http://edge.technet.com/Media/Real-World-DBA-Episode-1-SQL-S...(truncated)... The Zune(OK, iPod works too) link for subscription is here: http://edge.technet.com/Tags/Real+World+DBA/feed/ipod/ And the general RSS feed is here: http://edge.technet.com/Tags/Real+World+DBA/RSS/...(read more)[Image]

SQL Server Management Studio Standard Reports Service Broker Statistics: [DatabaseName]

(Indexed 2008-02-29):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

Setting up a Testing System

(Indexed 2008-02-27):

I build a new SQL Server just about every other day. As we write new software or fix bugs on old software, we create a new installation of SQL Server. Those of us who are testing the fixes, creating new features and so on have to install these new versions...(read more)[Image]

SQL Server Management Studio Standard Reports User Statistics: [DatabaseName]

(Indexed 2008-02-26):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

SQL Server Management Studio Standard Reports Schema Changes History: [DatabaseName]

(Indexed 2008-02-25):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

Tips for (getting ready to deal with) Support

(Indexed 2008-02-21):

At one time or another, we all need a little help. When youre knee-deep in Production and you run into a Problem, a little panic is unavoidable. Having a military background, Ive been taught that the best way to deal with inevitable panic is preparation....(read more)[Image]

SQL Server Management Studio Standard Reports Index Physical Statistics: [DatabaseName]

(Indexed 2008-02-18):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

System Center Operations Manager and SQL Server

(Indexed 2008-02-14):

If you have multiple systems in your organization, and especially if you use multiple kinds of systems (such as exchange, Forefront and SQL Server, for instance) then you might want to check out Microsoft System Center, and in particular, System Center...(read more)[Image]

A Few Links to SQL Server 2008 Resources

(Indexed 2008-02-13):

As I was presenting at TechReady yesterday on the new SQL Server 2008 features that will directly affect Microsoft System Center, I was asked a few times about some resources where people could go to learn more about it. I thought I would share that here....(read more)[Image]

Localizing Our Software

(Indexed 2008-02-07):

If you want to sell (or give away) your software in multiple countries, you need to think about Localization , which simply means ensuring that the application can be used in other places. At Microsoft, and in SQL Server in particular, we actually break...(read more)[Image]

SQL Server Management Studio Standard Reports Index Usage Statistics: [DatabaseName]

(Indexed 2008-02-06):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

SQL Server Management Studio Standard Reports Object Execution Statistics: [DatabaseName]

(Indexed 2008-02-04):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

Where should the tools be?

(Indexed 2008-02-01):

I've got another question for you - where should the tools be? Let me explain. Most of the time when you connect to SQL Server, you open SQL Server Management Studio (SSMS) on a client machine and then type in the name of a server somewhere on the network....(read more)[Image]

SQL Server Management Studio Standard Reports Database Consistency History: [DatabaseName]

(Indexed 2008-01-31):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

SQL Server Management Studio Standard Reports Resource Locking Statistics by Object: [DatabaseName]

(Indexed 2008-01-29):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

SQL 2K5 for Performance Reports - You have to download these

(Indexed 2008-01-28):

I've been covering lots of reports that ship in the box with SQL Server Management Studio , but there are several others that you can get (if you have Service Pack 2 for SQL Server 2005) that are really amazing. First things first. You can download them...(read more)[Image]

Scripts and more scripts

(Indexed 2008-01-24):

A few posts back I mentioned that I used the "Templates" feature in SQL Server Management Studio to track all of my script librarires. I wanted to expand on that a little bit, and make a correction. Like many DBA's, I have a series of scripts I have collected...(read more)[Image]

Blogs, Blogs and More Blogs

(Indexed 2008-01-23):

I subscribe to a lot of blogs - maybe too many! But I saw a thread the other day where someone was looking for more SQL Server Blogs. I've got a couple of links here that you might find useful: All SQL Server Blogs from Microsoft: http://msdn2.microsoft.com/en-us/sqlserver/bb671054.aspx...(read more)[Image]

SQL Server Management Studio Standard Reports Top Transactions by Locks Count: [DatabaseName]

(Indexed 2008-01-22):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

Questions and Suggestions

(Indexed 2008-01-21):

Ive covered quite a few of the reports in SQL Server Management Studio , and along the way Ive gotten some questions about them and a couple of really good suggestions. Ill cover another report tomorrow, but today I thought I would answer one or two...(read more)[Image]

Controlling Just One Part of a Maintenance Plan

(Indexed 2008-01-17):

A few posts back I mentioned that I was speaking with someone that wanted to use the new Resource Governor feature in SQL Server 2008 to throttle back only one portion of a Maintenance Plan, using the Wizard to create one. The problem he had was that...(read more)[Image]

SQL Server Standard Reports Top Transactions by Blocked Transactions Count: [DatabaseName]

(Indexed 2008-01-16):

In this continuing series on the Standard Reports in SQL Server Management Studio , were now up to the database-level reports, and were in the transaction-level reports at the moment. To get to this report, navigate from the server object in Object...(read more)[Image]

MSDN and TechNet SQL Server Sites Linked

(Indexed 2008-01-09):

SQL Server can be a strange animal - we have no direct end users, but we do have Developers and DBA's. For years we've have two sites that the two camps have used: MSDN for Developers, and TechNet for DBA's. But of course, there is no such thing as a...(read more)[Image]

When the PC becomes the server

(Indexed 2008-01-08):

I was speaking with a friend yesterday about a requirement he has to separate the maintenance tasks running on his SQL Server from the regular user transactions. In specific, he wanted to throttle the reindexing tasks while users were on, since his shop...(read more)[Image]

SQL Server Standard Reports Top Transactions by Age: [DatabaseName]

(Indexed 2008-01-07):

In this continuing series on the Standard Reports in SQL Server Management Studio , were now up to the database-level reports. To get to this report, navigate from the server object in Object Explorer, move down to the Databases object, and then right-click...(read more)[Image]

Using SSMS as a Dashboard

(Indexed 2008-01-04):

The other day I was asked by a friend about a recommendation on a Dashboard for SQL Server 2005. She wanted to be able to see all the pertinent information about her server with just a few clicks. She asked me what I had used in the past, and wanted...(read more)[Image]

SQL Server Management Studio Standard Reports All Blocking Transactions: [DatabaseName]

(Indexed 2008-01-02):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

We Have a Winner!

(Indexed 2008-01-01):

You'll recall that I posted a crossword puzzle in December from the Management Studio framework. We have a winner, selected at random from the correct entries: The artist formerly known as ravenbrk. Here are his winning answers: Across 1 TEXT 2 PROPERTIES...(read more)[Image]

SQL Server Management Studio Standard Reports All Transactions: [DatabaseName]

(Indexed 2007-12-27):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

SQL Server Management Studio Standard Reports Backup and Restore Events: [DatabaseName]

(Indexed 2007-12-24):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today were in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears,...(read more)[Image]

SQL Server Standard Reports Database: Disk Usage by Partition: [DatabaseName]

(Indexed 2007-12-19):

In this continuing series on the Standard Reports in SQL Server Management Studio , were now up to the database-level reports. Today Ill explain another report dealing with space in the database: Disk Usage by Partition: [DatabaseName] . To get to this...(read more)[Image]

SQL Server Internals Course

(Indexed 2007-12-17):

We normally don't recommend one product or vendor over another, but sometimes there is an industry-recognized expert that has something particularly valuable. Kalen Delaney has a course you might be interested in - definitely check it out. It's a course,...(read more)[Image]

SQL Server Management Studio Standard Reports Disk Usage by Table/Top Tables

(Indexed 2007-12-14):

I'm continuing my series on the Standard Reports in SQL Server Management Studio . Im covering two reports at once, since they have the same columns and output, but show the data from a different perspective. These two reports will help you find out...(read more)[Image]

SQL Server Standard Reports Disk Usage: [Database Name]

(Indexed 2007-12-11):

Were in the first of the database-level reports in this series of the Standard Reports that ship with SQL Server Management Studio (SSMS) . To get to these reports, navigate from the server object in Object Explorer, move down to the Databases object,...(read more)[Image]

Puzzle of the Month - this one with prizes!

(Indexed 2007-12-10):

OK, it's a shameless giveaway! See if you can fill out this puzzle, made with words you'll find in SQL Server Management Studio. Mail me the answers and I'll send out a small gift to one lucky reader with the right answers, chosen at random at the end...(read more)[Image]

SQL Server Management Studio Standard Reports Transaction Log Shipping Status

(Indexed 2007-12-08):

Were in the final server-level reports in this series of the Standard Reports that ship with SQL Server Management Studio (SSMS) . Dont worry, there are plenty more reports coming in the database and other objects; but this one is the last of the reports...(read more)[Image]

SQL Server Management Studio Standard Reports Service Broker Statistics

(Indexed 2007-12-04):

I'm continuing my series on the Standard Reports in SQL Server Management Studio . Today I'm covering a report on a feature you might not have used yet the Service Broker. This feature, which you can read more about here , allows you to set up a messaging...(read more)[Image]

Go get me a switch!

(Indexed 2007-11-29):

You probably access SQL Server Management Studio (SSMS) using an icon somewhere in your Start menu or on your desktop. But did you know that there are several command-line switches that you can use to start SSMS and end up right where you want to go?...(read more)[Image]

Templates

(Indexed 2007-11-28):

Ive run into quite a few DBAs who arent using the Templates feature we provide with SQL Server Management Studio. If you spend a little time becoming familiar with them, I think youll find they are a powerful tool. Templates are really just a series...(read more)[Image]

SQL Server Management Studio Standard Reports Performance Top Queries by *

(Indexed 2007-11-27):

I'm continuing my series on the Standard Reports in SQL Server Management Studio . Today I'm covering four reports at a time the performance reports that are broken down by average and total times. The reason Ill cover all four of them is that they...(read more)[Image]

CTP5 is out! OK, so maybe you already know that...

(Indexed 2007-11-26):

I got back from vacation ( more here if you want to read about where we go and what we do every other week) and we released SQL Server 2008 CTP5. If you have been waiting for a bit to install and learn more about it, this is a good release to try. Make...(read more)[Image]

More info on those reports

(Indexed 2007-11-16):

I spoke with Andrew Fryer yesterday via e-mail when I noticed he was also covering some of the same information we're looking at here on the Standard Reports in SQL Server. I think that's great - I had been wondering if there were many people using these...(read more)[Image]

SQL Server Management Studio Standard Reports - Performance - Object Execution Statistics

(Indexed 2007-11-15):

We're in the performance section of the series of Standard Reports you get with SQL Server Management Studio . The report we're covering today, located in the "Server" level of reports is another Performance Report, and it is interesting because it brings...(read more)[Image]

SQL Server Management Studio Standard Reports - Performance - Batch Execution Statistics

(Indexed 2007-11-13):

Im working my way through the Standard Reports you get in SQL Server Management Studio. Ive gone through several of them already, and today we finally move out of the locking and blocking reports. The next report in the list, and were still only on...(read more)[Image]

The Importance of Connections

(Indexed 2007-11-12):

Since I didn't blog about anything very technical in my last entry, I thought I would post a little something about what we're hearing about the "Connection" dialogs. We have quite a few places in Management Studio that we pop up a place for you to connect...(read more)[Image]

SQL Server Management Studio Standard Reports - Top Transactions by Locks Count

(Indexed 2007-11-08):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today I'm covering a report that shows more about locking activity, by transaction. Before I begin describing the columns and values in this report, it might make some...(read more)[Image]

Top Transactions by Blocked Transactions Count

(Indexed 2007-11-06):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today I'm covering a report that shows the transactions on an instance that block others. Ive described some of this blocking activity here , but this report orders...(read more)[Image]

SQL Server Management Studio Standard Reports - Activity Top Transactions by Age

(Indexed 2007-11-02):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today I'm covering another "Activity" report. This report shows more information about transactions, which Ive covered in some of the other reports. This one, however,...(read more)[Image]

SQL Server Management Studio Standard Reports - Activity Top Connections

(Indexed 2007-11-01):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today I'm covering another "Activity" report. I covered other activity reports, most from the viewpoint of users or sessions. This report shows much of the same information,...(read more)[Image]

Should it be easy to delete a database?

(Indexed 2007-10-30):

We had a discussion yesterday about Management Studio, and we focused on the Delete command. In SQL Server 2005, you can right-click a database and select Delete . You get a panel where you can set some options, or you can just click OK and the...(read more)[Image]

SQL Server Management Studio Standard Reports - Activity Dormant Sessions

(Indexed 2007-10-29):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today I'm covering another "Activity" report. The Activity Dormant Sessions report shows you the sessions you have on SQL Server that are connected, and perhaps at...(read more)[Image]

SQL Server Management Studio Standard Reports - Activity - All Sessions and Activity - Top Sessions

(Indexed 2007-10-26):

I'm continuing my serie s on the Standard Reports in SQL Server Management Studio , and today I'm covering two of them: Activity - All Sessions and Activity - Top Sessions. These are part of the a activity series of reports that provide more detail than...(read more)[Image]

Doing Everything the Graphical Way (Or maybe not)

(Indexed 2007-10-26):

One of the things we try to do in SQL Server Management is to allow you to work in whatever way is suited best for your situation and the way you do your job. Some people like to work with graphical tools, since everything you can do with the product...(read more)[Image]

SQL Server Management Studio Standard Reports - Activity - All Cursors and Activity - Top Cursors

(Indexed 2007-10-23):

I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today I'm covering two of them: Activity - All Cursors and Activity - Top Cursors. These are part of the activity series of reports that provide more detail than the...(read more)[Image]

SQL Server Management Studio Standard Reports Activity - All Blocking Transactions

(Indexed 2007-10-19):

Im continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS). We're now up to the "Activity" reports, which show more information than you'll find in SQL Server Management Studio in the "Activity Monitor" node. That...(read more)[Image]

Management by GUI or by Scripts?

(Indexed 2007-10-18):

I work in the part of SQL Server that is all about SQL Server Management. I handle the graphical tools, and another person handles the command-line stuff. Which is kind of interesting, since in my career I've mostly used scripting to manage my systems....(read more)[Image]

SQL Server Management Studio Standard Reports Memory Consumption

(Indexed 2007-10-17):

Im continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS). The Memory Consumption report shows information about how the Instance of SQL Server has partitioned out the memory on your system. Memory is probably...(read more)[Image]

Designing Women (and Men)

(Indexed 2007-10-16):

I understand some people are having an issue with the last few posts on the standard reports in Management Studio. Ill look into that but if you click into them, they seem to work fine. Anyway, I thought I would break from those for one post to talk...(read more)[Image]

SQL Server Management Studio Standard Reports Scheduler Health

(Indexed 2007-10-15):

Im continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS). The Scheduler Health report shows information about each scheduled process that the Instance of SQL Server runs on the processor(s) on your system. In...(read more)[Image]

SQL Server Management Studio Standard Reports Schema Changes History

(Indexed 2007-10-12):

DIV.Section1 { page: Section1 } P.MsoNormal { FONT-SIZE: 11pt; MARGIN: 0in 0in 10pt; LINE-HEIGHT: 115%; FONT-FAMILY: "Calibri","sans-serif"; mso-style-parent: "" } TABLE.MsoTableGrid { BORDER-RIGHT: black 1pt solid; BORDER-TOP: black 1pt solid; FONT-SIZE:...(read more)[Image]

SQL Server Management Studio Standard Reports Configuration Changes History

(Indexed 2007-10-11):

Im continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS). (Note: These reports only work against SQL Server 2005 and higher. They dont run against the lower versions because those dont have all the management...(read more)[Image]

SQL Server Management Studio Standard Reports Server Dashboard

(Indexed 2007-10-10):

@font-face { font-family: Cambria Math; } @font-face { font-family: Calibri; } @page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; mso-header-margin: .5in; mso-footer-margin: .5in; mso-paper-source: 0; } P.MsoNormal { FONT-SIZE: 11pt;...(read more)[Image]

SQL Server Management Studio Standard Reports - Overview

(Indexed 2007-10-09):

I mentioned in another post that weve been asked to bring back the TaskPad from SQL Server 7s Enterprise Manager. As I described there, when we plumbed out that question a little further we found that what people really wanted back is the quick access...(read more)[Image]

Its two helps in one!

(Indexed 2007-10-02):

Did you know that you can show help in two different ways from SQL Server Management Studio? Most of us like the full client of Books Online, which is actually a program called the DExplorer. This tool has multiple windows, a URL bar, a search feature...(read more)[Image]

I need to script with all options. Where are all the options?

(Indexed 2007-10-01):

In older versions of SQL Server, when you scripted an object you got a rather large panel that allowed you to set various options such items as DROPs, users, and more. In SQL Server 2005+, when you right-click and object and select Script you dont...(read more)[Image]

Argh! Bring back Query Analyzer!

(Indexed 2007-09-28):

Steve Jones, a friend of mine over at sqlservercentral.com, asked me a question the other day that is brought up quite a bit. In earlier versions of SQL Server, we had at least two management tools: Enterprise Manager (EM) and Query Analyzer (QA). EM...(read more)[Image]

Elements of Design

(Indexed 2007-09-25):

What is the "best" solution for a management tool for SQL Server? If you ask a pure DBa (those technical professionals concerned primarily only with system administration from a database standpoint) you'll get a different answer than you will from a data...(read more)[Image]

Script of the day: Count all objects in a database

(Indexed 2007-09-24):

This script counts all of your objects in the database, at least the ones stored in the sysobjects table. Some objects (such as jobs) don't show up there - one day I'll add those: /* usc_DBA_Count_Objects.sql Author: Buck Woody Purpose: Shows a quick...(read more)[Image]

PASS Day Three

(Indexed 2007-09-20):

Yesterday Ted Kummert (who is the VP in charge of SQL Server for Microsoft) made a bunch of really key announcements about SQL Server 2008 at the keynote: - There will be no price increase for SQL Server 2008 - Dell and Microsoft are working together...(read more)[Image]

PASS Day One

(Indexed 2007-09-18):

I've flown down to Denver, Colorado this week to work the booths and do a few presentations here at PASS, the Professional Association of SQL Server. This is a community group, so as a Microsoft employee I'm a guest at this function. I've mentioned before...(read more)[Image]

Sustained Engineering Process

(Indexed 2007-09-12):

In an earlier post I mentioned how we create the initial ideas for our software features. This may be a different process than you've seen at other software shops. Another big difference in the way the SQL Server organization does things is how we implement...(read more)[Image]

Change it! No wait, Leave it Alone!

(Indexed 2007-09-11):

We've used two major system management paradigms for SQL Server: The Microsoft Management Console (MMC) and the Visual Studio shared components. In early versions of SQL Server, we had Enterprise Manager for most management tasks from a graphical standpoint...(read more)[Image]

How do we create software at Microsoft?

(Indexed 2007-09-07):

Back when I worked in the User Education group as a content developer, I wrote an entry in this blog that described how we put documentation and other information together for SQL Server. I thought I would follow that up now that Im in a new group and...(read more)[Image]

OK - My SQL Server Management Studio environment is *really* messed up now

(Indexed 2007-09-06):

One of the advantages with placing the Management Studio for SQL Server in the Visual Studio shell is the ability to float the windows, pin them, and generally move them around. But sometimes you can get yourself into a situation where everything is pretty...(read more)[Image]

SQL Server 2008 CTP4 Virtual Machine Available

(Indexed 2007-09-05):

You can find the latest release of SQL Server 2008 in a convenient Microsoft Virtual Machine format here: Microsoft Connect SQL Server Microsoft Download Center Enjoy!...(read more)[Image]

SQL Server and Green Computing

(Indexed 2007-08-29):

As I was working through the list of customer requests for the next feature set in SQL Server 2008, one item that kept floating to the top was "multiple monitor support". More and more of us are using two flat-screen monitors to do our work each day....(read more)[Image]

Connection Security Confusion

(Indexed 2007-08-22):

You know, more than just about any other question I get the most common is around connection security for SQL Server Management Studio. Let's take a couple of scenarios and walk through the basics. Windows Accounts on SQL Server (Windows Authentication)...(read more)[Image]

Index Overlaps

(Indexed 2007-08-21):

There are a lot of tools in SQL Server 2005 that will help you with indexes, from enhanced plan diagrams to the Database Tuning Advisor. But I was scouting around this morning for a script that would find places where I had indexes that included duplicate...(read more)[Image]

Longhorn (Windows Server) and SQL Server

(Indexed 2007-08-17):

Feeling a bit adventurous, I've installed the latest Longhorn Beta and then SQL Server 2005 Developer Edition on top of it. Other than the inevitable video driver issues, it's been pretty stable. One feature I do like is the "Server Manager" that pops...(read more)[Image]

Powershell Plus - Very Nice!

(Indexed 2007-08-16):

Our group (the Management Platform Team) is heavily involved in the PowerShell-as-it-applies-to-SQL area. We're working on a new provider for SQL Server 2008, as I'm sure you've seen in some of the demos we've been giving at various user groups. Well,...(read more)[Image]

Script of the day - Locking Information

(Indexed 2007-08-15):

This one is an oldie but a goodie. I don't remember who originally gave me this, but I've edited it over the years. Works on SQL Server 2000 and 2005. /* usc_DBA_Show_Lock_Types.sql Author: Unknown Purpose: Shows the various locks taken on a system */...(read more)[Image]

Script of the Day - Find the Cores on a System

(Indexed 2007-08-14):

Sure, you can get the number of processors fairly easily ffrom SQL Server. But did you know you can also get the number of actual cores as well? This little gem works with SQL Server 2005: SELECT cpu_count/hyperthread_ratio AS sockets FROM sys.dm_os_sys_info;...(read more)[Image]

The Biggest Problem We Face

(Indexed 2007-08-09):

Last night I gave a presentation to the Pacific Northwest SQL User Group. I mentioned that we often think our biggest problems are lack of time, too much work, people we have to deal with or technical issues. But all of us get done only what we can, we...(read more)[Image]

Table Diffs

(Indexed 2007-08-08):

I think one of the most asked and answered questions in SQL (not just SQL Server, but SQL engines in general) is "How do I find/get the data from table A to table B?" In fact, you've probably seen multiple variations of the question and the answer. I...(read more)[Image]

Maintenance Plan MetaData

(Indexed 2007-08-07):

I was recently asked about which tables are involved in a maintenance plan in SQL Server 2005.The primary tables are: msdb.dbo.sysmaintplan_subplans msdb.dbo.sysmaintplan_log msdb.dbo.sysmaintplan_logdetail msdb.dbo.sysdbmaintplans msdb.dbo.sysdbmaintplan_jobs...(read more)[Image]

Secure by design

(Indexed 2007-08-06):

Whenever you're developing a new application that hits SQL Server, make sure you design in security from the outset. In the new releae of the Community Tehcnal Preview (CTP) for SQL Server 2008, you'll notice that there are several new improvements in...(read more)[Image]

Ch..ch..ch..changes!

(Indexed 2007-08-01):

I've been with Microsoft for about a year now. It's an interesting place to work, and I've enjoyed it here. I started with the SQL Server "User Education" team, and I've written documentation for Books Online, whitepapers and other technical outlets....(read more)[Image]

Slicing Books Online

(Indexed 2007-06-27):

With all of the content in Books Online arranged into a single table-of-contents, its sometimes difficult to find information that spans topics. For instance, if you want to find out about general security topics, youll find they are spread across...(read more)[Image]

The May 2007 Books Online Update is Available

(Indexed 2007-06-11):

You can get the latest crunchy SQL Server documentation goodness here: http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx...(read more)[Image]

Microsoft Update Now Updates Books Online

(Indexed 2007-05-16):

I've mentioned before that we update Books Online several times a year, and we're moving to do it even more often. I m very happy to announce that SQL Server 2005 Books Online Doc Refresh 4 is now part of Microsoft Update. This means that customers who...(read more)[Image]

The Grand Unified Theory

(Indexed 2007-04-30):

I've mentioned before that Books Online isn't just a client install, but the same content is also available on the web. In fact, it's available in two places on the web: SQL Server 2005 BOL on TechNet: http://technet.microsoft.com/en-us/library/ms130214.aspx...(read more)[Image]

How Do I Do That?

(Indexed 2007-04-12):

Did you know you can quickly locate all kinds of topics that walk you step-by-step through a lot of the common tasks in SQL Server, from backups to creating a report in Reporting Services? In the local client (DExplorer) tool, just look on the top bar...(read more)[Image]

Using the Search Feature

(Indexed 2007-02-23):

SQL Server Books Online is an absolutely huge resource. With over 58,000 pages that change several times a year, it's like getting all of the books at one time that you are ever going to use from Grade-School through College. Not only that, it is used by beginners, intermediates and experts; by developers, DBAs and architects, and is used as the definitive resource for design experts.As you can imagine, coming up with a single table of contents that all of these audiences and levels of readers c...(truncated)...

SQL Server 2005 Books Online is Updated!

(Indexed 2007-02-19):

As I mentioned in my last blog post, SQL Server is updated several times a year - our team calls this a "web refresh". Isn't it great that we come up with new words to describe things? Anyway, SQL Server Service Pack 2 just released, and Books Online must be updated right after you finish the SP2 install: Get the new Books Online here. As an aside, the README for this release is pretty huge, and has a lot of information about Vista and SQL Server in it. Make sure you read through that as well.(P...(truncated)...

SQL Server Books Online

(Indexed 2007-02-14):

Most of us have stacks of books on our shelves about SQL Server. But you also have an amazing resource that you get for free with SQL Server, or even on the web: Books Online. Some of you might not know how SQL Server Books Online is put together and what goes into it, so I thought Id explain whats going on in that part of Microsoft. I'm only talking generally about Books Online here - there are dozens of other teams dedicated to producing and releasing all kinds of documentation and information...(truncated)...

All posts and opinions are the property of their respective Sources. The rest copyright (c) 2006 SQL Server Reporter.

Images have been removed from posts, and some posts have been modified for display purposes.

SQL Server is a registered trademark owned by Microsoft Corporation.

The SQL Server Reporter is in no way affiliated with or endorsed by Microsoft Corporation.

Jobs indexed from indeed.com.