First Attempt At Changing Domains on a SQL 2005 Server

Well I have finished my first pass at moving one of our three SQL Servers to our new domain. I had to roll it back for the moment, but I learned a great deal in the process. This server runs on Windows 2003 Server 64-bit and SQL 2005 Enterprise Edition 64-bit. This server also runs Reporting Services and Integration Services packages to run scheduled reporting, so there are a number of things to test.

1) I changed the DNS Server IP addresses in the network settings to point at the DNS server which is part of the new Active Directory.

2) I changed the domain from the current domain to the new domain and completed the required reboot.

3) I setup security groups from the new domain on the SQL Server and assigned them the appropriate permissions. By assigning the domain groups as logins, it simplifies adding and removing employee permissions by making it a simple Active Directory group addition or removal for the user account.

4) I could connect to the server from my local PC (which I had already moved to the domain) from Management Studio. That gave me comfort that basic name resolution and integrated security were working properly.

5) From an IE browser, I tried to pull up the reporting services browser. No luck. In researching it, I found that the encryption key was not working after the domain change. I also found that a domain account from the old domain was setup for "Execute as" permissions. I attempted to change this to a new domain account but it failed. Tried restoring the backup of the encryption key, failed. I tried creating a new encryption key, failed.

Basically everything I tried to get Reporting Services up and running under the new domain failed. I was able to join the old domain and reset Reporting Services back to a working state so I can do some more research on the issues I encountered. I am considering moving the server to workgroup security and getting everything working right first, and then joining the new domain. Until I do some more research I am not sure.

Based on month end and some other priorities, I probably will not get another attempt at this until the middle of September. If anyone reading this has some advice or some good articles/blog entries on the subject, I welcome them.

Preparing To Join A Domain and Change Domains

Currently I am managing three production SQL Servers:
SQL 2000 on Windows 2003 with Workgroup security.
SQL 2005 on Windows 2003 with Workgroup security.
SQL 2005 on Windows 2003 with Domain/AD security.

We just spent the weekend moving all the PC's to a brand new Domain controller with a new Active Directory. The servers are next.

I have been surprised at how little is on the web about joining an existing SQL Server installation to a domain, presumably because most people start on one or never move to one. There is a little more on changing domains, but it still seems a little sketchy.

So sometime in the next week, all three servers will be members of the new domain. These servers use IIS, heavy Reporting Services, heavy Integration Services, and extensive cross server connectivity.

Hopefully I will have a good report, but either way I will let you know the good, bad, and ugly.

Books on SQL 2008

From what I have seen there are are two basic types of tech learners: the book learners and the "learn as you go" crowd. I lean toward the learn as you go crowd in that I enjoy jumping in with both feet and playing around with a product, then reading about it later. The playing around gives me more context for the books.

Anyway, I picked up a few books to start reading up on SQL 2008 to augment my playing around. At the office I have Microsoft SQL Server 2008 Management and Administration since initially management and administration will be my focus for work. I have barely started it, but the chapter overviews look promising.

At home I have two more books. I started reading Microsoft SQL Server 2008 Internals because from what I have read online, it is an excellent book for really understanding the guts of how SQL Server works. I'm only in Chapter 1, but my quick overview of the book looks promising.

The third book, which I have not started, is Microsoft SQL Server 2008-Database Development which is preparation for the 70-433 MCTS exam. I have never received a Microsoft certification, but I think it is time that I start down that road. Due to my current title of Database Developer this seemed like a good MCTS to start with. Since this one is the least important for me ramping up the installation, setup, and maintenance of SQL 2008, it will probably be the last book I read.

I'll post my impressions of the books as I finish them. And if you have any good book recommendations, feel free to post them in the comments.

Ready for Windows 7

I know patience is a virtue, but it's a virtue that I have in short supply. After having tested Windows 7 in beta and after installing RC a dozen times for production, I am ready to make the switch to Windows 7.

Everyone has their own reasons for liking Windows 7, but mine are pretty simple.

1) It boots faster than Vista and XP. Not a big deal to everyone but makes a difference to me.
2) From what I can tell, it either manages memory much better or it does a better job of prioritizing tasks. The end result is better performance even with less memory than on Vista.

Combine that with the great ease of networking a Windows 7 PC and the huge increase in security over XP along with more stability than Vista, and we have a winner.

I just wish it had not taken so long for Microsoft to churn out another OS as good as the beloved XP. Fortunately the new Toshiba laptop I just picked up at Best Buy (and am typing on at this moment) has a free upgrade to Windows 7. The wait is almost over.

Business Objects report scheduling

Part of our enterprise includes a Business Objects data warehouse and a host of Business Objects reports. I have mostly dealt with SQL Reporting Services or MS Access reports so I was excited recently to be asked to reschedule all the Business Objects reports.

Here are a few things I found noteworthy.

In addition to normal interval based report scheduling (i.e. daily, weekly, monthly, etc.) you can schedule based on a custom calendar. You create a named calendar and actually select the specific days that you want to schedule. It allows a high level of flexibility if your requirements are irregular.

The output options are pretty standard, PDF, Excel, and a web format, but you can easily use variable placeholders in your naming to customize the file name, email subject/message, etc. which simplifies and standardizes your file naming and/or emails.

One thing which is a problem with the scheduling, as with most out of the box solutions, is that you can not easily re-run a whole schedule day of reports. You must run the reports individually if there are problems. This is also true in SSRS reports which is why I wrote a database and SSIS packages to automate daily processing of scheduled reports. Maybe I'll blog about that another time, but it would take many posts to cover.

Overall, I am impressed with the Business Objects report scheduling ease of use and flexibility. Hopefully I'll get a shot at some report revision soon and I'll let you know how that compares to SSRS report design if I do.

www.jhughthomas.com,
www.facebook.com/jhughthomas

Fixing a Reporting Services Report that belongs to someone else

It is a common occurance in a shop with multiple report developers and without version control. Someone goes on vacation, you get a call that the report is giving an error, and then what? You don't have the local Visual Studio project or rdl file to edit. No Source Safe to pull the file from. What do you do?

Login to your reporting services host at http:\\[hostname]\Reports to get a list of folders and reports. Navigate the directory structure to find the offending report. Once you find it, you need to copy it to the local PC.

If the "Show Details" button appears on the right hand side of the sub-toolbar, click it. When it reads "Hide Details" you are ready to move on. Now click on the icon in the "Edit" column (just after the check-box). Now under the "Report Definition" section you will see an "Edit" link. When you click the "Edit" link, you will get a save dialog box and can save the report rdl file to your PC. My recomendation is to save it first to your desktop.

Then open SQL Server Business Intelligence Studio (i.e. Visual Studio) and either open a project that you use for researching that person's reports or start a new project to use for researching other people's reports. In the Solution Explorer, right-click on the Reports folder and select Add-->Existing Item...

You navigate to your Desktop (or wherever you saved the rdl) and select the rdl you just downloaded. Now it is a part of your project and you can look at data sources, evaluate issues, and exercise your genius to solve the problem.

And if the solution does not lie in the data sources, you can always setup the Target report folder, server, etc. to post changes to the actual rdl file. If you do this, I recommend holding onto that copy of the rdl that is still sitting on your desktop because it makes for a good backup in case you make things worse and need to back track.