Search results matching tags 'DBA' and 'Process'

Where is the SQL Azure Development Environment

BuckWoody — Thu, 03 Feb 2011 14:31:55 GMT

Recently I posted an entry explaining that you can develop in Windows Azure without having to connect to the main service on the Internet, using the Software Development Kit (SDK) which installs two emulators - one for compute and the other for storage. That brought up the question of the same kind of thing for SQL Azure.

The short answer is that there isn’t one. While we’ll make the development experience for all versions of SQL Server, including SQL Azure more easy to write against, you can simply treat it as another edition of SQL Server. For instance, many of us use the SQL Server Developer Edition - which in versions up to 2008 is actually the Enterprise Edition - to develop our code. We might write that code against all kinds of environments, from SQL Express through Enterprise Edition. We know which features work on a certain edition, what T-SQL it supports and so on, and develop accordingly. We then test on the actual platform to ensure the code runs as expected. You can simply fold SQL Azure into that same development process.

When you’re ready to deploy, if you’re using SQL Server Management Studio 2008 R2 or higher, you can script out the database when you’re done as a SQL Azure script (with change notifications where needed) by selecting the right “Engine Type” on the scripting panel:

(Thanks to David Robinson for pointing this out and my co-worker Rick Shahid for the screen-shot - saved me firing up a VM this morning!)

Will all this change? Will SSMS, “Data Dude” and other tools change to include SQL Azure? Well, I don’t have a specific roadmap for those tools, but we’re making big investments on Windows Azure and SQL Azure, so I can say that as time goes on, it will get easier. For now, make sure you know what features are and are not included in SQL Azure, and what T-SQL is supported. Here are a couple of references to help:

General Guidelines and Limitations: http://msdn.microsoft.com/en-us/library/ee336245.aspx

Transact-SQL Supported by SQL Azure: http://msdn.microsoft.com/en-us/library/ee336250.aspx

SQL Azure Learning Plan: http://blogs.msdn.com/b/buckwoody/archive/2010/12/13/windows-azure-learning-plan-sql-azure.aspx

ROI and the DBA

BuckWoody — Thu, 04 Mar 2010 13:46:00 GMT

I love them three-letter acronyms!

An “ROI” is a Return On Investment – it’s a basic financial calculation that places investments in a standard number so that they can be compared. If the number is positive, then it has a “good” return on investment, and if it’s negative, you probably shouldn’t make the investment. The basic form of the calculation looks like this:

ROI = [(Payback - Investment)/Investment)]*100

There are, of course, variations.

So what does this have to do with the Data Professional? Well, right now IT is being asked to consolidate, reduce costs, eliminate headcount, all kinds of moves to “save money”. But is anybody checking to see if any of that really works?

Don’t get me wrong – I’m all about saving money, and definitely about using every resource as much you can. Peg those CPU’s, spin those drives, cloud that app, virtualize those servers. But don’t do it for activity’s sake – learn how to do an ROI, and do some hard comparisons. In fact, ROI’s should be combined to form another calculation, called Compound Return. This lets you know about all of the savings combined.

Something you’ll notice in the ROI calculation – there’s no mention of time. There are a couple of ways to deal with this – you can factor the time as a part of Payback, or you can use the Investment part as a time calculation. How you do that depends on how you price out time.

So when you’re done estimating the ROI of one decision over another, don’t forget to come back and test your assumptions. This is the part I see done the least. Once the decisions are made, it can be “dangerous” to come back and see if it really saved any money – but it’s a step you need to take.

Tools and Processes for “Fitting it all in”

BuckWoody — Mon, 18 Jan 2010 14:42:05 GMT

Most data professionals I’ve met work in two modes: we plan for our day, and we react to the situations around us. I’m 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 – it’s 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 the two? Sometimes it can be quite difficult. I’m constantly working through my list in my mind, re-arranging what I’m focusing on based on what I perceive as the highest need. There are, however, some tools that I use each day to help me manage the workflow.

I use Outlook for tracking everything, since it has a task list (my primary tracking), a calendar, mail and so on. Also I can share the information, it’s on-line so I can see it anywhere, and I can even take it offline onto the plane this week when I fly out of town.

For the “ad-hoc” work, I rely on a script library, which I keep as SQL Server Management Studio projects. I keep those scripts and projects backed using Microsoft Live Mesh, which synchronizes those files (along with a few other critical files and my IE Favorites) across not only my laptop and primary systems, but even with my Virtual Machines.

Also for my SQL Server systems I use the Standard Reports I’ve blogged about here. I also use Greg Larsen’s Database Dashboard, and a series of PowerShell scripts that work across my systems, alerting me to any problems. Of course I’m using SQL Server Agent Jobs quite a bit, and I also use Alerts and some Perfmon automation for my monthly baselining.

So – is this your experience as well? Do you get driven by both planned and unplanned work? What tools and processes do you use to keep it all straight with your SQL Server Instances?

The Database Design Process

BuckWoody — Thu, 14 Jan 2010 14:02:00 GMT

I need your help. I know how I create databases, and I’ve watched a lot of other data professionals follow their own processes for that, but I want to know how YOU do it.

I’ve 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”, “FirstName”, “LastName”, “Business Name”, “Business Street” )

2. Group the nouns into “parent | child” sets (“Client: First Name, Last Name”, “Business: Business Street”)

3. Continue Steps 1 and 2 until you can’t do it any further. These are your tables and columns.

4. Set a value that uniquely identifies every record (line). This becomes the Primary Key. I normally use a “surrogate” key, but a natural key also works if you need it. I don’t like compound keys, but I’ll use them where they make sense. But the overall goal is that every key should be able to identify one “Buck Woody” from another “Buck Woody” record. If it doesn’t, I haven’t done steps 1-2 enough, or perhaps I’ve done it too much.

5. Examine each and every column and ensure that they are in the proper type. Check the business requirements to make sure. (dates are always dates, money is money, numbers are always numbers and so on) Repeat until everyone agrees.

6. Examine the relationships between the tables. Can there be many clients in a company? Can there be many companies for each client? This sets up Foreign Keys, and potentially other tables to solve many-to-many relationships.

There’s a bit more to it than that, and the business requirements side of things are where I actually spend the most time. If you get that wrong, the most beautiful design in the world won’t work over time.

I currently use DBDesignerFork for my design documents to coordinate with my business and development teams, which is not a perfect tool. But Microsoft doesn’t have a good one (we did in Visio, but we messed that up) and so this is what I have to work with. I don’t keep up with this diagram after I create it; it’s just a tool to help me communicate from business to dev to DBA’s.

So now here is where I need your help. Will you post a response here (if you design databases very often) that tells me:

1. What process do you follow?

2. How important are the business requirements?

3. What tool do you use to create the design, do you need it to diagram, do you even care about diagrams?

4. What’s your biggest pain-point about designing? Not with SQL Server, mind you, just the designing part.

Thanks!

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

BuckWoody — Thu, 07 Jan 2010 15:56:00 GMT

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 have to dive in and get the thing fixed.

Hopefully you're not operating like that all the time. If you are, you probably need to get some help with your systems, at least temporarily, until you can get them stabilized.

So which is it? Should you be like me, a type "A" who has to have everything planned out, or a reactionary agent, fixing things as they happen? How about - Both!

I think you should aim to plan and prepare as much as you can. Your life will be more stress-free, and you'll be happier in your job. But you can't lose your head when things go wrong and demand time to plan and prepare. You just have to jump in there and fix the problem.

But I think there's a happy medium in there somewhere. I ten to plan and prepare for the times I have to "just do it". I have my scripts ready for things like backups, DBCC repairs, restores, web site links for "how to" articles and so on. I have those right by my desk so that I don't have to panic when panic hits. So in effect, I'm doing both - I'm planning to just do it.

Wizards are evil – wait, no they aren’t!

BuckWoody — Mon, 14 Dec 2009 15:55:00 GMT

SQL Server contains many “Wizards. Wizards are simply programs that collect information based on user choices. The Wizard’s 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 too easily – especially if the user doesn’t read the screens.

For instance – in the example that’s always mentioned, the Maintenance Wizard, the screens present not only “Shrink Database” (which you should never do) but it presents both REBUILD and REORGANIZE options for the Indexes. Of course, Microsoft isn’t sure which you need to do, but some users select both – which isn’t necessary.

And then from time to time there’s a bug in the code and the Maintenance Wizard doesn’t work properly. For this reason, some experts have recommended that we remove all of the Wizards.

But I don’t agree. I think Wizards have their place – given two conditions. Number one, they always need to work. And number two, the users need to read the options carefully so that they make the right choices. The Wizards do a good job of making sure the process can be scripted, and many of them drop you off (like the Maintenance Wizard) with a package that can be changed later. So don’t give up on the Wizards. Let’s fix them. In fact, tomorrow I’ll talk about an idea brewing in my head, and it involves actually increases the number of Wizards!