Migrate Team Foundation Server’s SQL Server to another SQL Server (same or new version)

Yesterday we migrated SharePoint’s SQL Server to a new instance today we will be migrating Team Foundation Server (TFS) SQL database to new SQL instance.
In this example we are migrating it to a new version of SQL Server which is from SQL 2008 R2 to SQL 2012, this will also work on the same SQL version.

What we had done here is a very simple step by step instructions and this is what I’ve done with our live environment. I only migrated the Project Collection and Configuration Database so if you have the same scenario you can follow this with care of course. So let’s start.

Moving TFS Project Collections

1. Detach Project Collection

Go to Team Foundation Server Administration Console -> Application Tier -> Team Project Collections then choose the collection you want to migrate. Once chosen choose detach then click next on each following step to confirm.

Once finished you will see a Window similar to this, click Complete then close.

2. Detach you TFS_Configuration Database in SQL Server

3. Copy the Database Related Files

Copy all database related files (.mdf, .ldf) to the New SQL Server

4. Attach the Database in the new SQL Server

5. Attach Project Collection

Go to Team Foundation Server Administration Console -> Application Tier -> Team Project Collections then choose Attach Collection.

Once finished you will see a Window similar to this, click Complete then close.

At this stage try doing a check in and check out to see if everthing worked, compare file history as well to double check

Moving TFS Configuration Database

1. Stop IIS on TFS Server

Using the Admin context execute “iisreset /stop” at the command prompt.

If you can’t stop IIS there is another alternative. Just stop the TFS related sites and application pool in the Server

Stop TFS Website on Server

Stop Application Pools, those two highlighted in the screenshot below

2. Stop TFS Job Agent service

You can stop this via services.msc or execute “net stop TfsJobAgent” at the command prompt.

3. Detach the Tfs_Configuration database

Do it similarly with the step 2 on migrating the project collection

4. Attach the Tfs_Configuration to the new SQL Server instance

5. Prepare new SQL for TFS instance

Using Admin context open again Command Prompt if you had closed it then navigate to “C:\Program Files\Microsoft Team Foundation Server 2010\Tools”
Execute “TfsConfig prepSql /sqlInstance:{your_new_server_name_here}”

6. Register new SQL for TFS instance

In the same command prompt instance Execute “TfsConfig registerDb /sqlInstance:{your_new_server_name_here} /databaseName:Tfs_Configuration”

7. Reapply Service Account

Go to Team Foundation Server Administration Console -> Application Tier then click on Reapply Account.
Select the account you use then click OK

 

If successful you will see something like this

8. Start the TFS Job Agent service

Start the TFS Job Agent service via services.msc or execute “net start TfsJobAgent” at the command prompt.

9. Start IIS on TFS Server

Using the Admin context execute “iisreset /start” at the command prompt.
If earlier you just stopped the TFS related sites and application pool just run it all back again.

You have now successfully migrated your database.

Migrate SharePoint’s SQL Server to another SQL Server (same or new version)

This is a step by step guide on how you migrate your SharePoint’s (I used 2010 version in this instance) SQL Server to an new SQL Server host.  In this exercise I tried migrating it in a new version of SQL server which is 2012 from 2008 R2, also had tried it in the same version.  There are only 9 steps to perform this task but it takes into consideration you already have a new SQL Server provisioned.

Lets start

1. Stop all SharePoint and IIS Related Services

• SharePoint 2010 Administration
• SharePoint 2010 Timer
• SharePoint 2010 Tracing
• SharePoint 2010 User Code Host
• SharePoint 2010 VSS Writer
• SharePoint Foundation Search V4
• World Wide Web Publishing Service
• SharePoint Server Search 14
• Web Analytics Data Processing Service
• Web Analytics Web Service

2. Stop IIS
At the command prompt under admin context, type iisreset /stop

3. Detach all related SQL Server databases

• Configuration database
• Central Administration content database
• Content databases
• Service application databases

i.e.
Bdc_Service_DB_xxxxxxxxxxxxxx
SharePoint_AdminContent_xxxxxxxxxxxxx
SharePoint_Config
WSS_Content
WSS_Logging
WSS_Search_xxxxxxxxxxxxxx

4. Move all database files (.mdf, .ldf, and .ndf) to the new server.

Place it in the data folder of the SQL Server instance of the new host.

5. Set up same user permissions on the new SQL server

best way to do this is when you use AD accounts is by scripting the users on the old server and running that script on the new server. If you are not using AD accounts then you will have issues with passwords so best way to do that is to follow this -> http://support.microsoft.com/kb/918992

6. Attach your databases to the new SQL Server

7. Verify what ports your New SQL is using

If it’s using a dynamic port change it to static. To do this go to SQL Server Configuration Manager and into the SQL Server Network Configuration Section choose the instance you will be using. Go to the TCP/IP section and if TCP Dynamic Ports are set to 0 (zero) then remove that and assign a static port into the TCP Port Section on all IP’s. For this sample we will be using 14330 <- notice I just added 0 at the end from the default port. Note that as we will need that on the next section also if you changed the port number from dynamic to static you will need to restart your SQL Server before it can take effect.

8. Go to your SharePoint server and create your SQL Server Alias

If SQL Configuration Manager is installed just head over to the SQL Native Client 10.0 configuration then into Aliases. Create a new alias that points to your new SQL Server by right clicking on the Alias. You need to do this on both 32 and 64 bit instances.

• On the Alias name put your old SQL Server IP or Name
• On the Port the port you indicated on step 7, in this case 14330
• Protocol should be TCP/IP
• And on Server the new SQL Server IP or Name together with its instance name if there is any. In our case it’s SQL2012

If you don’t have SQL Configuration Manager in your SharePoint Server then run the CLICONFIG again on both 32 and 64 bit instances. For 32 bit go to run command and execute this “%SystemRoot%/SysWow64/CliConfg.exe” and for 64 just this “CLICONFG” all without quotes of course. Add the same info as above.

9. Start all your SharePoint services, now you have successfully migrated your database.  

• SharePoint 2010 Administration
• SharePoint 2010 Timer
• SharePoint 2010 Tracing
• SharePoint 2010 User Code Host
• SharePoint 2010 VSS Writer
• SharePoint Foundation Search V4
• World Wide Web Publishing Service
• SharePoint Server Search 14
• Web Analytics Data Processing Service
• Web Analytics Web Service

At the command prompt under admin context, type iisreset /start.

Upgrading S#arp to MVC4

First of all I can assure you that S#arp will work on MVC4 and I am using Sharp Architecture 2.0.4.  We just recently migrated and tested 2 large S#arp projects and it is fully working and is running in production.

Now lets go to the details, I guess you came across tis article either because you want to use MVC4 with your S#arp project or you got this “Entry point was not found” error because you recently installed MVC4 in your machine

If its the second reason yes, it is MVC4 doing it and not S#arp your project just auto referenced the latest MVC version.  Now to fix that issue is that you need to upgrade your S#arp project to use MVC4 this can also be used in any .Net web projects that is using MVC3 so this guide is not specific to S#arp projects.  Lets start!

1. Go to all of your web projects in this case if its S#arp its in the presentation layer.  You need to replace all instances of

System.Web.Mvc, Version="3.0.0.0"
System.Web.WebPages="", Version="1.0.0.0"
System.Web.Helpers="", Version="1.0.0.0"
System.Web.WebPages.Razor="", Version="1.0.0.0"

to

System.Web.Mvc="", Version="4.0.0.0"
System.Web.WebPages="", Version="2.0.0.0"
System.Web.Helpers="", Version="2.0.0.0"
System.Web.WebPages.Razor="", Version="2.0.0.0"

You can see this in 3 different locations

First is in the configSections

which means your old web.config lines which looks like this

will become like this

Next is in system.web.webPages.razor section under host and in system.web under assemblies

which means your old web.config lines which looks like this

will become like this

Finally check also under the views folder, there is one web.config file in there.

Old one will be like this

change it so it looks like this

In the root Web.config file update or add the following is not yet there

<appSettings>
  <add key="webpages:Version" value="2.0.0.0" />
  <add key="PreserveLoginUrl" value="true" />
</appSettings>

3. In Solution Explorer, right-click the project name (if using s#arp its the {ProjectName}.Web.Mvc) and then select Unload Project this will unload you project and you can edit what’s in the code behind.  Once unloaded right-click the name again and select Edit {ProjectName}.csproj.

Find the ProjectTypeGuids element and replace {E53F8FEA-EAE0-44A6-8774-FFD645390401} with {E3E379DF-F4C6-4180-9B81-6769533ABE47}.

so from this

will become like this

Save and close file you just edited, right-click the project, and then select Reload Project.  This will load the project with the new settings.

4. If the project references any third-party libraries that are compiled using older versions of MVC (definitely S#arp is) then you have to add the following
three bindingRedirect elements under the configuration section in your web.config

here is what you need to add:

<dependentAssembly>
  <assemblyIdentity name="System.Web.Helpers"
        publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0" newVersion="2.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.Mvc"
        publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="4.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.WebPages"
        publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0" newVersion="2.0.0.0"/>
</dependentAssembly>

so it should look like this

Altering Mirroring Endpoints (Ports) on SQL Server

We are in a middle of a SQL Server 2012 migration project and one of the main tasks is to completely mirror all of the databases. Having said that it does not mean we did not mirrored it before, some of the legacy databases are already mirroring and we have one dedicated server to do the job and that’s what it only does. With this project we want to install a new instance of SQL Server 2012 to be as witness for other SQL Server 2012 databases on top of SQL Server 2008 already installed in the server, so after installation we then setup the mirroring and then we had this error:

TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'YourDatabase'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address TCP://servername:5022 can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Now that is giving you an exception because port 5022 is used by the old SQL Server Witness, which means you need to change this port so that it would work.

Run this TSQL command in your Witness server instance to check if indeed 5022 is being used and at the same time what other ports you can use

SELECT * FROM sys.tcp_endpoints

To change that all you have to do is select a port number not on the list below as well as any port number that is not used by your server and applications, so for this example lets use 5023, you have to issue this command on your SQL Server Witness

ALTER ENDPOINT [Mirroring] AS TCP (listener_port = 5023)

Once done restart you will see “command(s) completed successfully“, you need to restart the SQL Server for it to take effect.  Now go back and configure your mirror again and it will now connect to the 5023 port

Moving a Sharepoint Sub Site to its own Site Collection in 3 easy steps

Moving a Sharepoint Sub Site to its own Site Collection why should I do that?  Well there might be a lot of reasons on doing this but for me its more handing over the Site collection Administration to the business which enables business owners to choose themes, manage security within the site collection, manage activated solutions and deploy other customizations.  Another good reason for me is about Quota Management which I can’t do in a Sub Site model.  How about you whats you reason?

So whatever your reason is I guess you either want to do it or thought of doing it that is why you are reading this article.  So let’s get started and perform that 3 easy steps in moving your Sub Site in its own Site Collection.

On this demo we will be moving a Sub Site called “Sandbox” which is located in http://someURL/SomeTeamSite/sandbox

Step 1: Export your subsite by using the Export-SPWeb command.

You can do this on your Sharepoint Server and run the command using the SharePoint 2010 Management Shell.  For this sample I had used the following command

Export-SPWeb -Identity "http://someURL/SomeTeamSite/sandbox/" -Path "C:\Exported Sub Site\SandboxSubsSite.cmp" -IncludeUserSecurity -IncludeVersions all

What that command does is basically export the sub site to a .cmp file where we included all Security Settings and Versions for more options check out Export-SPWeb on TechNet

You will notice once it exports you will have a log file showing you whats been done during the export process.

Step 2: Create you site collection.  This is where you will host your new site.  

Still on the Sharepoint Server (you can also do this remotely), go to “SharePoint 2010 Central Administration” then into “Application Management” then “Site Collections” then “Create a Site Collection”.

Indicate all of the properties needed

Take note you can also create managed paths, which means you can create a site at a specific path. To do that click on “Define Manage Paths” under the “Web Site Address”.

Once everything is set up properly you will receive a confirmation that your site collection was created.

Step 3: Import your Content Migration Package (CMP) file that you exported using Import-SPWeb.

Still in the Sharepoint Server using the SharePoint 2010 Management Shell issue the Import-SPWeb command, for this sample it would be something like this

Import-SPWeb "http://someURL/sites/Sandbox" –Path "C:\Exported Sub Site\SandboxSubsSite.cmp"

Like Export-SPWeb the Import-SPWeb can have several parameters which you can see on TechNet

It will also create an Import log and once the import finishes your all good to go.

Now check your new site collection and compare to the sub site, and if your happy feel free to remove the subsite.