Search results matching tags 'sql server' and 'Best Practices'

PASS Workshop “Creating a BI solution from A to Z” - Interview

manowar — Thu, 16 Sep 2010 22:16:27 GMT

If you’re still unsure if the workshop I’ll deliver at PASS Summit the next November - “Creating a BI solution from A to Z” - is a good choice for you or not, you can get some more details reading the brief interview here:

PASS Summit Post-Con Preview - Davide Mauri

Where you’ll find answers to the following questions:

Is there an audience that would benefit especially from this session?
After having attended your seminar, what are two or three things that an attendee will be able to take back to the office and put to use right away?
What background should attendees ideally have to be fully prepared for your seminar?
What experience are you, as a speaker, bringing to this session?

That may help you to take your final decision. More than 20 people already decided to come: you’ll be in good company! :)

Use Those Schemas, People!

BuckWoody — Tue, 18 May 2010 12:39:35 GMT

Database Schemas are just containers – they aren’t 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.

But I still see a lot of applications, developed by shops I know as well as vendors, that don’t make use of a Schema. Everything is piled under dbo. I completely understand this – since permissions can be granted to a schema, they feel a lot like a user, so it’s just easier not to worry about both users and schemas when you create a database. But if you’ll use them properly you can make your application more understandable and portable.

You should at least take a few minutes and read more about them – you owe it to your users: http://msdn.microsoft.com/en-us/library/ms190387.aspx

Testing and Validation – You Really Do Have The Time

BuckWoody — Tue, 04 May 2010 13:20:01 GMT

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.

I’ve been a data professional myself for many years. Over that time I’ve 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. But because I now focus on a “set” base of clients (over 500 professionals in over 150 companies) I get to see them over a longer period of time. Many of them help me understand how they use the product in their projects, and I even attend some DBA regular meetings. I see the way the product succeeds, and I see when it fails.

Something that has really impacted my way of thinking is the level of importance any given shop is able to place on testing and validation. I’ve always been a big proponent of setting up a test system and following a very disciplined regimen to make sure it will work in production for any new projects, and then taking the lessons learned into production as standards.

I know, I know – there’s never enough time to do things right like this. Yet the shops I see that do it have the same level of work that they output as the shops that don’t. They just make the time to do the testing and validation and create a standard that they will follow in production. And what I’ve found (surprise surprise) is that they have fewer production problems.

OK, that might seem obvious – but I’ve actually tracked it and those places that do the testing and best practices really do save stress, time and trouble from that effort. We all think that’s a good idea, but we just “don’t have time”. OK – but from what I’m seeing, you can gain time if you spend a little up front. You may find that you’re actually already spending the same amount of time that you would spend in doing the testing, you’re just doing it later, at night, under the gun.

Food for thought.

Backup those keys, citizen

BuckWoody — Tue, 20 Apr 2010 12:14:50 GMT

Periodically I back up the keys within my servers and databases, and when I do, I blog a reminder here. This should be part of your standard backup rotation – the keys should be backed up often enough to have at hand and again when they change.

The first key you need to back up is the Service Master Key, which each Instance already has built-in. You do that with the BACKUP SERVICE MASTER KEY command, which you can read more about here.

The second set of keys are the Database Master Keys, stored per database, if you’ve created one. You can back those up with the BACKUP MASTER KEY command, which you can read more about here.

Finally, you can use the keys to create certificates and other keys – those should also be backed up. Read more about those here.

Anyway, the important part here is the backup. Make sure you keep those keys safe!

What to leave when you're leaving

BuckWoody — Mon, 15 Mar 2010 14:09:00 GMT

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, but was angry at the company and told me "hey, I know this is going to be hard on you, but I want them to know how important I was. I'm not telling you where anything is or what the passwords are. Good luck!" He then quit that day.

It took me about three days to find all of the servers and crack the passwords. Yes, the company tried to take legal action against the guy and all that, but he moved back to his home country and so largely got away with it.

Obviously, this isn't the way to leave a job. Many of us have changed jobs in the past, and most of us try to be very professional about the transition to a new team, regardless of the feelings about a particular company. I've been treated badly at a firm, but that is no reason to leave a mess for someone else. So here's what you should put into place at a minimum before you go. Most of this is common sense - which of course isn't very common these days - and another good rule is just to ask yourself "what would I want to know"?

The article I referenced at the top of this post focuses on a lot of documentation of the systems. I think that's fine, but in actuality, I really don't need that. Even with this kind of documentation, I still perform a full audit on the systems, so in the end I create my own system documentation. There are actually only four big items I need to know to get started with the systems:

1. Where is everything/everybody?
The first thing I need to know is where all of the systems are. I mean not only the street address, but the closet or room, the rack number, the IU number in the rack, the SAN luns, all that. A picture here is worth a thousand words, which is why I really like Visio. It combines nice graphics, full text and all that. But use whatever you have to tell someone the physical locations of the boxes. Also, tell them the physical location of the folks in charge of those boxes (in case you aren't) or who share that responsibility. And by "where" in this case, I mean names and phones.

2. What do they do?
For both the servers and the people, tell them what they do. If it's a database server, detail what each database does and what application goes to that, and who "owns" that application. In my mind, this is one of hte most important things a Data Professional needs to know. In the case of the other administrtors or co-owners, document each person's responsibilities.

3. What are the credentials?
Logging on/in and gaining access to the buildings are things that the new Data Professional will need to do to successfully complete their job. This means service accounts, certificates, all of that. The first thing they should do, of course, is change the passwords on all that, but the first thing they need is the ability to do that!

4. What is out of the ordinary?
This is the most tricky, and perhaps the next most important thing to know. Did you have to use a "special" driver for that video card on server X? Is the person that co-owns an application with you mentally unstable (like me) or have special needs, like "don't talk to Buck before he's had coffee. Nothing will make any sense"? Do you have service pack requirements for a specific setup? Write all that down. Anything that took you a day or longer to make work is probably a candidate here.

This is my short list - anything you care to add?

Did that change really fix the problem?

BuckWoody — Tue, 02 Mar 2010 13:31:51 GMT

When we’re heads-down on a problem, it’s sometimes far too easy to relax the method we should follow for troubleshooting. We’re supposed to gather as much information as possible, freeze the system as much as possible, and then develop the plan for the steps to correct the problem. Then we’re supposed to make a change, test the change, and solidify the change if it corrects the error.

Most of us have learned to research what really happened, what changed, and then search the documentation, web, forums and other sources for possible fixes.

We also know that “freezing” the system in our case often means ensuring there is a full backup before we change anything. If you don’t follow this rule; don’t worry – you will. The first time you’re at fault when it all goes wrong and you don’t have a way to put it back is the time that you’ll start with this step.

It’s from here on out that the tyranny of the now sometimes gets the best of us. We may create a plan in our head, thinking “this is a simple problem” but an hour later we’re not sure what we’ve touched. Settings have been changed (and most importantly, not changed back), code has been altered, processes have been run and at some point, the problem goes away. We breathe a sigh of relief and go on about our day.

But which “fix” fixed the problem? Or which combination of them? Sure, it might not matter right now – the problem is gone – but at some point it will come back to haunt us.

So the lesson for today (and I always point these diatribes at myself as well) is that we should always follow the scientific method, no matter how “trivial” the problem: hypothesis, test, control. The “control” part is where we change the setting back from the one you changed!

Have you backed up your keys lately?

BuckWoody — Mon, 01 Mar 2010 14:06:04 GMT

Did you know that you already have a Server Master Key (SMK) generated for your system? That’s right – while a Database Master Key (DMK) is generated when you encrypt a certificate or Asymmetric Key with code, the Server Master Key is generated automatically when you start the Instance.

So you should back all of those keys up periodically, and then store that backup AWAY from the server itself.

There are two reasons for this – first, if the drives get stolen and you’re storing the key backup there, well, that should be obvious why that’s bad. Second, you want to protect the keys in case the system is destroyed or you can’t recover the drives. You will need those keys if you have encrypted anything in the database to get the data back.

More here: http://technet.microsoft.com/en-us/library/bb964742.aspx

No, the standard Maintenance Wizards don’t get this data. And no, I haven’t seen it addressed in most of the maintenance scripts out there anyway – sometimes for good reason, but this means you need to take care of it manually, and then document where you put that backup.

How Does Microsoft Do IT?

BuckWoody — Wed, 03 Feb 2010 14:17:24 GMT

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, DBA’s, 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+ seats, especially when most of your company are a bunch of tech-savvy geeks? It’s a tough job, but the neat thing is that we tell you how we’re doing it – everything – right here: http://technet.microsoft.com/en-us/library/bb687780.aspx. If you want to focus in on just SQL Server, just check here: http://technet.microsoft.com/en-us/library/bb687798.aspx.

(By the way - I *totally* should be doing our marketing – isn’t that title catchy? My catch-phrases and product names would be a lot better than what we normally come up with. I’m just sayin’.)

SQL Server Best Practices: Use Roles When You Can

BuckWoody — Mon, 07 Dec 2009 14:49:46 GMT

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, it’s a good idea to apply permissions not to the individual users, but to database roles. The reason is that you can move users in and out of the roles without changing the permission scheme.

It also helps if you want to transfer the database to another server. All you have to do is script out the groups and permissions, and when you transfer the database to another system you simply add the users on that system to the proper roles – no permission changes needed.

Disk I/O: Microsoft SQL Server on SAN Best Practices from SQL CAT's Mike Ruthruff (& Prem Mehra)

aspiringgeek — Sun, 01 Mar 2009 18:44:00 GMT

While at the PASS Community Summit in November 2008, I had the pleasure of attending a handful of excellent presentations. One of the best was delivered by Mike Ruthruff (& not just because he shilled for my presentation on disk partition alignment later that day—though I suspect he contributed to my session being SRO).

Mike is a member of the SQL Server Customer Advisory Team (SQL CAT). He authored the deck with contributions from SQL CAT patriarch Prem Mehra.

Most of you probably know Mike because he is the primary author of the landmark white paper we all know-&-love & have read over-&-over again because we know how unbelievably valuable it is:

SQL Server Predeployment I/O Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

Mike provided a copy of his latest-&-greatest deck for publication here:

Microsoft SQL Server on SAN Best Practices

The deck includes the following:

Characteristics of SQL Server I/O operations
Best practices
- SQL Server Design Practices
- Storage Configuration
- Common Pitfalls
Monitoring performance of SQL Server on SAN
Emerging Storage Technologies
Additional Material In Appendix Section (not covered during session)
- How to validate a configuration using I/O load generation tools
- General SQL Server I/O characteristics
- How to diagnose I/O bottlenecks
- Sample Configurations

I think you'll enjoy this presentation—one of the best, perhaps the best of its kind ever assembled. ¡Yo! Only first-rate decks on this blog. Besides which, SQL CAT does nothing but the best. Get ready to be wowed by 50 slides of geekly goodness.

Administrivia

Jimmy May, MCM
SQL CAT Sr. Program Manager
SQL Server Customer Advisory Team, Business Platform Division
317.590.8650
http://blogs.msdn.com/jimmymay
Microsoft: Change the world or go home.