Microsoft has always been pretty good at one thing, ease on install.
One of the things I always says is the greatest thing about SQL Server is any idiot can install it. The other thing I also say is the worst thing about SQL Server is any idiot can install it.
This has carried over to the other fine products that ship with SQL Server. The one thing I’ve had pretty good luck using as a novice since it was introduced is SQL Server Reporting Services. As someone who hasn’t had the best experience over the years with crystal reports SSRS was a breath of fresh air.
One of the things I’ve never had to do though is move SSRS to a new server. To start with I did what I always do; I went to MSDN and read what I could find on moving or migrating to a new server. I was feeling pretty confident that I could pull this off.
I had backed up my databases and my key so I could import it on reinstall.
My first mistake was hubris that I could do what I thought was correct and second guess the documentation.
I installed SQL Server plus all the other services, including SSRS just like I was doing a clean install. The server didn’t start out with the same name and that was part of the problem. I copied all the files I needed from the old server and shut it down. Next we renamed the server and I renamed the SQL Server instance to its new name. Once that was done I restored the old SSRS databases and the encryption key. I fired up SSRS when to look at a report and got an odd error.
This version doesn’t support multiple instances.
My second mistake was assuming the documentation on MSDN was complete. After staring over and following the instructions there I ended up variations on the previous error, or the reports wouldn’t generate period.
What should have been a two hour migration was stretching into two days. I had to pull the trigger and bring the old hardware back on line.
Having a fall back plan saved us from having any impact on the business but it did set me back a week until the next available maintenance window was available.
I now had a whole week to research the issue and make sure I had a successful migration, so I got cracking.
I went to my buddy Mr. Google and asked him where the hell I messed up and if anyone else had come up against this. As I thought I wasn’t the first, but there was a twist it wasn’t just one thing it was a few things that I was bumping up against. Luckily between MSDN and some of the fine folks on the SQLServerCentral forums I was able to piece together a successful migration strategy.
The list of obstacles I had to overcome.
This was a named instance and the $ played havoc with the Reporting Services Configuration Manager. This was a migration of existing data plus the security key to the server. The server name was also being changed after install to reflect the old server name. The instructions from MSDN tell you not to choose SSRS to be configured during the install with the default values. This causes a key part not to be configured, IIS. This is a problem because without IIS configured you can’t view the reports! This by its self isn’t an issue if you are working with a default instance of SQL Server. But, as I indicated, I’m dealing with a named instance. After you have installed SQL Server and SSRS you are suppose to use the Reporting Services Configuration Manager to setup IIS. But, since the named instance has the $ in it, the Reporting Services Configuration Manager kicks back the virtual name with:
ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified is not valid. Make sure the specified name is not too long and doesn't contain illegal characters. (example: ;) at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)
Very nice, if you let the installer configure the site at install time it gladly puts the $ in the name for you like http://<servername>/ReportServer$<instancename>.
I had puzzled out the IIS part and gleaned the rest from Tim Benninghoff and his post on SSC. Also, the bits about restoring the encryption key came from MSDN, none of the other articles talk about a named instance at all and assume it is a default instance you are moving.
So, I followed these steps to restore the IIS folder structure and move my instance of SSRS to the new server. Remember, please validate any changes you make to your server, and always your success isn’t guaranteed just because you followed these instructions.
On the original source server:
On your new destination server:
--create RSExecRole role in master and assign permissions USE [master] GO CREATE ROLE [RSExecRole] AUTHORIZATION [dbo] GO GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole GO GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole GO GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole GO --create RSExecRole role in msdb and assign permissions USE [msdb] GO CREATE ROLE [RSExecRole] AUTHORIZATION [dbo] GO -- Permissions for SQL Agent SP's GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole GO GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole GO GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole GO GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole GO GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole GO GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole GO GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole GO GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole GO GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole GO GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole GO GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole GO GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole GO -- SQL Server 2005 requires that the user is in the SQLAgentOperatorRole IF EXISTS (SELECT * FROM sysusers WHERE issqlrole = 1 AND name = N'SQLAgentOperatorRole') BEGIN EXEC msdb.dbo.sp_addrolemember N'SQLAgentOperatorRole', N'RSExecRole' END GO
Once all that is done, if you had an instance already configured you may need to remove the virtual folders from the default website.
I also restarted everything again and tested it all out.
Good luck and I hope you find this useful; trying to glean all of this from different resources and separate what was really going on by the cryptic messages SSRS pumps out can be difficult in the best of times!
Having been married to Microsoft for most of my professional career doesn’t mean I drink the Kool-Aid.
I have had the distinct privilege to grow up in interesting times. I loved DOS. As a BBS operator DOS was the de facto OS for most BBSes that ran on x86 hardware. Combined with QEMM/DESQview I was a multitasking fool, running many nodes on a single 386 and a ton of ram, 8 Megabytes to be exact.
Other OSes came and I tried them as well Even running OS/2 for a while. It was DOS compatibility and multi-instance that I was after, though you could run Windows 3.x apps in it, why bother.
I just didn’t see where Windows was anything near as powerful as my good old DOS prompt. I had used GUI’s before and knew that some day it would be the way things went. To put it bluntly though, I hated Windows. I my eyes at the time, it did nothing well. It made my sleek powerful machine run like a pig. It required me to learn how to do things the Windows way, which slowed me down. I even went so far as to actively refuse to own or install a mouse I so loathed Windows.
In many aspects my opinion hasn’t changed much. To be honest I blame the “over promise, under deliver” method of development that Microsoft seems to employ with Windows OS development.
Windows 3.11 for Workgroups was modestly noteworthy in my view because it help bring the internet into homes for the first time. I also knew the internet was awesome and powerful but I didn’t grasp the whole World Wide Web thing immediately ether. Not being a graphical guy I didn’t see what it bought me over any other tools that ran on the internet, until I really saw it running on Windows.
It still wasn’t enough to completely win me over. I was already working with GNU/Linux going back to DOS/Windows mostly to play games or develop on as a platform since that paid the bills. I had been using NT for quite a while as a systems/network/database administrator but still ran Linux at home and for other projects when I could. That changed a bit with the release of Windows 2000. To me it was Windows all grown up. 32 bits, nice GUI, fairly stable it had a lot going for it. Plus, Windows Me was such a miserable experience it was an easy choice to go with 2000 Workstation and pretend that particular nastiness just didn’t exist. Though it wasn’t until XP hit that I switched full time for my day to day desktop to Windows, from dual booting just XP all the way.
That lasted for quite a while…. Until Vista. Vista didn’t have a marketing problem, it didn’t suffer from bad press. It was just fundamentally broken. I don’t care how many Mojave commercials you run, live with it for a while and you will be just as unhappy with it if it was named Santa Clause. I do thank MS for releasing Vista though, it turned me back on to Linux and specifically Ubuntu which I have been using now for the last couple of years. If anyone can close on Windows I think Ubuntu and Mark Shuttleworth have the best chance, unless Apple looses its mind and releases OSX for white boxes.
In the interest of full disclosure, I will always give Windows its due when it comes to ease of configurations and common usage. If it wasn’t for Windows my mom would still be putting stamps on her mail to me.
With that little history lesson, and my obvious bias against Windows I still always try the latest and greatest for MS. It is in my best interest to do so. I don’t want to ever be too far behind the curve, or have a lack of something to complain about.
So, with all my gripes and soap-boxing, here I sit typing away on a x86 machine with Windows 7 loaded on it, and I’m happy with it. So happy I’m not dual booting into anything at the moment and my laptop has it loaded as well.
Why, you may ask, am I back on the bandwagon? Here is the short list.
Performance.
That’s right, as bad as Vista was, 7 doesn’t show any signs of the past sins. My first big ugh moment with Vista was trying to copy files on the network. It just wouldn’t start, or if it did it took forever to finish. I know it was addressed in a patch and later by SP1 but it was a band-aid on a sucking chest wound. Rarely would I come close to gigabit speeds even though I’m on a managed switch and both ends can easily handle the load. XP came much closer, and if I wasn’t using Samba, Ubuntu just flew over the wire. Windows 7 brought that back in line. When I got near wire speed on my first test run I just assumed it was wrong. I still doesn’t handle lots of small files as well as my Ubuntu setup but its not enough to quibble about.
Visuals.
Vista had them but at the cost of making your state of the art machine run like last years eMachine you bought for your mom for 300 bucks. On the other hand Ubuntu with Compbiz was just stunning and ran on my older Pentium M laptop with a radeon x200 mobile GPU in it. Again, 7 addresses this it keeps the visuals from Vista and improves on them, I got to say the rotating wallpapers is my current favorite feature at the moment. It is still a generation behind Compbiz as far as raw visual stunning effects. I’ll never forget when a friend of mine was going on about Aero glass and transparencies in Vista all I did was break out my laptop and tab through the running apps. Once he picked his jaw up he asked how I had gotten Vista to do that….. After he got over the second shock, that it was Linux, I had him trying Ubuntu for himself. I’ve also attempted to use Stardock to get as close to the same effects on windows and just had to give up. There was enough annoying crashes and blips to make it not worth my time.
Organization.
I wasn’t sure I was going to like the new fat bar but it has quickly grown on me. I hate having a million icons on my desktop but I want things to be accessible that I use day in and day out. With Windows 7 replacing the quick launch with the ability to pin an application to the bottom bar, or in the start menu, you get the best of both worlds you task bar shows you what is running it also acts as your quick launch and it is remarkably uncluttered. I am also a fan of the mouse over preview that shows you how many things you have open per group and what is in them i.e. having multiple browsers open or multiple management studio sessions. With the quick preview I can just peek and pick the one I need to work with now without having to alt-tab through everything. The focus and fade effect you get when you mouse over then up onto the preview showing you only that window on the screen is also a nice touch. I use to always use the minimize all windows using the shortcut on the quick launch bar, then alt-tab through the list of running programs to find the one I was after it sucked but it was fast enough.
Compatibility.
Out of the box I had very few driver issues with 7. It even installed without my help on my Nvidia raid array. There are a couple of drivers missing for my laptop but no real show stoppers. Since Vista took the brunt of that attack I’ll chalk it up as a win in that column for Vista.
Security.
Don’t laugh I mean it. 7, even as a beta and now RC has a better, more polished security model. Not the open range XP was and not the heavy handed style of Vista. Just to make other Ubuntu/Linux junkies upset I don’t think it is any more disrupted as having to execute under sudo to install components or do administrative actions. I do wish there was a bigger push to move stuff out of the kernel space and into user land for security and stability but I think time will fix these issues as well.
Stability.
I still hear you snickering from the above topic but I must push on. Other than the 1.5 BILLION reboots to install software or update drivers I haven’t had any real issues with crashing. The compatibly run as model actually worked for me on a couple of apps that didn’t play well under 7, but did just fine on Vista. Also, the fact you can install an application in this mode made life easier to the legacy stuff I have to have. Another thing that will make the OSX guys upset is I haven’t rebooted my laptop since the install was completed. Hibernate actually works and that is the mode I leave it in. On my new laptop with 4GB of ram and a decent SSD drive it comes back from hibernate in a flash(no pun intended, oh hell who am I kidding of course it was intended). I was pretty much guaranteed that If I put Vista into hibernate it was about a 1 in 3 chance that I’d have to ditch the saved image and reboot clean.
This all adds up to a better user experience and enhanced productivity without a steep learning curve. I don’t feel like this was rushed out the door and then crammed down our collective throats as the pentacle of operating systems.
If you haven’t tried it, do so. I think you will be pleasantly surprised.
I read, a lot. I’ve been a prolific reader all my adult life.
I use to split my reading between tech books and my regular relaxing reading but since I got into audio books several years ago I just pretty much read tech books now. Some times I’ll listen to a book and read a manual at the same time breaking from one or the other if I need to really focus on a particular passage.
This allows me to really chew through a large amount of text in a pretty short amount of time.
I also have a method of digesting the information as well.
When I read a large technical volume I usually do it in three passes.
First pass is a scan of the entire book marking things of immediate need or interest for detail re-read later.
Second pass is the detail look at my notes from the first pass.
Finally, I re-read the whole thing and take notes on stuff I may have skipped the first time through because I “thought” I knew about that already, or didn’t apply to my core skill sets.
I recently posted a short list of authors I always read and now I’m going to follow that up with specific texts with some notes.
Eventually, I’ll expand some of these into single book reviews, ones I’ve come back to over and over or that I found extremely interesting.
Kalen took over Inside SQL Server full time for the 2000 release and hasn’t looked back. Inside SQL Server 2000 was a must have on any book shelf. 2005 saw a shift by breaking the book into several volumes and inviting the industries best to write about the fields they were established experts in.
This time around, that theme has been carried forward and several noted experts lend a hand in this volume. SQL Server as a product is impossible for a single person to be an expert in from end to end. Even the core engine is beginning to grow to a point that just being an expert in a single aspect may be enough to establish a career.
With the inclusion of Paul Randal, Kimberly Tripp, Conor Cunningham and Adam Machanic this book is an exceptional read. The access to other experts and team members on the SQL Server team at Microsoft lend this book a level of authority that any other book like it in this space just simply cannot match.
Again, Kalen gives us a solid walk through the core engine, what has changed and how it works. I was personally happy to see Conor do his chapter over the optimizer.
Itzik is a giant in the query world. He has a grasp of the T-SQL language that few can match let alone surpass. Time and again I reach for his work to solve a problem or just to learn something new.
With this volume he has added some other note worthy people have joined in. Lubor I have known for quite a while. He is a staple figure at Microsoft and is also a big thinker.
Steve Kass long time SQL Server MVP and scary smart fellow himself adds his wealth of knowledge and his ability to communicate to this book.
I have a feeling this will become a “must have” and a classic in it’s own right.
Another Itzik book teaches you the foundations of T-SQL including previous versions of SQL Server and raps that up with the new stuff in 2008.
Never think you are too smart to read a foundation book no matter what point you are in your career. This is how you can catch new ideas, theories and techniques that “what’s new in…” books can miss.
Plus Itzik’s style and clarity may actually re-learn something you thought you already understood.
I’ve known Louis for a number of years and have read every one that I know of.
He has a down to earth conversational style that is easy to read. Also, having Kevin Kline work on this book is just icing on the cake. I have been a fan of Kevin’s work since the 7.0 days.
This book is good for someone who has been doing some design and development work and really wants to start digging in deeper. He makes no assumptions about what you do or don’t know he starts at an introductory level and walks you through what it takes to do design work and turn that into a usable physical model.
I will say he has progressed to cover more material in every new volume and this latest outing is no exception. Make no bones about it Louis works for a living. There are a lot of text that will make you feel stupid or completely inadequate to do your job but Louis makes some of the more complex problems easy to digest by putting them in real world examples.
I’ve got a lot more books in the pipeline and will be talking about them soon.
I will also be covering 2005 and even 2000 in the coming posts. I know there are some folks still on 2000 and even 7.0 but I don’t have any books save one that old on my shelf at this point. Since I do more than just SQL Server or database work in general I’ll be covering those volumes as well and why I like them. Lastly but not least I will also cover books I’ve read and didn’t find that helped me move my knowledge forward, not that these books were bad, they just didn’t fit me, or there was another author who covered the material that made it easier for me to understand.
We got something good in the mail last week!
Some quick observations:
The build quality is outstanding. Nothing cheap at all about this card. The engineering that has gone into this shows in every way.
It is made up of modules that are screwed down, I can see where they really thought this through so each rev of the card doesn’t require all new PCB’s to be manufactured.
It does require an external source of power via 4 pin Molex or SATA power connector period. Make sure your server has one available, even though these are sold by HP not all HP servers have the required connectors.
PCIe expander bays are few and far between. The issue is most of these are used to expand desktops, laptops or used in non critical applications mostly AV or render farms.
http://www.magma.com/products/pciexpress/expressbox4-1u/index.html
This is a nice chassis but they are currently being retooled and won’t be available for a month or so. It is the only 1U and it has redundant power.
It exposes two drives to the OS per card. We will initially configure them two per machine in a RAID 10 array for redundancy.
More to come!
Wes
