Voice of the DBA

Tag: syndicated

  • How to change the SQL Server service account

    It’s easy. First you click here:

    serviceaccount1

    Then you find the SQL Server 2008 or 2008 R2 program group

    serviceaccount2

    Open those and then pick the SQL Server Configuration Manager.

    serviceaccount3

    You might get a UAC message asking if you want to make changes to the system, and say yes. This application can change service accounts, which is a sysadmin function. The screen basically looks like this:

    serviceaccount4

    If you right click the SQL Server entry, the database engine, you can select the properties, and you’ll get this screen:

    serviceaccount6

    On this screen, you can change the service account, and this is where you should change it. There are the three built in accounts in the top drop down (Local System, Local Service, and Network Service). I don’t recommend these. Instead, I recommend you create a new Active Directory Domain account (or local user account), assign it no rights, and then use the lower radio button to select the account.

    serviceaccount7

    Note that you do can search for it, and also you need to enter the user account. The password, while conforming to your domain policy, shouldn’t be one you use elsewhere. Make it a long, impossible to guess combination of stuff. You don’t need to recover this or log on as the user after you’ve assigned it to SQL Server. If you need to recover the password, just change it.

    A couple quick notes, on the Service Tab you can set the startup mode (Manual, automatic) for the database engine.

    serviceaccount8

    You can also change startup parameters here, on the Advanced tab.

    serviceaccount9

    That’s it, it’s easy, and it’s how you should change the service account for SQL Server.

  • T-SQL Tuesday #25 – T-SQL Tricks

    TSQL2sDay150x150It’s time for T-SQL Tuesday again, and this time Allen White (@SQLRunr | blog) is asking for your tricks. If you want to participate, read Allen’s post and learn how.

    The question this month is: What T-SQL tricks do you use today to make your job easier?

    My Tricks

    I don’t have any great whiz bang tricks in T-SQL, and I’m sure there are more than a few people that can out-code me with their. However I do like to make my job easier, and so I have a couple of administrative tricks for use with your T-SQL environments. These are the ways that I save time, and work more efficiently.

    The thing that has helped me most often in my career is to keep little snippets of code handy to that I make few mistakes and save time. These days I do that quite often with SQL Prompt, a third party tool from my employer. It basically implements intellisense for SSMS, but more importantly, it gives me shortcuts.

    However the biggest advantage to me is the Snippets in Prompt. There’s a feature that allows you to type a shortcut and then press “Tab” and have that shortcut replaced with a longer section of text. For example. I have this shortcut:

    prompt1

    If I type “zqd” in SSMS, and then hit tab, the T-SQL in the “code” box above appears. There are a few very frequently used slices of code that I can insert like this, without taking my hands off the keyboard, which is very handy. There’s even a whole snippet manager in Prompt that has pre-defined, and custom, snippets.

    prompt2

    The most often one I use is “ssf”, which inserts this:

    SELECT TOP 10 * FROM

    So do you need to buy SQL Prompt? No, but if you do, tell them I recommended it so my boss with be happy and maybe send me a nice bonus next Christmas.

    A very similar functionality is in SSMS. I actually used to heavily use templates in the old Query Analyzer days of SQL Server 7/2000 and this has continued in Management Studio with the Template Explorer

    template1

    I can drag a template from the explorer on the right into the code window and the code appears. I’ve pulled in the backup template. You can even add your own:

    template2

    It’s easy to do, and you can read more about Template Explorer in BOL.

    However if you’re like me, you move around, you use VMs for coding, and you want to be sure that your tools are on all these machines. There are a few ways to do this:

    • portable drives
    • cloud sevices

    I guess these are both the same thing, just implemented differently. I’ve used both ways, and while I do carry some flash drives, and hard drives, with various items on there, I find that I can never quite keep these up to date, and they’re really emergency drives for me in the event I don’t have connectivity.

    The primary way that I manage mode snippets, templates, etc. is by putting all my code in centralized places. For Prompt and SSMS, these locations are known, and while configurable, I stick with the defaults. For me this means I have three folders to track:

    • SQL Prompt default snippet folder
    • SSMS Templates folder
    • \SQL in my Documents folder in Windows

    All three of these folders are the same on all my machines, and I use a cloud service to keep them in sync. For me, I have two difference services in play, mostly for testing, and I see little difference between them. I have Live Mesh, a Microsoft service, for some folders, and DropBox for others. From what I’ve seen, they both work essentially the same, though DropBox is a little smoother for me with the Apple integration of some apps. That probably doesn’t matter for most of you, but it’s a difference. Live Mesh works on my Macbook, but not on the iPhone.

    There are other cloud services, and you can choose the one that works well for you, but I highly recommend you have a script library, as well as a snippet/template library, and you use a cloud service to be sure you can access those files if you are away from your primary machine. You might be surprised how handy this is when working on a server or remote machine.

    That’s my T-SQL Tuesday trick for T-SQL, better script management to make your work easier.

  • Working with SQL Server Third Party Vendors

    I ran across Denny Cherry’s (@MrDenny) blog on Fixing Vendor Performance Problems in which he outlines three methods of dealing with the. His list is basically

    1. Work with the vendor
    2. Do nothing
    3. Fix it yourself

    Denny recommends that you do #3, and add indexes, statistics, etc., and script them so that you can drop them if the vendor needs to access the database, and add them back afterwards. This is done to avoid voiding a service contract.

    I’ve done that in the past, but I try to avoid it. My first inclination is always to choose #1. I have had great success with this in the past, with a number of vendors, and it’s the item I recommend. Try to work within the system and make change, rather than just go "cowboy” right away and make the changes you think need to be made. I’ll give you the how and why I recommend this.

    How

    How do you get the vendor to make changes? The same way you get the best support you can from a vendor: you have a logical and rational discussion, explaining your thoughts with backing documentation.

    I have had vendors insist that the “sa” login was needed, and avoided it. I have fixed performance problems by getting vendors to add and drop indexes. I have convinced vendors that they application could run, and be supported, on an instance with other databases.

    All of this has been done with tracing the calls, analyzing performance, and then explaining where I saw issues with the vendor’s application. lt wasn’t done to prove I’m right, it wasn’t done to tell the vendor their code was sh*t, It was always done with me in the middle, along with the support person, between my boss, and their boss.

    Talk to the vendor like he’s a friend coding in the next office, and he’s doing his best, but perhaps doesn’t understand why something isn’t working well. Give them supporting documentation, show them execution plans, show them before and after views on test systems, give them reasons why a chance is needed. Even offer to be a guinea pig for the tests. I’ve done that before, and we convinced a vendor that they didn’t need to index every column in a few tables based on six months of evidence from our system.

    If they argue with them, perhaps they have good reasons. Get them to prove to you that they have logic and evidence on their side. They might, but if they don’t, then you ask to talk to their manager, or their development lead, and work through the argument there. Most vendors don’t want crappy performance, but as Denny mentioned, they often just don’t have expertise.

    Work with them, not against them.

    Why

    Why should you spend time educating and teaching the vendor about SQL Server performance? Obviously you want to maintain your support contract status. There’s nothing worse than violating this and then having a problem that doesn’t get supported. That’s an ugly conversation to have with your boss.

    You also don’t want issues with upgrades. I have seen more than a few problems with upgrades because someone “touched” the system. Make sure that you not only document the changes, but get the vendor to document them in your account. They might even work with you on upgrade scripts.

    Lastly, you want to be right. There are plenty of people out there that make mistakes, or make a decision based on an incomplete picture. You might be sure that an index is needed for this query that runs every day, but maybe the vendor tested it and found a better index, or they found it caused worse performance for some other queries. Maybe you’re misreading the evidence yourself.

    If you are forced to explain yourself, and can do so in a way to convince someone else, you should have more confidence that you are making the correct decision.

    Plus it gives you something to blog about and put on your resume. Winking smile

  • PASS Speaking Results

    I got the PASS speaking results today on the new speaker portal. They posted the top sessions as well, overall and in each track. I did two sessions, and here are my summarized results:

    Branding Yourself for a Dream Job

    67 attendees, 39 surveys.

    Overall, I was mid 4’s (4.59, 4.72, 4.74, 4.56, 4.56) for the various questions. Anything over a 4 means that I was doing a good job overall. I got some great comments, many of them in caps. I hope that means people were excited, but in any case, here are a few:

  • IT IS TIME TO VAN SP_UPDATERESUME
  • GREAT, GREAT PROFESSIONAL DEVELOPMENT TIPS! ESPECIALLY LIKED THE PART ABOUT BLOGGING. WELL DONE!
  • IDEAS FOR FINDING EMPLOYEES AND OWN CAREER
  • THE IMPORTANCE OF MANAGING MY IMAGE
  • STEVE OPENED MY EYES TO THINGS I ALREADY KNEW BUT WASN’T REALLY AWARE OF.
  • LEARNED SOME GREAT TIPS FOR IMPROVING "BRAND" TO HELP IN KEEPING/FINDING JOBS
  • GLAD I CAME. VERY ENJOYABLE AND INFORMATIVE.
  • SUPERB PRESENTATION!
  • I GOT SOME GREAT TIPS TO IMPROVE MY ONLINE PRESENCE.
  • DESIRE TO PROMOTE ONLINE PRESENCE
  • I ALREADY STARTED TO UPDATE MY ONLINE PRESENCE AND I’LL BE UPDATING MY RESUME WHEN I GET HOME.
  • GREAT STUFF! MORE GOOD INFO THAN EXPECTED!

    • Nice comments, and I enjoyed the session. Got some great questions during this presentation.

    In the PD track, I was third, and I regret not going to see Plamen’s talk on great presentations. The slides look good.

    The Top Ten SQL Server Skills You Need

    188 attendees, 80 surveys.

    I was worried about this session a bit because it was near the end of Friday, the last day, and it’s a basic 100 level session. Also, I was in a huge room, the one that Adam Machanic filled up with 500+ people that morning.

    However I was pleasantly surprised to see similar scores here in the mid 4s (4.43, 4.66, 4.6, 4.45, 4.34). I guess that means I do a decent job presenting, even when I’m not at my best. One comment caught my eye:

    • Voice was more monotone than yesterday. Sounded tired.

    Very, very true. I was wiped out, and after this session I left immediately for the airport for a flight. I dislike speaking late in the day, and after being in Seattle for a week, I was definitely tired.

    This is a tough session since it’s basic, and I suspect some people come to see me, expecting something more. I was actually surprised this session was picked by the program committee, though clearly wanted. I was more surprised it was scheduled so late in the week. I had some nice feedback:

    • several of the tips were useful and possibly the links to more information is most helpful
    • go home and use what I learned
    • finally someone at this conference who teaches people new to sql server the basics and not something over my head Thanks Steve!
    • how to train my entry level dba’s

    Glad I met the need. Some not to great feedback, though I’m not sure how I can fix some of these. It’s listed as a 100 level session.

    • Could have said for beginners. This was a very low level presentation of many basics that most dbas do every day.
    • I expected sql server dba function more advanced than types of joins and how to create basic indexes
    • Could use a little more just like most sessions

    I’m not sure what “more” I could do, especially with 10 things. I suppose I could cut it down, but my aim was to point people in a direction, not teach them deep skills.

    I think I’ll retire this one, unless I get a call for it. I don’t love it, and with a mixed skill level audience, I always find people complaining that it’s too basic.