Right now, this very moment, there’s an ugly piece of work tapping on your shoulder – and I don’t mean your boss.
You agreed to do it months ago during the 2012 planning sessions, but you never really expected January 2012 to arrive. Now that it’s here, you’re starting to get nervous because you’ve never done it before and you’re not quite sure where to begin.
I’d like to help.
I believe I can’t be successful unless other people around me are successful, and that means you. I also believe that if there’s anything out there I haven’t done before, I bet either somebody I know has done it, or a few other readers have. Between all of us, we can figure it out. In the year 2012, there’s no need for you to feel alone going up against anything. Whether it’s implementing replication, fixing a performance problem, getting your blog back on track, losing weight, or beating cancer, we’re here for each other.
Leave an anonymous comment describing the work that scares you. We (and by we I mean the internet) will talk about our first time doing it, what we wish we’d have known when we did it, and our favorite getting-started resources.
94 Comments. Leave new
Setting up regular backups for our databases, and log shipping for 2 databases that operate on a 24×5 basis.
You would be AMAZED at how many shops don’t do this. You’re certainly not alone there. It’s the first thing I check when I start working with a new client because I want to make sure they’ve got a backup in case things go awry. I don’t want to be That Guy who causes them to lose their business without a backup.
The easiest way to get started is to do full backups daily to a network share. That way, if any hardware goes bad, you’ve already got a backup off the box. That’s a controversial answer, though, because some folks would rather have the backups go locally because it’s faster. (My two cents: if you gauge backup success by speed, just back up to \nul and they’ll be blazing fast.) I talk more about that at https://www.brentozar.com/go/backup along with listing best practices.
Then I’d do transaction log backups at *least* once every 5 minutes, if not every minute. I know it generates a lot of files, but you can get scripts to automatically restore all files from a particular directory. I’ll email you one Jonathan Kehayias gave me. To make your life easier, put each database in its own backup directory, and name the backup files with databasename_yyyymmdd_time_fullOrLog. That way they’re sorted in the order you need to restore them.
If you’re doing log shipping, the native wizards in SQL Server 2008/R2 are much better than past versions, and the backup compression built into 2008R2 Standard Edition is a great free feature. If you’re not on R2 yet, I’d check out a third party backup compression product like Quest LiteSpeed, Red Gate SQL Backup, or Idera SQLsafe. All three of those have built-in tools to automate log shipping. Don’t write your own scripts whatever you do – log shipping’s been out for a decade as a technique, and there’s a lot of scripts out there. I don’t use ’em myself – I prefer a tool because I want someone else (a developer, a sysadmin, or a manager) to be able to reinitialize the log shipping if things go wrong.
Monitor the backup target’s free space so that it doesn’t run out. When it does, your backups stop working, and your database log size starts growing. Next thing you know, production’s run out of drive space, but you can’t fail over to the secondary box because log backups haven’t worked in a while.
Finally, decide with the business how you want failover to happen, and then write a script to go live on the secondary box (using RESTORE WITH RECOVERY). Put the script in a shared location and practice it with your team – sysadmins, developers, etc – so that any team member can do it if you’re not around. Have a standard agreed-upon decision process about going live on the secondary box because you WILL lose data when the primary box goes down without finishing its t-log backups. Put down in writing that you may lose up to X minutes of data, and the longer t-log backups haven’t been working, the more you stand to lose.
I agree with just about everything that Brent said. He and I will never agree on the backups to local vs. online contraversy. I don’t think either of us are movable on that topic. That debate is for antoher day though.
I would add to what Brent said that when I am faced with a new, existing server, the first and last thing I do is set up backups. Failure to have backups is one of the few unforgivable sins if you are a DBA. I have a script that I call my standard backups. I run it and 10 seconds later, I have weekly full, daily differential, and half-hourly log backups running on the server. It’s extremely customizable and adaptable, so I can add it to any server and know that it’s at least minimally protected.
I’ll move on to other stuff like setting up integrity checks, checking the performance counter/waits & queues of the instance, capacity, existing HR/DR, etc. I’ll eventually circle back around to backups. I’ll work with determining or defining the uptime and data loss (RTO and RPO) requirements for the server. This will often lead to some tweaking of the standard backups.
The possibility of jumping to a new company. I’ve been at the current place for a year and a half, and it’s my first official “MS SQL DBA” role in my 15 years of IT experience. I have learned so much and grown with my skills in both SQL Server and IT in general.
Other than that, setting up mirroring on our Production web presence DB Server so I can not only provide some DR for the Data that hosts our external face to the internet, but also so I can upgrade from 2008 to 2008 R2 with minimal downtime. I’m pushing the IT organization to set up some type of redundancy on the web / application side of things as well.
Jason – the easy one first, heh, mirroring. I’m a big fan of Robert Davis’s book on database mirroring. It’s the only resource you need.
About the job – man, there’s never been a better time to be in the SQL Server DBA job market. There’s so many companies looking. I thought offshoring would be a problem, but that just hasn’t materialized. Companies still want their DBA to be physically in the office. If you get to the point where you’d like to see what’s out there, email us your resume at help@brentozar.com. We always know folks looking, and we can do anonymous advertising on your behalf. I can post tweets saying there’s a DBA in the market in (insert location here), and then other folks can contact us without revealing who you are.
Thanks Brent, and thanks Brent! I will check out Robert’s book and blog. I agree that there’s never been a better time, I’m getting a lot of interest in my linked in profile, more than ever in the past 4 years.
Setting up mirroring might seem daunting at first, especially when you’re using it for migrating from 2008 to 2008R2. The first step is to make the process into a checklist. Write down every step that you think you’re going to execute along the way. Once you’ve written down the steps at a high level, break them down into smaller items until each step can become an executable T-SQL script or something you can take action on – e.g. “Give developers new connection strings.” You’ll want to script out security settings, sp_configure settings, and any SQL Agent jobs that might have to execute on the other server (make sure you disable those job).
Robert Davis and Ken Simmons wrote an excellent book about SQL Server mirroring: SQL Server 2008 Mirroring. Despite the witty title, it’s a great guide to setting up and monitoring mirroring. Robert has also written a ton of blog posts about database mirroring, including this gem: Transferring Logins to a Database Mirror.
Ideally, you’ll be able to set this up once in your development environment and test your process before switching over to the production environment. By running through your checklist once, you should spot potential problems and be able to have scripts prepared to execute instead of having to scramble on the production system.
Thanks for the plug, Brent! That’s another 25 cents I owe you. 2 more and I can buy you a beer at the Summit. 😉
Joking aside, there’s also a lot of information about mirroring on my blog that isn’t in the book, but the blog postings won’t take you from level 0 to production ready with mirroring like the book will. If you run into an issue with mirroring that isn’t covered in the book, odds ar eprobably pretty good that I covered it in my blog or didn’t cover it because someone else had already covered it well. If you have issues and don’t find the answer, you can always leave a comment on my blog (www.sqlsoldier.com) or hit me up on Twitter (@SQLSoldier). If using Twitter, I’d recommend adding the #sqlhelp hash tag to the question so that more people will see it and possibly answer it.
Writing up my thesis (and I don’t think there’s any point in being anonymous for that).
The write up is all that’s left to do, but those lit survey chapters (and I have to write 2) are just horrendously difficult. Academic style writing (not the casual or semi-casual we typically use on blogs or books) and every statement has to have a reference.
Gail – wow, yeah, the thesis is hard work for sure. Even when I just read Wikipedia articles, I’m amazed at the work that goes into those.
35 pages at the moment, excluding title page, abstract, ToC and reference list. 8 of those pages are research results which will need rewriting completely, once I get the lit survey finished. 🙁
Got to get this thing done so I can pick up a couple other projects that are sitting on the list.
What scares me for the coming year is that we’ve been told this year we will get our main application off of a 14+ year old VAX and onto SQL Server and .Net. The project to get it off the VAX has been on-going for at least the last 7+ years and is long overdue. It is the most important system we have and is highly visible politically. It’s what our organization is based upon. Without it, we’re in big, big trouble. Especially if the VAX dies because I believe there is no support for VAX systems anymore. I’ve rarely worked with this system but chances are pretty good I will be helping with it. At least part of it is already in SQL Server 2005. This project will require the coordination and cooperation between pretty much all of the developers and the DBAs (at least 2 of us). It currently involves transactional replication as well which is one of my weak points. I’m sure I’ll be learning it real fast. 🙂 At the same time I need to learn Oracle administration for other systems. Not sure how much time I will have to learn Oracle or even to learn SQL Server 2012. This conversion project of our main system will probably take up the majority of my time. It’s a fairy complicated system as well. Of course, the developer who knows the most about this system is retiring at the end of January. Figures, huh? 🙂 So I guess my point is I’m afraid my skills are going to become stagnant this year and I’m not going to be able to devote time to learning new things. I’m also afraid of screwing up or delaying this extremely important project. Maybe it’s just a lack of confidence. I don’t really know. 🙂
Rebecca – well, hey, on the bright side, if you’re forced to implement transactional replication, you’re going to learn a LOT, and it’s a valuable skill. It’s hard for companies to hire people who are comfortable working on transactional replication, and when it works, it’s a killer feature. Really enables a lot of cool stuff from a business perspective.
I’m not good with transactional replication either, but it’s funny that two of Robert Davis’s favorite subjects have come up here already! He wrote a good post on some of the gotchas recently: http://www.sqlsoldier.com/wp/sqlserver/undocumentedgotchasoftransactionalreplication
Thanks. Luckily, it’s already implemented and I’m sure we’ll be adding to it. At least it’s a good thing to learn. Glad to hear I’m not the only one who isn’t good at it. 🙂 Thanks for the link!
Heh, vax… good luck!
Thanks… I think 🙂
I honestly can’t think of anythign that I’m scared of this year. In fact I’m excited to see everything that SHOULD be happening here this year.
I’ve got a SQL 2000 server that SHOULD be going away, and our old 2005 prod server will most likely go away. After that the only 2005 instances will be express (and i’m hoping to migrate all of those together at least onto a 2008 standard license, but that’s a long shot). Then everything will be on 2008+.
I’m also excited to see about updating our servers to 2012 (we kind of skipped 2008 r2 except on a couple of instances).
So yeah, less scared, more excited!
David – wow, good for you that you’re able to get rid of 2000. Most of my clients have taken the attitude that 2000 can stick around forever inside of virtual servers. As long as they’re off the boat anchor hardware that’s out of maintenance, they don’t seem to mind being on unsupported software. (sigh)
Well… the 2000 server was just PtoV’d, and killing it depends on if the company can get it’s arse into gear and get off of project 2003 and into project 2010. That’s been going on almost a year now….
Putting together a plan for Peer-To-Peer replication. The word replication itself already give me a tummy-ache 🙂
Yanni – heh, yeah, that one is indeed a monster. I’m glad we’re helping you with that one. 😀
Most of our in house development work and software has been replaced with vendor stuff. This year our challenge is to support vendor database/application as opposed to our own.The database ownership is an odd one not defined in the contract, it is not clear from business point fo view on who owns what and who is responsible for what. We have a lot of compliance issues to answer to (healthcare)..and the year started by imposing some security restrictions on what they do, which went down poorly, to put it mildly. I am not looking forward to a whole year of this, and would love to hear more on challenges faced by DBAs with vendor software and databases that they have to host and support in some way.
Mala – ooo, that’s a very popular issue. Mike Walsh and Erin Stellato gave a presentation about that topic at the PASS Summit this year, and it was voted as one of the top sessions. Did you go to the Summit and get the DVDs, by chance? If not we may be able to pull some strings and get you a copy of just that one session.
Thank you Brent, i did attend the summit but missed that session. Now waiting on dvds and hope it will arrive soon. Will take you up with that offer if it does not arrive this week :))
This is not the first vendor database we are taking care of. There have been others before just not as mission critical. In this case there is lot of mix up on who does what and how far do you go limiting dba from vendor side who is very used to being a sysadmin and doing exactly what he wants. I can handle any technical challenge, matter of fact i welcome them but i don’t like the politics. Let us see how it goes.
I have already had replication running for a few years now… but with more subscribers these days, the whole thing seems to break much more often, and it is a nightmare syncing the disjointed data back into something cohesive before restarting it again…
That said, what I really need to do is recreate my db’s front end on a web platform, which I have never done before. We’ve always been a Windows shop and I would be fine with using ASP but others are pushing for open-source… like the LAMP setup except using SQL Server.
I can’t seem to get Windows security working on the type of setup though, but it needs to happen cause I am behind the times for our app’s font-end solution and if the whole thing was web based I would be reduced to only maintaining one SQL Server again which would be a dream come true!
Wendy – we’ve got clients doing both approaches – either using the Microsoft stack on the front end, or using open source stacks. The challenge with using open source front ends is that the support always lags behind what you get with the Microsoft stack, and it’s harder to find good troubleshooting resources. Personally, I wouldn’t even bother trying to use Windows security on that type of setup – I’d stick with SQL authentication to make your life easier.
At the end of the day, it comes down to what you and your developers know. Is it just you, or do you have other folks working with you to build the front end?
It is all me lol. I am the DBA/front and back end developer/data analyzer/database support for my organization.
The front end has been MS Access based for the last 10 years or so… time to move forward!
I work at a school so the web traffic needs to be encrypted and use SSL and whatnot so that’s why I was trying to use Windows authentication, plus that’s what I’ve been using so permissions are already set up using Windows groups but…
I have been eyeballing SQL authentication to make my life easier 🙂 or at least for now then can go back to Window at a later time if I chose to…
So anyways… it’s all on me!! So any streamlining tips for going form Access to web would be appreciated! (That was another reason I was looking at ASP – I am already familiar with VB and wouldn’t have to learn another language while developing the new front end :P)
Wendy – ooo, I did this myself back in 2000, so my only recommendations here would be wildly outdated. I used Macromedia Dreamweaver UltraDev, a product that’s not around anymore from a company that’s long since been acquired. 😀 But if you already know VB, yeah, ASP.NET makes perfect sense.
I have a SoX audit coming up in May, while I have been on the receiving end of a PCI-DSS audit SoX is a bit more vague as to what I need to do.
I have tried getting the business to point me in the right direction with the controls but I have been told to write the policies myself.
John – you’re definitely not alone there. I’d start with my favorite compliance resource, the SQL Server Compliance Portal:
https://www.microsoft.com/sqlserver/2008/en/us/compliance.aspx
You’ll find that audit info out on the web is very vague indeed. There’s no simple checklist you can follow to become compliant. Every auditing firm seems to handle this stuff differently, and some of the auditors I’ve worked with have been absolutely idiotic in the way they interpreted the guidelines. Some go so far as to say that even a solo DBA can’t be sysadmin.
Hi John
SOX has been a regular pain for me over the last fifteen months. Unfortunately, I haven’t got round to blogging it in any detail.
From what I recall without checking my notes, the main things I’ve had to demonstrate are:
* backups – schedule and logs, including failure alerts
* restores – logs (regular auditing of the various backuphistory / restorehistory etc tables in MSDB helps here)
* application-level access – who has access to the apps
* what level of access they have within the application
* who has access to the data without using the application
* what level of access they have there
* auditing non-application access
“And a big side-order of WHY”
In our environment they are primarily concerned about access, not much about backups or restores. Everything everyone does is audited, including what DBAs do. And we have a ticketing system to which it needs to be linked. So if you dropped a table you better have a request from an authorised person who has that script as part of it. Vendors who do things by seat of pants are not used to it and it is a huge pain to teach them.
Thomas – I’m still laughing hours later about “a big side order of WHY.” That’s awesome.
Brent – I wish I could claim authorship of that. It’s a Douglas Adams / Hitch-Hiker’s Guide to the Galaxy. As far as I remember, Zaphod Beeblebrox has just found Zarniwoop in an alternative universe that’s in Zarniwoop’s office (hey, don’t blame me, I didn’t write this), and is filling him in on just why they are there.
Zarniwoop: … any questions?
Zaphod: Yeah. I’ll take who, what, when and where, followed by whither, wether, whence and wherefore, and a big side order of why.
Ford: And the wine list!
Sorry. Irrelevant mental meandering. Please, carry on…
I’m actually fine with all of the technical work that I am aware of coming up in 2012.
What scares me is the fact that I plan for 2012 to be quite a life altering transformation for me.
My main goal is to simultaneously grow and shrink myself. Grow my presence in communities, both virtual and physical, work on putting together a presentation or two and mustering up the courage to put myself out there and give them. Shrink my physical footprint. While weighing 400 pounds at 5 foot 10 has its advantages (I’ll have to think of some later) I’m tired of it. I know the series of events that led me to put up this wall but I think I’ve finally gotten to the point where I am ready to move on.
It’s time for me to begin making the transition from who I was to who I’m supposed to be and this is the year to start making it happen.
Needless to say, that kinda/sorta scares the crap out of me.
Congratulations on both of your personal journeys this year, Jeff! I started the journey to shrink myself about 6 weeks ago. A decade of taking really bad care of myself is going to take a long time to fix. It scares the heck out of me, too, but know that there are a lot of people going through this with you. One of the best things you can do is find a supportive community to take part in. There are some communities on Reddit that might be helpful, or you could join the Nerd Fitness Rebellion. I went with the second route, but I’m not very active on the forums there.
Growing your presence in communities and speaking can be scary at first. I remember talking to a friend about writing one of my first blog posts, I was terrified about being wrong and even asked, “What do I do if I’m wrong and someone corrects me?” His response was, “Then you’re wrong, you learn from it, and you get smarter.” Turns out I wasn’t wrong very often and every time that I was wrong, some awesome people stepped in and helped me get things right.
Just remember to have fun with everything that you’re doing – both presenting and getting in shape can be a tremendous amount of fun. If something isn’t working for you, find a different way to try things and you may find that you love something that you used to hate. You could find that presenting is a blast, but only when you use sock puppets. It’s taken Brent a good 8 years to get over his sock puppet love.
Jeff – woohoo, congrats on making the decision to go for it! I salute your upcoming work. The cool thing about work like this is that it’s entirely self-decided: you’re the only one who can hold you back. The bad thing is that it’s really easy to hold yourself back, hahaha.
I would second Jeremiah’s recommendation on NerdFitness – I’m giving that a shot this year myself.
We’re planning to upgrade a dozen or so production SQL Server 2000 instances to 2012 this year… that’s quite scary. I think the scariest part will be migrating the gazillion DTS packages and jobs that form our daily batch processes – sadly no time to re-write them all in SSIS.
I feel for John who has a SOX audit coming up; what an incredible amount of BS that you can’t avoid 🙁
Ben – ouch, I feel that DTS pain. Have you seen PragmaticWorks’ DTS xChange product? It’s not cheap, but it’s cheaper than rewriting the packages yourself. They also have offshore DTS package migration services. If I was going to pick a company to handle that for me, that’s who I’d choose. (This is the second time I’ve recommended PragmaticWorks in a single post, heh – I’m not on the take, I swear!) Those guys have been doing DTS conversions since SSIS first came out, and I’ve had a couple of clients use them successfully.
I’m concerned about recommending SharePoint + SQL 2012 for our company. SharePoint was explored and rejected by IT two years ago, but that was before the BI stack became so tangled with it.
There’s a mentality of fear-based resistance to change in the IT department. However, my bosses have the final say and can force change. I don’t want to damage the trust they have in my judgment by pushing hard for SharePoint and having a disastrous (not to mention expensive) implementation. Plus, I don’t have any experience running a SharePoint environment. The combination of no experience with SharePoint and staking my reputation on its success is what scares me the most about this coming year.
I’d pick a personal project and see if you can’t implement SharePoint on your side project before going full-bore with it. I know that’s not the ideal solution, but it will give you some exposure to SharePoint before staking a larger part of your reputation on it. If that’s not feasible, try to identify a low-risk way to get started with SharePoint so you can ease the team into the new tool. SharePoint, like many things, is just a tool. While you can use a 4″ stiletto heel to drive in nails, you’re better off using a hammer or a heavy work boot.
Thanks, Jeremiah. I’m going to try it on a VM first, but it won’t be quite the same with domain/firewall issues. I worry the problem may require a solution faster than I can present one. I’m certain this is the year where we need to implement a more unified system for sharing and delivering data. There’s tremendous opportunity to do something great for my company. Hopefully I can get a demo platform going on VM’s and build trust (and something really cool) from there.
This might sound goofy, but if I was going to stake my reputation on a piece of software I’d never implemented before, I think I might actually choose SharePoint as the one to run with. Users LOVE it, it’s been out for quite a while, and there’s a lot of documentation and training courses for it.
If you’ve got the budget for it, I’d check out a one-week onsite visit from PragmaticWorks. They do a lot of BI-in-a-box deployments.
Trying to change configuration on a small server in a rural medical office. I’ve never done this before and I feel like there’s a lot at risk. But the Doctor trusts me to perform the work and I’m sure that I can do it. Just nervous!
They use a vendor based product and would like for me to add some custom reports to it. Another great challenge!
Looking at some of the comments, maybe I can setup a VM and try it there — why didn’t I think of that already?!
So hmmm… guess I took care of some of the nerves already! Thanks!!!
Judy – yeah, I’d agree with doing a VM or a separate machine there just for your own work. The last thing you want is to be thrown under the bus by the software vendor, saying you’re the reason they can’t check in patients. (sigh) Vendor work can be a little tricky! But if you write it in a VM, like with your own mini SSRS setup and maybe the free SQL Server Express Edition to store report config data, you won’t have to worry as much about your work interfering with anyone else.
What scares me about 2012 is trying to figure out how to setup sql server, how much memory, CPUs, etc. Currently have an active/passive cluster setup. I really don’t like being forced to do something I am very uncomfortable doing.
forgot to mention the boss wants it on a vm server.
Anonymous – This one’s a little tricky. I actually don’t recommend doing SQL Server clusters in a VM. I love clustering, and I love virtual machines, but the combination of those two adds a little more troubleshooting pain than I’m comfortable with on a 24/7 machine. Technically it’s supported, but it slows down the process of troubleshooting. If those databases absolutely have to be up 24/7, I’d rather have them on a physical cluster rather than a VM. If you want to shoot me an email at brento@brentozar.com to help discuss it with the boss, I’d be happy to do it for free just to help open his eyes and make sure he understands what he’s getting into.
I’m at a small college that migrated away from an old proprietary netware3-based solution that was no longer supported to a new system running sql server 2008 R2. We went live 2 months ago. The decision to change corresponded with my hire, so I only had to support the old system long enough to get rid of it. The old system managed grades, payroll, transcripts, general ledger, donations, admissions, everything.
What scares me this year is three things:
1. Support and hand holding for all the users that have been torn away from a system that was in place for 14 years – literally forever (longer than any current employee with only 2 exceptions). Thankfully one of them is on my team.
2. The new system ships with minimal executive dashboards. A big promise from the new system is better reporting. I’ll have to deliver on that this year, and I’m on the only person here with any development skills at all.
3. That I don’t do something really stupid to break our new software. It needs a lot of hand-holding as we work out the quirks in the conversion and aligning our business processes with how it wants to do things. That occasionally means scary UPDATE queries. Of course I have a sandbox to test in first, but that only takes you so far…
Joel – ouch. This is scary because there’s not just technical challenges, but also political challenges. I tell you what, though, when (not if) you pull this off, you’ll be in a very good position to do management or consulting! This is very much what consulting & management is all about. I’d make my resolution not to learn anything new technically, and instead do some reading on interpersonal skills to try to smooth over the bumps. As crazy as this sounds, I’d recommend The No Asshole Rule:
http://www.amazon.com/Asshole-Rule-Civilized-Workplace-Surviving/dp/0446698202/ref=sr_1_1?ie=UTF8&qid=1325597725&sr=8-1
Like many folks here, 2012 is an year for me where I am supposed to master the transactional replication so I am a bit nervous.
In 2011, I was a DBA with 3 years of exp. who was supposed to implement transactional replication in our production systems. This was a golden opportunity for me but a scary one too because I had never implemented this and till now nothing made sense when I read articles about it.
1) I implemented transactional replication using wizards for smaller databases in our production. I separated the reads and I got a pat on my back for scaling out reporting work where earlier linked servers were used which are a pain. The database was fairly small so I never worried much.
2) Came July 2011, I was asked to implement the same for 15 databases and total size was around 300 GB.
3) Used wizards again and got it working. Felt good.
4) Then came a series of days and nights when I got troubled . I noticed that whenever my senior DBA generated a new snapshot ,all the tables were created from scratch, we lost all index upfront and all the reports started timing out because a lot of tables were above 500 MB in size.
I then explored replication options where at one place a nice feature was kept
” Truncate data if object already exists”
Now, I never lost any indexes or table permissions.
finally I cheered up
5) Replication started failing due to bad data, old data type and I was crying again.
I cried, shouted at my computer, contacted Brent Ozar, Paul Randal, Hilary Cotter,Paul Ibson and finally got things moving.
6) After wizards, I learnt how to find problems in failing replication using distribution database objects.
Now, I felt like a hero.
2012 means a new set of challenges. I like challenges and want to be part of error resolution because that experience teaches you like a gem but you need to take a few capsules to bear the pain in our a$$.
Great post Brent! I just love all the Ozars, Randals, Gails, Soldiers, LaRocks, Farleys, Shieas(Linchi), Jonathans, Berry, Woody, Jeremias, Little(Kendra), Mechanics…. You are an asset to SQL Server world!
Regards
Chandan
Chandan – ha! Glad I could help, but if I could make one suggestion on replication is to not go live using just the wizards. 😀 As you’ve found out, they work great with small databases in isolation, but when you get lots of databases, lots of moving parts, and lots of people touching stuff, you gotta understand how it works under the hood. You gotta be able to troubleshoot with scripts. (I’m not the guy to do that, either.)
Doesn’t massively scare me, but it’s still got to be done; Setting up my first SQL 2008 R2 2-node cluster with an iSCSI backend and deciding the best solution for transaction logs and backup.
Richard – I can totally identify with that! Clustering scared the hell out of me before I built my first one. It’s gotten SO much easier now with Windows 2008R2 and SQL 2008R2 – I think you’re going to be pleasantly surprised. Pick up Allan Hirt’s book Pro SQL 2008 Clustering:
http://www.amazon.com/Server-Failover-Clustering-Experts-Voice/dp/1430219661/ref=sr_1_1?ie=UTF8&qid=1325598363&sr=8-1
You can get by without doing the book – you can just pretty much wizard your way through the whole thing – but if something goes wrong, you’re going to want the base knowledge that the book provides. Once you get done, you’re going to be shocked at how simple it is, and you’ll get “CLUSTERS4LIFE” tattooed across your chest like Allan did. I hear.
I’m going to be trying to transition from a software engineer role with an interest in SQL Server, to a database engineer / dba role. I’m not sure the company I’m at has the opportunity I’m interested in, so in the past quarter I’ve earned my MCITP – Database developer 2008 and I’m getting ready to sit for the MCTS – SQL Server 2008 Implementation and Maintenance. I am hoping the knowledge I gained reading and studying for these will help, but am still a little nervous about the potential new job/city.
Adam – congrats on deciding to make the move! I think the practice of studying for a cert is a decent way to gain knowledge. The best way is solving problems in real life, but the good news is that even without having the job, you can gain more of that real-life experience by working on forum questions at places like http://StackOverflow.com, http://DBA.StackExchange.com, and http://SQLServerCentral.com.
I’d definitely try to segue into the position at your current employer though – it’s much easier to move into a DBA role at an existing company rather than getting your first DBA job somewhere else.
We have been tasked with taking all of our SQLServer2000 databases and upgrading them to IBM’s UDB by 2012Q4. But… we have to be off of our old servers by 4/1. So, we’re upgrading to 2008R2 as an interim solution. The existing setup includes over 25000 lines of business logic in about 600 Stored Procedures.
Pre-Frustrated – ooo, 25k lines of logic by 4/1, that’s interesting. Bad April Fool’s joke! The good news is that you’ve got an easy test route: capture long traces, and then replay them on a SQL Server 2008 R2 server where you’ve restored your databases. That’ll help you catch problems.
The possibility of having to look for a new job. After 10+ years, I may have outgrown my position. And there is nowhere to go here. Unless I can somehow convince management that tracking down goofy data issues is a waste of my time (yet still needs to be done) and that more serious DBA work is needed, I may find myself on the market.
The scariest thing ahead of me for 2012 has to be the group trainings that I’ll be doing for the development team. It also turns out that this is the thing I’m most looking forward to for 2012. Speaking before a group doesn’t bother me, but putting together a valuable, interesting, and credible presentation is daunting. Not only once, but again and again and again. If it goes well, perhaps you’ll see me presenting to the SQL community someday.
What scares me is that I still don’t know what I want to be when I grow up…or just growing up. I had a almost 10 yr. side track in my IT Career ( I was a mechanic for the USAF).
I’ve been making up for it with education but still feel behind. I’m in an MBA program hoping to do a little catch up, but need to start working on other technical knowledge. Though I find it easier to tackle the technical as I go, since there is amazing communities out there supporting any technical needs that arise and I’m pretty confident in my SQL Server skills, it’s just the person experience I’m behind in. I’m trying to become more involved in these communities and user groups, but it’s tough being an outsider, especially when I’ve got a heavy school load. For the most part the communities are welcoming, but you’ve still got overcome your own nature and get involved.
Hi there,
First off love the work you and the team do Brent, I don’t often subscribe to daily e-mails but this is one I would not miss!
I guess the big piece of work we have to do this year is move our SQL Server 2008 Standard installation from one machine to another. I know, fairly simple when compared with all the things going on with folks here but still a hassle with the potential for so many things to go wrong. There are lots of differnt jobs pointing at the existing server from other systems (even some we don’t know about I’m sure!) that need to be changed, it’s the ones we don’t know about that’s the worry.
Anyhow, Happy New Year to you and yours, I hope it’s filled with fun, family and opportunity!
A couple of ways to make moves like this easier. First is to create a DNS alias for the SQL box, all apps and users connect via that alias. When you move the instance, just update the DNS alias to the new machine’s IP.
Barring that, the second way would be to give the new machine the same name as the original. It’s trivial to change a machine name with SQL 2008.
Deciding to stay in my field (IT – databases) and play catch-up from specializing and working in older versions or start over with something else completely. Playing catch-up kinda feels like starting completely over…
It probably doesn’t help that am highly ADD [ADHD] and I’m finding it harder and harder to stay on task, especially with learning new things. I think a huge part of this decision revolves around the difficulties I have with that and then not feeling like I am awesome at my job.
Has my passion for database waned or am I just frustrated? Putting out fires constantly also doesn’t leave a lot of time for furthering education AND work/life balance.
If you lose your passion this might be because you did nothing the keep it. When I need to learn something new I develop a new project with that technology at its core. I force myself to bring that project to a state where it is production ready. That teaches me a lot and is very satisfying.
For me the “new year task” is reducing backup size. We have a gigantic table that stores every HTTP-request ever made. I intend to partition it by month and move each old month to a new filegroup which I then set to readonly. That will save me from backing it up all the time.
A good idea?
For my the ‘New Year’ task is to learn data analysis. I mean seriously learn it…data mining, semantic analysis of websites, graduate-level statistics, etc. Needless to say, this is fairly daunting, since I’m learning this on my own time.
Getting both my MCITPs will be my task. I may have another task once I start my new job on Monday
I’m scared and excited about building my first DW/BI environment in 2012. Should be fun!
One thing that scares me is that I might have to reboot the server before I have a chance to save all of my open SSMS query windows. Some times I get carried away and have about fifty tabs open at one time, then have to walk away before saving away the contents as notes. Or worse, I do actually step through each tab one by one, saving each one individually to a folder in the file system.
Ignoring the human behavior aspect of this; it would be nice if there was a way to save all SSMS query tabs as individual text files to a single directory, automatically naming each one something unique, with only a few keystrokes.
Any ideas?
Nicholas – the easy way to do this is with the free SSMS Tools Pack, which can even save your executed queries that you don’t save: http://www.ssmstoolspack.com/
Nice – thank you Brent!
WORK: I’m the dba-by-default and we’ll be migrating from 2005 to 2008 sometime in the next 6 weeks or so. The new Win2008 servers aren’t even up yet, so who knows when I’ll actually get to find out if I know what I’m doing!
PERSONAL: I’ll be continuing to try to teach myself enough SQL / SQL2008 to pass the 70-432 exam. Failed in November, 563 score. Difficult to know where to point my focus to “know” enough to pass. Hoping that passing will open a door somewhere to get real experience.
ACTUAL PERSONAL: Had our second child, a baby girl in November 2011. Adding her to our 4 year old son has been fun, rewarding, and challenging!
Thanks Brent & Co. for all your efforts here, best of luck in 2012!
CodeNamePapa – migrating from 2005 to 2008 will be a good experience. That’s pretty much all gain and no pain.
About the personal goal to get certification to get experience – I gotta be honest, the 70-432 cert alone won’t open doors. I’ve blogged about that here: https://www.brentozar.com/archive/2009/04/certifications-are-the-icing-on-the-cake/ Hopefully that post will help emphasize why certs aren’t the best way to get into another company.
I know that certs != experience or a “reason to hire me” – I *am* learning more about relational DBs in general, etc. and building foundational knowledge so that if/when I get another opportunity I’ll be better prepared to learn real-world stuff, not just the basics.
I know I’ll be ok with 2005 to 2008, I’d just like to practice the migration once or thrice!
I feel your fear. I have to migrate from SQL 2000 to SQL 2008, and of course there are several enterprise applications that run on the databases I’m moving. I’ve never done anything this large before, so I’m terrified also. I’ll have to roll out new DSN’s, update over a dozen custom Access databases, refactor god-knows how many linked server queries, and organize training of the users on new versions of several programs. I’m terrified, but I”m trying to plan using Gaant charts before I do anything. OH, and I also can’t have more than a day of downtime, so I’m going to try to setup replication between the old and new databases to minimize the downtime. Its all so much, and I’m almost completely on my own with it.
Marc – wow, that’s a heck of a project! I wouldn’t set up replication to minimize downtime – I’d use log shipping. Log shipping’s easier to manage and doesn’t have so many moving parts. Are you going to try to keep the server name the same?
No, I’m using a different server name because I’m also condensing two SQL server instances into one, so the old names wouldn’t make much sense. I’ll look into log shipping, truth is I’ve never done either before. Also, disk space on these old servers is sparse, so we only have simple logging turned on right now.
After being outsourced at my prior two jobs, I decided to give different aspects of the CS field a try. I was a sysadmin for a year at one company, and am currently working for a systems integrator at my current company.
Both were ‘learning experiences’. What I’ve learned from them is that at the ripe old age of 43, I’m really tired of working for, putting long hours in for, and ESPECIALLY being ON CALL for …someone else.
I currently have one sideline job that will provide at least some income in the coming months, but I will likely need to do contract jobs and sharpen my skills before I can be even a marginally successful consultant.
So, I’m just about ready to jump ship and completely fly on my own…and I’m scared to death, because I know I’m putting it all on the line…but you know, one thing I’ve learned is that job security is an illusion anyway..but it’s a darned addictive one. 🙂
2012 planning sessions! Hahaha
Alan – sounds like your company is what scares me. 😉
You and me both!
What scares me is not my work, but the work of others in the Straights of Homuz. If things go beyond words then fuel will probably double in price after a few weeks and economies and job security will drop in proportion.
Ed – two things I’d keep in mind. First, don’t worry about things you can’t control, and second, when there’s that much money to be made, people will stop at nothing to make sure the money (oil) continues to flow.
Technically, nothing really scares me. With time, anything can be learned.
What really scares me — being outsourced by similarly technically-skilled but very low-cost overseas labor. It’s pretty much already happened to my clients’ DEV shops.
My ability to speak clear, native English is all that stands between me and an 80% reduction in my salary. How long will that last?
Steve – try to apply for a remote telecommuting DBA job and you’ll find just how hesitant companies are to replace their onsite DBA. I shared your concern a few years ago when the offshore craze first hit, but it hasn’t been an issue.
I **am** a remote, telecommuting DBA…who speaks native English!
Steve – ah, in that case, you’re screwed. Most companies won’t hire remote DBAs, but if your company’s already willing to do it, then they’d probably be willing to save money and hire someone further out. You’re wise to be scared on that one.
Well, I do have a few extras that help me out: I live in a small, inexpensive, midwestern city, so my price is lower that what I would command in a bigger city, given my extended skill-set (Oracle, TSQL, SSIS, C#, EMC SAN).
I too am a mostly remote telecommuting DBA – I do spend some time in the office, but less than one week in four. It’s nice to get a contract like that – saves a fortune in travel / accommodation costs.
Turning 50 this year .. that scares the crap out of me 🙂
I don’t consider this “work” as it’d be my choice whether or not to take it on, but for me it’s group speaking.
I enjoy attending the Summit, watching online recorded sessions, and going to my local user’s group meetings. There are a lot of people that are very good at presenting, yourself included Brent 🙂 But I break out in sweat at the thought of me being the one up there.
Last year our user’s group put on a lightning talks night, and I had my 5 minutes of terror. After it was done I had a good time, but I can’t imagine extending that out to an hour or more. I’d be a nervous wreck for weeks.
Come to think of it, I’d rather face a server on fire than be in front of a room full of DBAs!!
Randy – hahaha, I know that feeling. Technology is fairly predictable, but people, that’s another story!
My next project is to migrate our compnmay from 2000/2005 to 2012 64-bit on VMWare with a SAN back-end, should be a fun year this!
I go to running and yoga groups at a lululemon store in Auckland and their moto is “Do one thing a day that scares you”. It’s hard trying to live up to that but this year I do have some goals that don’t really *scare* me but shouldn’t be easy:
*run the Pittsburgh marathon (any takers? May 6th)
*pass 2 more cert tests
*speak at least 1-2 times at a user group (batting .000 on that one now)
*find a topic to blog about that hasn’t been beaten to death and dig deep into it. As an inspiring blogger, I learn tons every week but it’s hard to find new ideas to blog about because I learn so much from other bloggers and just monitoring the #sqlhelp hashtag on twitter.
One thing that will be intersting this year is moving back to the states and seeing how the industry has changed since my time away from the states. Also finding a new position that is open to 2012 should be fun.