Move the SBS 2008 SharePoint Services content database to a Windows 2008 member server By Mariette Knap sbs, sharepoint, migrate In a default SBS 2008 Premium setup you have two servers. One with SBS 2008 and another one with Windows 2008 Server installed. That additional server can be used for several purposes. Pages Move the SBS 2008 SharePoint Services content database to a Windows 2008 member serverInstall SQL Server 2008 on the member serverAdditional configurations tasks on the member serverSharePoint Services content database move process In this article we will install SQL 2008 on the member server and create an instance that will hold the Sharepoint content database. Install SQL Server 2008 on the member server SQL 2008 can be found on the 5th DVD. Start setup from the DVD. Setup will install several updates. Set is working... Setup will install .Net Framework 3.5 SP1 first. Setup is working... Setup has finished installing .Net Framework SP1. A hotfix needs to be installed before SQL 2008 can be installed. Setup is installing the hotfix. The server needs to be restarted. Click Restart Now to reboot your server. Choose to install a new SQL server standalone installation... Set checks if there are problems that might occur during the installation. Click OK to continue. Enter your product key and click Next. Accept the licensing terms and click next. Click Install... Setup is working...be patient! There is one warning about the Windows Firewall. Click on the warning. We will look into this later if we make the final checks. Click OK We are back in the previous Window and click Next. Make sure you check the choices as in the screenshot below. Click Next. We choose to create a new named instance called Sharepoint. Click Next. A summary....click Next I use the System account to start SQL Server Agent and Database Engine. Click Next. You need to add at least one user who should have unrestricted access to the Database engine. Click Next. No problems were found. Click Next. Setup is ready install and presents you a list with features that will be installed. The installation runs... Setup is complete. Click Next. We have successfully installed a new SQL server 2008 instance on our member server. Click Close. Enable remote connections on the SQL Server that you want to connect to from a remote computer. Change the listening port of the named instance. Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser Service. Additional configurations tasks on the member server Before we can use the new SQL Server 2008 instance we need to: Enable remote connections on the SQL Server that you want to connect to from a remote computer. You do this by using the SQL Server Configuration Manager. From the start Menu open SQL Server Configuration Manager. Highlight the Instance you want to use and set TCP/IP to enabled if is not already enabled. Restart the SQL Browser Service. Change the listening port of the named instance. Named instances of the Database Engine and SQL Server Compact 3.5 are configured for dynamic ports. This means they select an available port when the SQL Server service is started. When you are connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall. From within the SQL Server Configuration Manager right click the TCP/IP protocol name and choose Properties. Delete the value from TCP Dynamic ports and add '1433' to TCP Port. Remember that if you already have a Default SQL instance on your server that you cannot use port 1433. Click Apply. Click OK. Restart SQL Server and Server Browser. Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser Service. We can configure the Firewall in 3 different ways Use the GUI from the standard Firewall Use the MMC for the Advanced Firewall Or use the command prompt with 'netsh'. I will show you the way I have done this with 'netsh'. Open a command prompt and make sure you run it as administrator. In the text box below you see that I run 3 times a netsh advfirewall command that adds 3 rules to our firewall. You can copy and paste the commands from this text box and use it on your own server. C:\Windows\system32>netsh advfirewall firewall add rule name = "SQL Server" dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN Ok. C:\Windows\system32>netsh advfirewall firewall add rule name = "SQL Admin" dir = in protocol = tcp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN Ok. C:\Windows\system32>netsh advfirewall firewall add rule name = "SQL Browser" dir = in protocol = udp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN Ok. The result of these commands can be found in the Avancanced Firewall MMC. From the start menu open the Windows Firewall with Advanced Security. Eureka! There are our rules SharePoint Services content database move process Before we continue there are some things you need to know. If the destination SQL Server is not on the Windows SBS 2008 server, you might encounter issues when you use the SharePoint 3.0 Central Administrator to add the new content database. There for we need to use 'stsadm' to move the reference to the database. After you move the content database to SQL Server, you are no longer able to use the Move Windows SharePoint Services Data task from the Windows SBS Console (Server Storage, which is located on the Backup and Server Storage tab). This is because the task can only move data for the Windows internal database. Prepare your internal Web site Make sure you disable the Internal Website so that other users cannot use it during the migration. Open the Windows SBS Console and highlight the Internal Web site and click 'Disable this site'. Start the SharePoint 3.0 Central Administration from administrative tools. In the Central Administration page, click the Application Management tab In the SharePoint Web Application Management section, click Content databases. On the Manage Content Databases page, click the content database that you want to move (ShareWebDB). On the Manage Content Database Settings page, in the Remove Content Database section, select Remove content database, and then click OK. Click OK. There are no content databases listed anymore. Now we have to detach the databases from the Windows internal database. Because I upgraded my SQL client tools on my SBS 2008 to SQL 2008 the screenshots may look a little different to the SQL Server Management Studio Express that are by default installed on a SBS 2008 but the idea is the same. Right click SQL Server Management Studio from the Start menu and choose 'Run as administrator'. If you don't do this you will not be able to login the Windows Internal database. In the Connect to Server dialog box, under Server type, select Database Engine, and then in the Name field, type \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query. Click Databases to expand the list, and then click ShareWebDb. Right-click the database name, click Tasks, and then click Detach. Detach the database by clicking OK Our database has been detached. Copy or move the ShareWebDb.mdf and ShareWebDb_log.ldf files from C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data to a destination folder on the member server. I have copied them to my SQL Sharepoint instance data folder on the member server On the destination server, attach the content database to the SharePoint instance. Click Microsoft SQL Server 2005 or Microsoft SQL Server 2008, right-click SQL Server Management Studio, and then click Run as administrator. Connect to the Sharepoint instance. Click Connect. Right click the node Database and choose Attach. Click Add. Browse to the location where you moved the database to and highlight it. Choose OK. Click OK. For now we ignore the note about the Full text catalog. Yes, there is our database listed. Our last step is to tell Sharepoint that our databases are located on the member server. This is done by stsadm. Open a command prompt and browse to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN. Remember that you need to change -databaseserver [memberserver\sharepoint] to your own specific situation. Note: In many manuals found on the internet including Microsoft Technet they tell you to use -url https://sites.987. That does not work. You must use http://companyweb as show in the box below. C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN>stsadm -o addcontentdb -url http://companyweb -databaseserver memberserver\sharepoint -databasename ShareWebDb Operation completed successfully. We need to enable the companyweb before we can use it. Now that everything is ready browse to http://companyweb to see if it works... Remember your data is now on the member server is because of this it is no longer included in the backup you run on the SBS.