Bucky Woody

Quote of the Day: On the Efficiency of Government

"If you put the federal government in charge of the Sahara Desert, in five years there'd be a shortage of sand." - Milton Friedman

Categories: SQL Server MS Blogs

Dealing with Data: Defining the Components to Tune

I've been reading a fascinating article about the Large Hadron Collider, or LHC facility. It's a scientific research facility that houses a particle collider, which generates an incredible amount of data. Their original plan was to stream the data to tape, then sending the data to "islands" closer to the users, offloading the network as quickly as possible. But they found that the network could handle the streaming better than they thought - so they now stream the data directly to the users, saturating the network. It's a new way of thinking about moving the data around.

Another interesting data concept is that they filter it before they store it. We're not talking trivial reductions here - they are filtering a petabyte (PB) of data a second to a gigabyte per second! That's incredible. In fact, an overwhelming majority of the CPU power there doesn't go to computing numbers and so on in the scientific exercises - it's used to filter the data.

Most of us concern ourselves with data storage. We fret over space, the cost of drives, and backing up. But the LHC staff deals with that as well - but they are more concerned with network and CPU. To be sure, their data profile is different than yours or mine - but there are still things we can learn from their efforts. You can read the whole article yourself here: http://arstechnica.com/science/news/2010/08/lhc-computing-grid-pushes-petabytes-of-data-beats-expectations.ars?utm_source=rss&utm_medium=rss&utm_campaign=rss

Categories: SQL Server MS Blogs

The New World of Work: Hyper-Connectivity and Work/Life Blur

In my last couple of posts I’ve mentioned a new world of work environment put forth by the Gartner Group’s Tom Austin, with a few additions from my own perceptions. I’ve mentioned “swarming” and that we deal with more unique tasks, and today I’ll continue that theme with two other observations.

We’re seeing more and more that knowledge workers (those who spend a majority of their time on a computer screen) are being asked to deal with larger and larger amounts of data. That has at least two ramifications. One is that we need to be connected more often, and on faster lines. Moving that data around is more problematic - we can't just copy an entire dataset down to a spreadsheet and work with it. The systems have to allow us to store, process and analyze large data right where it is.

The second is that we not only deal with much more data, we have to interpret it. It’s no longer enough to quickly graph something on a spreadsheet chart, we need to be able to explore data patterns, interpret that data, and create a strategy to deal with it – all very quickly. For instance, in years gone by it was fairly common to manage a few databases on a single large server. We had time to plan, observe, and tune the systems. Now we’re asked to manage hundreds of databases on multiple servers, in mixed environments from physical and virtual machines to datacenters and cloud systems. We don’t have the luxury of spending a lot of time on one system, so we have to amalgamate that data to derive patterns for growth, stability and tuning.

Another trend is blurring the lines between work and life. I have a flexible schedule to some degree, which means I can walk my daughter to school but I’m online for work throughout the evening. While I’m on the couch with my family reading a web feed or online book, I get a notification that a server I manage has completed one phase of a task. I review the logs, and start the next procedure, all while talking with my wife.  So does this mean we have to work twenty-four hours a day? No. The effects of over-work, or “burnout”, are well known, and don’t lead to higher productivity. What has changed is exactly when we work.  I work throughout the day, but I also have time for things I need to do throughout the day. To be sure, not everyone has this style of work – many of us still go to work at a certain time and leave at a certain time. But most data professionals I know are contacted “after hours” to do work on the data systems.

Interestingly, the cloud comes into play here again, as does the datacenter. We need a method to manage the data on my systems from wherever we are – using a smartphone, web kiosk or other connectivity methods. I don’t have my laptop with me all the time, so I need to connect, respond and control my systems from multiple devices – some of them not even mine. In essence I share the duties I have with the datacenter or cloud staff. They keep the hardware running and I manage the data the systems contain. Having a system that I can access from anywhere keeps my from driving in, and not having to manage the physical hardware helps as well.

The key is that we need to learn to think about all the factors in this new world of work to make things easier on ourselves and those who use our data. And along the way we have to consider new threat vectors for security and system access. And we also have to learn to maintain a healthy work-life balance – even though work is only a smart-phone away.

Categories: SQL Server MS Blogs

Quote of the Day: On Thinking Differently

We can't solve problems using the same kind of thinking we used when we created them. - Albert Einstein

Categories: SQL Server MS Blogs

The New World of Work: Unique Tasks

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, and another interesting by-product: unique tasks.

What this means is that we’ve pretty much “drained the swamp” for many industries in the area of automation of repeatable tasks. Machines now perform tasks that years ago were done by people – but even more so than just performing obvious tasks like making pasta or painting cars, machines perform very complex work that many thought would never be taken over by electronics. In software, this trend is even more prevalent, in everything from scripting to 4GL programming languages and visual programming, where non-professional developers write code. In fact, Microsoft is one of the key vendors catering to this market. We just released project “Lightswitch”, which lets non-developers write fairly sophisticated code. In effect, we’ve pushed the coding tasks to the end user, much like what happened when word-processing software pushed typing out to the executives, removing the need for a secretary to do that work.

You can even see this in the next computing wave: the “cloud”. It remains to be seen what the full impact of having servers somewhere else is, but one scenario might be that developers and business folks write code using data objects, not tables and so on, using simple programming techniques that bypass many of the roles that we data professionals currently hold. If this scenario plays out, they would simply buy a cloud services, open Visual Studio, and begin to write code. No design, no planning for server layouts, HADR, scalability – all that would be handled by the cloud. They won’t need (or they’ll think they won’t need) a data professional at all.

I’m not saying this is a good outcome – one can look at the state of business communications to see that the removal of professional secretaries may not be a great thing. But it did happen. Business folks and non-trained developers won’t write code properly, or plan for disasters the way they should. Things will go wrong, and the cloud vendors (like Microsoft) will react to that to fix those issues. But what of the data professional? How should we react?

My personal opinion is to begin now to work with the organization to find out where the business needs for new technologies for data lies. I work closely with the Business Analysts, or, if there aren’t any, with the business people, to educate them on the value, placement, retirement and so on of data within the organization. I’m helping the organizations I manage to find where data “in the cloud” fits, and when it should stay “on-premise”. In fact, I had this discussion just a couple of weeks ago, when I migrated two applications to the cloud for a small church I work with. I laid out the pro’s and con’s of the decision quite clearly, and we decided to implement two solutions on the cloud. I did the work, and they were up and running in an afternoon. I simply couldn’t have ordered a server, installed and configured the software, and written the code that quickly. It’s maintained, versioned and so on, and they can now hit the application from anywhere. In that case, it was the right thing to do.

But I still have applications “on-premise”, even at that small church. For those, I’ve automated just about everything I can think of. My role as the data professional there is what I call a “System’s Shepherd” – I just ensure the automation is running. So my new role is twofold: I do a lot of design and decision work to enable this new world of automation. From there I automate heavily. And after that, it’s a monitoring and reacting environment. Even the reacting is largely automated. And in that way, I can support more systems from a remote location than I ever could before.

My advice? Learn the cloud. Learn scripting. Learn automation. Or be dominated by all three.

Categories: SQL Server MS Blogs

The New World of Work: Swarms

I’ve been reading some excerpts from Gartner, Inc. and information from others on the changes they are seeing in the workplace. It’s holding true where I work and in the workplaces of the other data professionals I work with. One of those new trends is called “Swarming” – where informal teams get together to work on a particular project, and in some cases a single task, as a group. They then move on to another task, and so on, like a swarm of bees. These are less formal than the “Tiger Teams” I used to be part of that were also temporary, but had a more formal banding and dis-banding. The Gartner article states that this is more often the norm in companies than not.

This is interesting to the data professional because there are usually so few of us in the organization. There are on average a single data professional for every five to ten developers, or in an operational sense, one DBA for every several hundred servers in my experience. So when a “swarm”, or “tiger team” or any other kind of tactical group is assembled, the data professional is usually required to be part of it. That’s fine, but it ends up with a few outcomes that you might not expect.

For one, the rate of burn-out or overwork can be higher, since not everyone else works on every project like you do. The data professional has to take part in all of them, so you move from high-stress project to project. The key here is that since not everyone is on every project (except you) they may not notice it happening. They only see you on "their" projects, so they assume you're working at the same level as they are.

Another impact of a swarm on a data professional is that over time you might become more tactical than you want, and you might also be perceived that way by others. After all, if they only see you responding to fires, perhaps they’ll think that’s what you like or what you are good at.

So how do you mitigate these problems associated with this new way of work? Communication. You need to let your boss know, in a non-complaining way, what your workload looks like. No, not how *much* work you have, but what *kind*. They need to understand that you would like the ability to have a good pace to allow for the best quality work, and most importantly that you would like to build some time into your schedule for strategic work along with the tactical requirements. Of course, you need to balance this with actually getting your work done, but I have a few ideas about that which I think might be able to help. I’ll blog about those another time.

Categories: SQL Server MS Blogs

Quote of the Day: The Role of Persistence

Success is going from failure to failure without loss of enthusiasm. - Winston Churchill

Categories: SQL Server MS Blogs

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

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 by several other SQL Server professionals, he’s put together “Project Phoenix” – where he invites unemployed or underemployed developers to propose a software project for a non-profit agency, school, or church. He and his group will give you a package of the latest software, tools, and training resources to help you improve your skills, get up to date with current technologies, gain practical experience, and potentially earn a recommendation for your efforts that could lead to full-time work.

If you’re able to help out like this, then do it! If you’re not the “organizing” type, join in with folks like Andy to see what you can do. When we help each other we all get better.

Arnie's site is here: http://sqlblog.com/blogs/arnie_rowland/archive/2010/07/30/like-a-phoenix-rising-from-the-ashes.aspx

Categories: SQL Server MS Blogs

Direct Contact with SQL Server's Future

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 if someone's already submitted that bug or feature. If they have, you can vote on it to "add your voice" that you want something done. The product team seriously evaluates these requests as they design features and fixes - SP4 for SQL Server 2005 is a direct result of this process, as are many other features and fixes. So start there.

We also have a group here called Microsoft User Research. It's a group of folks that have HCI degress (that's "Human/Computer Interaction") that study in very scientific detail how you use the software. From time to time that involves not only studying mountains of data but physically interviewing folks that use SQL Server. You'll see them with special setups at PASS, TechEd and other locations. They also conduct on-site tests in some firms, and from time to time they offer studies at various Microsoft locations. And you're invited.

Here's a direct invitation to you - if you live here in Seattle (more will be held elsewhere in the future):

Microsoft Ressearch is currently recruiting Seattle area SQL Database Administrators, Developers and Business Intelligence professionals for several upcoming research studies. This is an excellent opportunity to share your experiences, challenges and ideas with our researchers as we develop the next generation of Microsoft products and technology. Study opportunities can be chosen from Tuesday, August 17 through Friday, August 27, and all will be from 2 to 2.5 hours in length on the Microsoft campus in Redmond. For those who participate, we will offer a choice of current Microsoft hardware, software or games as a gratuity. If you (or any of your colleagues) are interested, please respond with the following in an email titled "SQL" to uccoord@microsoft.com:

1.       Name/Phone Number

2.       Job Title/Company

3.       How many SQL Servers does your company currently run?

4.       Do you build BI solutions or work with cubes?

5.       How many hours per week do you work with an integrated development environment, i.e., Visual Studio, JDeveloper, BIDS, etc.?

6.       Do you have experience with SQL Server Configuration Manager?

7.       Do you have experience setting up High Availability and Disaster Recovery for your production databases?

You can visit Microsoft User Research for more information or to sign up for future studies. They are also on Facebook, so you can stay up to date with these studies that way.

Categories: SQL Server MS Blogs

Quote of the Day: On the Future

The future is here. It's just not widely distributed yet. - William Gibson

Categories: SQL Server MS Blogs

Watch those clustered indexes

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-Every-Angle.aspx  

Categories: SQL Server MS Blogs

ISV Applications versus In-House Applications

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 also be useful; but at least know the applications you have and whether they are from an Independent Software Vendor (ISV) or whether your developers wrote them (also called in-house). In an emergency or in Business Continuity planning, and even in sizing and licensing, this is invaluable.

Categories: SQL Server MS Blogs

How I work: Workstations for the Data Professional

I’ve used a lot of working methods for my desktop environment (not my servers) over the years, but they fall into three “buckets” of systems:

1.      Big Workstation, VM Server, all tools, documentation, test environments local

2.      Laptop with all tools installed, as well as Microsoft Office, hit the documentation on the web and a remote testing environment

3.      Purpose-built Virtual Machines

I’m moving towards the latter model right now – I use Office in the “cloud”, Outlook on the web, and SharePoint for almost all my “office” work. I run a VM that is set up for T-SQL development and database design (with the Redgate, Quest and Embarcadero tools there, among others), another for Business Intelligence, another for SharePoint testing and so on. I find that each of these can use a different OS, patch level and so on. The only issue is that sometimes I actually want that hardware dependence, and of course I need a decent machine to run the VM’s. I don’t always run them all at the same time, so in fact that really isn’t an issue. I do have to synch between the VM’s, so I use a combination of PowerShell Scripts and Windows Live Mesh for that. So far I’m finding that I am very efficient this way, and can carry my “computer” on a USB drive assuming there’s a host machine at the conference or client where I’m travelling.

So, what are you finding to be the best way to work? Or do you have much of a choice in that?

Categories: SQL Server MS Blogs

Quote of the Day: On Risk

"To win without risk is to triumph without glory." - Pierre Comeille

Categories: SQL Server MS Blogs

Permanent storage – a myth or a requirement?

I'm looking over some data retention strategy documents for a company, I noticed that they want to keep some data "forever". Now, beyond the quibble of determining how to store something for eternity, how permanent can data really be? There are atomic (n, not ACID, atomic) ways of storing binary representations, but will the interpretation of those 0's and 1's really be there for more than a few decades? Think about the data stored on punch cards - where is that now?

The answer is that it morphed into other storage along the way. If something really needs to be permanent, you need to think more about it's migration than the actual media it's stored on. That's what I'm recommending in this document - tag data sets to be migrated first, or at least considered in the migration planning. It's a simple pivot on storage versus programming, but an important one.

Categories: SQL Server MS Blogs

PowerShell for the DBA: Search the Windows Event Logs for Errors

This is a very simple script - but it's one I run each morning. It searches the Windows System Event Log for an error condition. You can replace "System" here with "Application" or "Security", or any of the other logs that are created on your Windows Server. This is run at the server, since I have each server check itself and make a file of the results - then I swing by and pick up the files each morning with another script that builds a web page.

 # Parse for errors
Get-eventlog System | Where-Object { $_.entryType -eq "error" }

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Categories: SQL Server MS Blogs

Schemas as Security Boundaries

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 control over, without having to make them a full database owner. I would show you that process here, complete with an example and so on - but happily theSQL Server best practices team beat me to it. Check this link, and move to the middle of the page - where it starts with "Using Schemas in SQL Server": http://msdn.microsoft.com/en-us/library/dd283095(SQL.100).aspx 

Yet another great reason to learn and use schemas...

Categories: SQL Server MS Blogs

Don’t mess with the system databases in SQL Server, or Error: 916

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. And by “system databases” what I mean are the big four:

1.       master

2.       model

3.       msdb

4.       tempdb

In some cases however - specifically in the security area - we (Microsoft) have been less than clear on the system databases. I want to address one particular issue that’s been going around in discussions on the web, so I want to make sure I clear this up carefully.

Statement: Don’t remove the “guest” account from the msdb system database.

Hopefully that’s clear. Just don’t remove it. It’s not a bug that it's in there. You need to keep the guest account in msdb for LOTS of stuff to work, from Policy Based Management (PBM) all the way to SQL Server Management Studio. If you do remove it, you’re apt to get this message (but only if you’re not in the sysadmin group):

Failed to retrieve data for this request. (Microsoft.SqlServer.Manager.Sdk.Sfc)

Additional Information:

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo)

The server principal “Buck” is not able to access the database “msdb” under the current security context. (Microsoft SQL Server, Error: 916)

I know, this is a very rare thing, and if you change something and then things quit working, you’ll probably put 2 + 2 together to know what happened. But just in case an admin removes it and you can’t access your databases through SSMS any more, well, there you go.

We DO have documentation on this: http://msdn.microsoft.com/en-us/library/ee342155.aspx  and we’ll be updating the security best practices whitepapers we have to make this very clear. But since some guidleines tend to sound like you should remove guest from EVERY database, I wanted to make sure you know what to do in the meantime.

My friend Cliff Dibble, a Principal Program Manager on the same team at SQL Server I worked at has provided us a script you can use to see if you have the issue:

/* Find the issue of 916 if result set is empty, you have the issue */
USE msdb;

SELECT prins.name AS grantee_name, perms.*

FROM   sys.database_permissions AS perms

JOIN   sys.database_principals AS prins

ON     perms.grantee_principal_id = prins.principal_id

WHERE  prins.name = 'guest' AND perms.permission_name = 'CONNECT';

GO

/* Fix issue */

USE msdb;

GRANT connect TO guest;

GO

So there you have it. Look for more clear guidance in our security tools forthcoming.

Categories: SQL Server MS Blogs

Quote of the day - Doing the right thing

Today's quote is from Ovid, a Roman poet: "Video meliora proboque deteriora sequor"

 (I see and approve the better course, yet choose the worse.)

Unfortunately all too true for me far too often.

Categories: SQL Server MS Blogs

Just how permanent does that data need to be?

As data professionals, we plan for things. We plan for growth, we have a disaster-recovery plan, we have a plan for consolidation. Those are all good things. But I've seen very few shops that have a good data retention plan. This is a plan that basically answers the question, how permanent does that data need to be? Just like the disaster recovery plan, we have to ask the organization what they want, and just like that DR plan they'll tell you they want it all. In the case of the DR plan, they'll say "we don't want any downtime", and when you ask them how long to keep a set of data they will say "forever". But both of those things come at a cost. Not only is there a storage cost, but as the data grows your window for maintenance, backups and optimization grows.

So take some time today and put that retention question on your roadmap. I know, you have a lot to do, but if you don't at least open the discussion now, you'll have a lot more to do in the future.

Categories: SQL Server MS Blogs