Using your SQL in SBS R2 Premium as a back-end for WSS 3.0

I have been getting this question a lot recently, so I decided I should probably blog it.

When you install Windows SharePoint Services 3.0 using the published document from Microsoft, Windows SharePoint Services is set up as a stand-alone server.  This configuration results in Microsoft SQL Server Embedded Edition (SSEE) being installed as the SharePoint v3 data source.

For SBS Premium customers who want to use their version of SQL Server as the data store for SharePoint v3, you need to deviate from Microsoft’s published installation document.  Specifically, on page 7 under step 4b select “Front-End Server” instead of “Stand-Alone Server”  This will result in the setup process not installing SSEE on the server. 

When you run the SharePoint Products and Technologies Configuration Wizard after the install finishes, you will be given the option of either joining an existing SharePoint farm, or creating a new farm.  Select to Create a new SharePoint Farm, and you will be able to specify the SQL server instance you want to use as your data store.  The wizard will then complete the SharePoint configuration and you will then be running SharePoint 3.0 with a full SQL data store instead of SSEE.

SharePoint v3, Updates and 404 Errors

I think I figured out how I blew up my SharePoint on my test box a couple weeks ago.  I mentioned in my previous post that I blew up SharePoint while testing an upgrade scenario.  Of course, being a typical tech working on a test box, I was guilty of changing too many things at once – including changing the SharePoint config and applying patches.  I know, I know – whip me, beat me, and send me to my room . . .

I’m doing a little server consolidation here getting ready to move our servers in to a data center.  So I did a clean install of Windows Server 2003 R2 on what is destined to be my new web server.  I proceeded to get SharePoint, SQL, and all of my other stuff installed, then updated the box via Microsoft Update.  About 50 updates (including Win2k3 SP2 – yes, I am a glutton for punishment) and a few reboots later, the server was fully patched and humming right along.  I opened SharePoint 3.0 Central Administration and after a few seconds was presented with a nice 404 Page Not Found error.

WTF?

I verified my services were running, then went to check out the Event Viewer.  In the Application Event Logs, I saw a slew of SharePoint errors with Event ID 5586 with details about being unable to connect to the database.  Looking up this error on EventID.net, there wasn’t much there – and I wasn’t particularly keen on running psconfig -cmd upgrade -force and then uninstalling / reinstalling SharePoint.

Googling on “SharePoint event id 5586″ wasn’t very helpful either.  I encountered a number of threads that all referenced the hotfix from KB 932091 not installing correctly, and recommending extracting the update and manually running it again.  I had tried this a couple weeks ago on my test box to no avail, and found several others in the threads that this didn’t work for.  Just to be safe, I tried it again on this box, but it still didn’t work.

One difference with this server is that I am actually running full SQL 2005 (instead of SSEE on the test box), so I could access my database via the SQL Management Studio.  When I opened the Management Studio and attempted to connect to my database instance, after a long pause the Management Studio threw an error that it wasn’t able to connect to the database instance – I discovered I received the error whether I was trying to connect via Windows or SQL Authentication, which was an interesting tidbit of information I hadn’t found when troubleshooting the issue on the test box with SSEE.

On a whim, I decided to check the SQL Client Configuration – since I remembered seeing something similar in the past with Microsoft Retail Management System when it couldn’t connect to the database.  I went to Start | Run and   cliconfg  

This is what your cliconfg should show:

 

This is what cliconfg showed on this server:

 

I enabled TCP/IP and Named Pipes, then selected “Enable shared memory protocol.”  After clicking OK and rebooting – Voila!  SharePoint is back in business and SQL Management Studio can connect to my database instance via both Windows and SQL Authentication.

I’m not sure which update broke this, but considering in the course of 3 reboots I MU’d approximately 50 updates (including SQL2k5 SP1, 932091, and Win2k3 SP2) – it’s hard to know exactly what disabled my SQL client protocols.  But it was an easy fix, and it’s working now – so I’m happy  smile_regular

Moving a WSS 3.0 site to a new farm

If you haven’t heard already, I am going to be at Jeff Middleton’s SMB Disaster Recovery Conference in New Orleans at the end of this month, and at some point during the weekend I will be discussing Windows SharePoint Services in the context of Disaster Recovery – not only how to recover from a SharePoint disaster, but how SharePoint can be a valuable technology in the face of a true catastrophic disaster.

Well, it just so happens that I have had my own little SharePoint disaster on my hands.  A little over a week a go, I was testing a SharePoint upgrade scenario on my web server and proceded to blow up WSS 3.0 on that box.  The good news was that I had a disaster to fine-tune my recovery skills.  The bad news?  Being a bit lazy I actually had a production SharePoint site on that server (oops smile_embaressed  )   Luckily, the production site was non-critical (just the site for my family – and while they’re used to me blowing things up, I really didn’t want to have to mess with recreating that site and it’s 20GB of data).  And I’ll also admit that I was slightly behind on my SharePoint-based backups for that site as well – far enough behind that I really didn’t want to have to go that far back if I didn’t have to.

I’ll admit that I’m not making much progress with repairing the WSS installation on the web server – I’ve tried just about everything, and it looks like I’m going to break down and call PSS to get it working again, assuming I don’t just re-install.  Naturally, the only thing holding me back was this one site that I really wanted to salvage.  Well I’m happy to say that after some trial & error (and the help of Google), I have the site up and running on a separate SharePoint farm – and it was actually quite painless.

First – for the sake of clarity, I’m going to talk about this process in terms of moving a site to a different SharePoint farm, for the simple fact that we can have multiple front-end servers running SharePoint in a single farm, and moving a site to different servers in a single farm is a separate process.  Most SBSers are going to be running a single server farm, and as such will often talk about moving a site between servers, when they’re really talking about moving between farms. 

So to accomplish this task, here’s what I did:

1)  On the database server for the current farm, I performed an offline backup of the content database for the site I wanted to move.  (Easiest way to accomplish this is to stop your SharePoint database service and copy your .mdf & .ldf files).

2)  I restored the .mdf & .ldf files for the content database to the SQL data directory on the database server for the new farm.

3)  I attached the restored database to the SQL instance for the new farm.

4)  On the new farm, I opened SharePoint Central Administration.

5)  On the Application Management tab, I created a new SharePoint Application.  In doing so, I created a new web site that used the same port and host header the site on the original farm used.

6)  When the application creation process completed, I did not run the Site Collection Creation Wizard.

7)  Within SharePoint Central Administration Application Management tab, I clicked on Content Databases.

8)  I verified the Web Application, then clicked on the content database (which should be an empty content database created during the Web Application creation process)

9)  On the Content Database Settings page, I changed the database status from Ready to Offline, clicked to select the option to Remove Content Database and clicked OK.

10) Back on the Manage Content Databases page, I verified the web application then clicked Add a content database.

11) On the Add Content Database page, I entered the name of my SQL server where I attached my restored database, as well as the database name.  I verified Windows Authentication was selected, selected the appropriate Search Server, and clicked OK.

12)  Once that process finished, I was returned to the Manage Content Databases tab and could see that the restored content database was now associated with the web application I had created minutes earlier.

13)  I edited my local hosts file so the URL of my site would resolve to the local server

14)  I opened a command prompt and ran an iisreset, then ran an  ipconfig /flushdns, then pinged my site URL to verify it resolved to the local server.

15)  I opened Internet Explorer and browsed to my site – and voila! there it was smile_regular

So in the big picture, what is the big deal here?  The significance is that with WSS 3.0, you can actually restore one or more SharePoint web applications to a new farm with nothing besides a file-level backup of your content database.  And if you ever tried accomplishing the same feat with WSS 2.0, you can appreciate just how significant this functionality is.  Don’t get me wrong – this isn’t going to be the preferred method (I still recommend you take a look at native backup/restore functionality either from within SharePoint Central Administration or via the command line using stsadm.