none
moving all sharepoint 2010 databases to a new sql server RRS feed

  • Question

  • hi

    we are going to move all databases for project server 2010 and SharePoint 2010 to a new sql server

    I read 2 article and all things seem clear except this (To point the Web application to the new database server by setting up SQL Server connection aliases) in the second link

    http://technet.microsoft.com/en-us/library/ff961887(v=office.14).aspx (Project server 2010)

    http://technet.microsoft.com/en-us/library/cc512725(v=office.14).aspx (SharePoint server 2010) 1-as far as I know I have to use the second procedure . am I right?

    2- this paragraph is confusing .what does it mean? which servers do I have to do this on?

    To point the Web application to the new database server by setting up SQL Server connection aliases
    1.  

      Start SQL Server Configuration Manager. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Configuration Manager.

      noteNote
      If SQL Server Configuration Manager is not installed, you must run SQL Server setup to install it.
    2. Expand SQL Native Client Configuration, right-click Aliases, and then click New Alias.

    3. In the Alias Name field, enter the name of the original SQL Server instance, for Protocol, verify that TCP/IP is selected, for Server, enter the name of the new server that is hosting the SharePoint Server 2010 databases, and then click OK.

    4. Repeat this procedure on all servers in the farm that connect to the new instance of SQL Server.

    Sunday, November 16, 2014 8:36 AM

Answers

  • Hello,

    The high-level steps are below. I would test this on a test farm first:

    • Shutdown the SharePoint / Project Server
    • Backup ALL of the Farm databases from source SQL server
    • Restore ALL of the farm databases to the target SQL Server using the same names
    • set up all of the logins on the new target SQL server
    • script out the delete expired session SQL job from the source SQL server
    • create the delete expired session jobs on the target SQL server
    • repoint SQL Server Alias / DNS alias from source SQL server to the target SQL Server for SharePoint / Project Server
    • start up SharePoint / Project Server servers
    • set up SQL maintenance plans on new target SQL server

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    • Marked as answer by Sam-Net Tuesday, December 9, 2014 1:47 PM
    Sunday, November 16, 2014 12:40 PM
    Moderator

All replies

  • Hello,

    The high-level steps are below. I would test this on a test farm first:

    • Shutdown the SharePoint / Project Server
    • Backup ALL of the Farm databases from source SQL server
    • Restore ALL of the farm databases to the target SQL Server using the same names
    • set up all of the logins on the new target SQL server
    • script out the delete expired session SQL job from the source SQL server
    • create the delete expired session jobs on the target SQL server
    • repoint SQL Server Alias / DNS alias from source SQL server to the target SQL Server for SharePoint / Project Server
    • start up SharePoint / Project Server servers
    • set up SQL maintenance plans on new target SQL server

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    • Marked as answer by Sam-Net Tuesday, December 9, 2014 1:47 PM
    Sunday, November 16, 2014 12:40 PM
    Moderator
  • Hi,

    The steps need to be run on all SharePoint Servers in the farm (where SharePoint bits have been installed). On these machines you will need to configure a setting like (oldserver\oldinstance -> newserver\newinstance).

    Paul

    Tuesday, November 18, 2014 7:10 PM
  • Maybe this will help

    http://blogs.technet.com/b/meamcs/archive/2013/01/22/creating-a-sql-server-alias-using-the-sql-server-client-network-utility.aspx

    Paul

    thanks Paul

    it means I don't need to install 'SQL Server client tools ' on all SharePoint servers (1 app server, 1 WFE server and 1 report server).it is one of the windows server features. am I right?

    Saturday, November 22, 2014 10:59 AM
  • Hello,

    The high-level steps are below. I would test this on a test farm first:

    • Shutdown the SharePoint / Project Server
    • Backup ALL of the Farm databases from source SQL server
    • Restore ALL of the farm databases to the target SQL Server using the same names
    • set up all of the logins on the new target SQL server
    • script out the delete expired session SQL job from the source SQL server
    • create the delete expired session jobs on the target SQL server
    • repoint SQL Server Alias / DNS alias from source SQL server to the target SQL Server for SharePoint / Project Server
    • start up SharePoint / Project Server servers
    • set up SQL maintenance plans on new target SQL server

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    thanks Paul

    you are always very helpful

    would you please explain more about :

    • script out the delete expired session SQL job from the source SQL server
    • create the delete expired session jobs on the target SQL server

    I didn't find these two steps in the TechNet article

    Sam

    Saturday, November 22, 2014 11:03 AM
  • Project server 2010 will create a sessions database along with a sql job to delete the expired sessions. You just need to ensure you migrate this job also. If you look at the jobs on the sql server user sql management studio with a sys admin account you will see a delete expired sessions job. Right click a script it out to create a new job. It might need to be modified before you create it on the target SQL server, ensure the databases name and account details are correct.

    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP

    Saturday, November 22, 2014 11:24 AM
    Moderator
  • Regarding the alias, was the farm installed with the SQL server host name or using an alias (DNS or SQL)?

    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP

    Saturday, November 22, 2014 11:25 AM
    Moderator
  • Hi,

    That is correct, you don't need to install 'SQL Server client tools'. Try to run cliconfg in command prompt and see if you get anything (it should already be installed).

    If the old server is not used anymore maybe it is easier to configure a DNS alias (in case the native SQL instance or same name named instance is used)

    Hope this helps

    Paul



    Saturday, November 22, 2014 10:53 PM
  • Project server 2010 will create a sessions database along with a sql job to delete the expired sessions. You just need to ensure you migrate this job also. If you look at the jobs on the sql server user sql management studio with a sys admin account you will see a delete expired sessions job. Right click a script it out to create a new job. It might need to be modified before you create it on the target SQL server, ensure the databases name and account details are correct.

    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP

    why isn't this step  mentioned in TechNet article? what will happen if I don't do that?
    Sunday, November 23, 2014 10:44 AM
  • Regarding the alias, was the farm installed with the SQL server host name or using an alias (DNS or SQL)?

    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP

    unfortunately I don't understand what you mean. I installed this farm on a sql server which is named SQLSRV1

    is there any difference between them?

    Sunday, November 23, 2014 10:47 AM
  • Can't answer as to why that isn't in a TechNet article but if you don't recreate the sql job the expired user sessions will not be removed from the database. Paul

    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP

    Sunday, November 23, 2014 10:49 AM
    Moderator
  • Is SQLSRV1 the host name of that windows server? The advantage to use an alias is that the alias can be updated to point to another windows / sql server. Once you all the sql objects on the new SQL server you can just repoint the alias from the old SQL server to the new sql server. Sharepoint / project server works as it did before without having to reconfigure the farm to use a new SQL server name. If that is a host name, is the old server going to be turned off? If so, create a DNS alias of sqlsrv1 and point that to your new SQL server when you carry out the other tasks. Paul

    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP

    Sunday, November 23, 2014 10:54 AM
    Moderator