Monthly Archives: October 2009

Something to check for on your Disaster Recovery plan

We just had something happen that caught us by surprise. We were log shipping to a remote warm standby server. It was all running smoothly and we were ready for anything. Then the remote server went down – right down. Looks like we might have to rebuild it. Meanwhile, I’ve got transaction log backups piling up and nowhere to ship them to. Without a standby server that’s on the same backup cycle as the live server we just don’t have a DR plan that applies. Now I have to tell people I can’t do those urgent things I promised to deliver because I have to sort this out. And I thought it was hard enough getting buy-in for setting this up in the first place!

When we drew up our plan we did briefly consider what would happen in this scenario. We were far too casual. We said to ourselves, “Well, we didn’t have this capability at all until now and we’ve never had a disaster so it won’t be such a problem to be without it again while we fix it. After all, you can only plan for so much and after that you just have to wing it.” Wrong answer! The last thing you want to be doing when things are all screwed up is to start improvising. A standby server is just as likely to fail as a live server, maybe even more likely. After all, you keep a keen pro-active eye on your live servers – you are doing that, right? – but it’s hard to justify spending too much time monitoring a standby server. As long as the replication/mirroring/log-shipping works, that’s fine. Wrong! A standby server is just as important and warrants just as much care and feeding.

Don’t let this happen to you. What does your plan say about the standby going down? How does it affect your routine DR jobs etc. How will you get a standby server back again? Do you need to alert the business that you may need some budget? How will you re-synch it with your live server so you can resume normal operations again? Knowing in principle is not enough. Write it down. Test it. Know it will work.

More fun with SQL 2008 and Windows 7

I’ve just got round to trying out Reporting Services on my laptop, having rebuilt it with Windows 7. I’m fairly comfortable with Reporting on XP but I’ve clearly got some reading to do because there was some baffling security weirdness that I never would have fixed without help. Once again, a great product is let down by stuff that you have to be pretty advanced to understand. With the installation problems I had and this one, if I was a newbie I would probably by now have written off SQL Server up as a pile of crap and started looking at a competitor’s product.

This is SQL 2008 Developer installed on Windows 7 Pro on a standalone laptop. I have no domain. I defaulted everything during installation: Report Server is in Native mode and the Reporting Services service is running under the Local System account. When I browsed to http://<machine-name>/Reports it asked me for my login and password. Odd. I thought it would default to Windows authentication and just let me in. Then, although I’m a member of the local Administrators group in Windows, I couldn’t do anything in Report Manager. I could see the Home page but not the links to administer security etc. Only My Subscriptions. So I went into SQL Management Studio and connected to Reporting Services. I could see some stuff but I couldn’t view details of anything. Those options were greyed out.

I read the Books Online stuff about authentication modes etc. but my brain started to hurt. I’m not a web security guru! So I asked the question on StackOverflow (http://stackoverflow.com/questions/1540800/sql-2008-reporting-services-i-have-no-rights) and got the answer. Thanks, Mozy.

To cut a long story short, when you go into IE in Windows 7 UAC ensures that, even though your account is an Administrator, once you log in you are no longer running with those privileges. So, when you get to the Report Manager you are a nobody. So you have to add http://<machine-name&gt; to your trusted sites. Then you have to use Run As Administrator on IE to get into Report Manager and then add your own login as a System Administrator in Site Security. Finally you can actually try the technology out.

I just don’t think I was trying to do anything exotic here, Microsoft. I’m a reasonably accomplished .NET developer, DBA, and Reporting administrator and developer in SQL 2005. And I’ve got completely stuck twice now using two of your flagship new products in their ‘out of the box’ configuration. There may be perfectly sound ‘secure by default’ reasons for these things but please make it easier to discover how to switch the stuff on. With PowerShell you can’t even run your own scripts on your own machine without making some changes but that was quite well publicised and it’s easy to find the instructions. But I haven’t found it like that with SQL Server 2008. Maybe I missed some excellent advice about all this somewhere in Books Online. I don’t think so but, if I did, that’s the point: it was easily missable! Please don’t assume that, just because it’s a server product, it’s going to be set up by geek gods who instinctively understand a wide range of network and security details. I suspect it usually won’t be. And it certainly wasn’t this time!