Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Cannot move Sharepoint databases to new sql server

已答覆 Cannot move Sharepoint databases to new sql server

  • Saturday, October 06, 2012 3:08 AM
     
     
    Sign In to Vote
    0

    SQL 2005 server on Windows 2003 with Sharepoint 3.0

    Trying to move the Sharpoint databases to my SQL 2008 Server running on my Windows 2008 Server both 64 bit

    I added the following server roles dbcreator processadmin diskadmin bulkadmin securityadmin serveradmin setupadmin sysadmin to a domain user account named spadmin

    SPadmin is the account I am trying to create the new farm database with.

    What am I missing?

    Thanks

    Tom

    Thomas R Grassi Jr

     

All Replies

  • Saturday, October 06, 2012 6:18 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    Are you sure the instance name of that SQL Server 2008 is tcgcs010\sharepoint? Run the discovery report as explained on the following article:

    http://blogs.msdn.com/b/petersad/archive/2009/11/13/sql-server-2008-discovery-report.aspx

    On the column with the tile “Name” you will find the instance name of the server.

    Remember to configure Windows Firewall to allow access to SQL Server on both servers involved in the migration.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked As Answer by TRGOneCare Saturday, October 06, 2012 9:31 PM
    •  
     
  • Saturday, October 06, 2012 2:43 PM
     
     Answered
    Sign In to Vote
    0

    Alberto

    Ran report and my instance name is valid

    Sql Server 2008 SHAREPOINT MSSQL10_50.SHAREPOINT Database Engine Services 1033 Standard Edition 10.51.2500.0 No
    Sql Server 2008 SHAREPOINT MSSQL10_50.SHAREPOINT SQL Server Replication 1033 Standard Edition 10.51.2500.0 No
    Sql Server 2008 SHAREPOINT MSSQL10_50.SHAREPOINT Full-Text Search 1033 Standard Edition 10.51.2500.0 No

    On my windows 2003 R@ Standard server that runs SHarepoint I do not have a firewall running.

    I do have a firewall running on the Windows 2008 R2 STandard 64bit Server running SQL 2008 64 bit

    I have inbound rules setup for the dynamic port that this instance is using.

    I do have another instance that I am using for my production database and thats is working fine I can access that database from anywhere via the web. So I think I have the firewall setup ok. Unless sharepoint is looking for someother port to be open.

    AS a test I stopped the firewall on the sql server and i received the same error So I believe the firewall is ok .

    Is there a sqlcmd function I can run to test this?

    I tried this from the windows 2003 server

    sqlcmd -S server10\sharepoint -U spadmin -P xxxxxxx

    HResult 0xFFFFFFFF, Level 16, State 1
    SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re may be caused by the fact that under the default settings SQL Server does not
     allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    Now if I do this

    sqlcmd -S server10

    >1

    it connects to the default instance but will not connect to any of the other instances

    If we figure this out then the Sharepoint install will work because it is doing a logon to the instance.

    I also get the same results on my workstations when I do the sqlcmd.

    Just ran this on my workstation and on the Windows 2003 Server

    C:\>sqlcmd -L

    Servers:
        SERVER01
        SERVER10
        SERVER10\SHAREPOINT
        SERVER10\PRODUCTION

     So I can see the SQL servers and all instances so why cant I logon to them?

    Any other ideas?

    Thomas R Grassi Jr





    • Edited by TRGOneCare Saturday, October 06, 2012 3:41 PM
    • Marked As Answer by TRGOneCare Saturday, October 06, 2012 9:31 PM
    •  
     
  • Saturday, October 06, 2012 4:10 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    Please note that named instances use dynamic ports. Please use the following guide to configure Windows Firewall or disable it temporarily just to confirm is a firewall configuration issue:

    http://technet.microsoft.com/en-us/library/cc646023(v=sql.100).aspx

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



     
  • Saturday, October 06, 2012 4:18 PM
     
     Answered
    Sign In to Vote
    0

    Alberto

    I did do that already. I know the instances use dynamic ports. I made the dynamic ports static and configured the firewall for those ports both tcp and udp for all my instances. I do not believe it is a firewall issue. I stoppped the firewall on the server and it still fails.

    What about my sqlcmd examples? If I cannot logon to the instance from sqlcmd the sharepoint install will not work either.

    Alberto

    I just tried this from the Windows 2008 R2 Standard 64 bit server running SQL 2008 64 bit

    sqlcmd -S server10\sharepoint

    >1

    then I tried this

    sqlcmd -S server10\sharepoint -U spadmin -P xxxxxxxx

    Msg 18456 level 14 state 1 server server10\sharepoint line 1
    login failed for user domain\spadmin

    So even on the same server I can not even logon to the instance with this user account

    What am I missing on sql security this account spadmin has all the roles

    Any thoughts?

    Thomas R Grassi Jr


    • Edited by TRGOneCare Saturday, October 06, 2012 4:31 PM
    • Marked As Answer by TRGOneCare Saturday, October 06, 2012 9:31 PM
    •  
     
  • Saturday, October 06, 2012 4:41 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    You are trying to get connected using a SQL login, and it requires the instance is configured on mixed mode:

    http://technet.microsoft.com/en-us/library/ms188670(v=sql.100).aspx

    Make suren TCP/IP and Named Pipes protocols are enabled. Do not enable VIA.

    http://msdn.microsoft.com/en-us/library/ms191294(v=sql.100).aspx  (Restart SQL Server service when finish)

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked As Answer by TRGOneCare Saturday, October 06, 2012 9:31 PM
    •  
     
  • Saturday, October 06, 2012 6:27 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Alberto

    I did do that already. I know the instances use dynamic ports. I made the dynamic ports static and configured the firewall for those ports both tcp and udp for all my instances. I do not believe it is a firewall issue. I stoppped the firewall on the server and it still fails.

    What about my sqlcmd examples? If I cannot logon to the instance from sqlcmd the sharepoint install will not work either.

    Alberto

    I just tried this from the Windows 2008 R2 Standard 64 bit server running SQL 2008 64 bit

    sqlcmd -S server10\sharepoint

    >1

    then I tried this

    sqlcmd -S server10\sharepoint -U spadmin -P xxxxxxxx

    Msg 18456 level 14 state 1 server server10\sharepoint line 1
    login failed for user domain\spadmin

    So even on the same server I can not even logon to the instance with this user account

    What am I missing on sql security this account spadmin has all the roles

    Any thoughts?

    Thomas R Grassi Jr



    -U is used when you use SQL account. In the first post screenshot, you have given domain account. So, you need to login to operating system with the account. and then use

    sqlcmd -E -S server10\sharepoint

    That should simulate the error.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked As Answer by TRGOneCare Saturday, October 06, 2012 9:32 PM
    •