[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

migrating sharepoint 2010 to 2013 - issues copying SQL database

Posted on 2014-08-11
18
Medium Priority
?
345 Views
Last Modified: 2014-11-03
Dear Experts,

Before you begin reading, please be aware that I have little knowledge of SQL server and even less of Sharepoint.

I have a client with a server in a domain which hosts sharepoint 2010 standard and MS SQL server 2008 (not R2).  The admin (and developer) had originally setup the environment to 'play around' with sharepoint and it quickly became a production server with 150 users using it daily.

Now, he wishes to upgrade to sharepoint 2013 to take advantage of some new features. Having been given an extortionate quotation from a 3rd party, he has tasked me with this challenge. So I have P2V'd his DC and sharepoint server and took them back to my lab to do a trial run.  I setup the VMs and added a new VM with server 2012, sharepoint 2013 and SQL server 2014.

I found an article here: http://www.codeproject.com/Articles/663663/Live-Migration-of-SharePoint-to-SharePoint 

I have been following this article and have come to the point where I am copying the database from the old server to the new one but have hit a problem.

As I said above, the source server uses SQL server 2008 and the destination uses SQLserver 2014. During the wizard, I am asked to enter the destination server details.  When I browse for the destination server it cant find any.  when I manually type in the name of the destination server, I get the following:

Failed to connect to server 'destinationservername'
Additional Information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify the instance name is correct and that SQL Server is configured to allow remote connections. 9provider: Named Pipes Provider, error: 40 - Could  not open a connection to SQL server)  (Microsoft SQL Server, Error:5)

I then cancel the database copy wizard and restart it. This time, when I manually enter the destination server details, it continues to the end and then fails on:

Create failed for JobStep.

Additional information:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The @flags parameter is not valid for a job step of type 'SSIS'. (Microsoft SQL Server, Error: 14545)

I can ping both servers from each other and openly browse the c$ share so its obviously an SQL thing.  Perhaps moving a database from SQL server 2008 to 2014 isn't possible?   I know that I could do the detach are reattach thing but I don't know where that would leave me with the step-through article.

All help appreciated.
0
Comment
Question by:tech53
  • 8
  • 8
  • 2
18 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 40253200
On your destination (2014) server, run the "SQL Server 2014 Configuration Manager", expand "SQL Server Network configuration" and select the instance you're migrating to.  Double-click on "Named Pipes" in the right hand side and then select "Yes" besides "Enabled".  Then restart your SQL server.

If the client & server are on the same machine, then most applications try to use a Named Pipe, since its a little more efficient in that case than using TCP/IP (and why bother, when you're both on the same host).
0
 
LVL 29

Expert Comment

by:becraig
ID: 40253201
so this sounds like an issue with the configuration of the sql 2014 server.

Either the firewall is blocking the port of you need to configure connections from the 2014 server.
1. Click Start | Control Panel | Network Connections
2. Right click on the network adapter that services inbound connections to SQL Server and select Properties
3. Select Advanced | Windows Firewall | Settings
4. Select Exceptions | Add Port
5. Under Name put "SQL Server" and under Port Number select 1433. Use TCP as the protocol.
6. Click OK to close out all dialogs.


Ensure SQL is listening on tcp/ip, the link below will show you how to:
http://msdn.microsoft.com/en-us/library/ms177440.aspx
0
 
LVL 23

Expert Comment

by:nemws1
ID: 40253206
BTW - I was basing my suggestion on the error you posted:
The server was not found or was not accessible. Verify the instance name is correct and that SQL Server is configured to allow remote connections. 9provider: Named Pipes Provider, error: 40 - Could  not open a connection to SQL server)  (Microsoft SQL Server, Error:5)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:tech53
ID: 40269072
Thanks folks.  I have tried your suggestions but to no avail.  I'm wondering if this is an issue between the different versions of SQL server.
0
 
LVL 1

Author Comment

by:tech53
ID: 40271394
Having played around a bit further, I discovered that I could use the 2008 SQL management studio to successfully connect to the 2014 SQL server and browse the databases!  This would certainly suggest that there are no communication issues between the servers.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40271410
Ok so in looking over your issue we seem to have tried to fix a glitch instead of addressing the overall issue.

You can do the detach and reattach or backup / restore.

Then simply verify the logins in step 16 are properly configured in the destination server (you can do this in the security tab  in ssms in the gui on the destination server)

Then you simply continue from step 21 once you have validated the logins and ensure the DB is properly attached / restored on the destination server.
0
 
LVL 1

Author Comment

by:tech53
ID: 40271514
Ok. I've done that before except for the logins. Databases are quite large so I'll try it in the morning. Cheers.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40293173
Any further issues or are you all sorted out ?
0
 
LVL 1

Author Comment

by:tech53
ID: 40293440
Sorry. I've had the rare pleasure of being on leave. Physical database files have copied.  Will get back with results on Monday. Cheers.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40293443
Sounds great, just wanted to make sure we did not abandon you :~)
0
 
LVL 1

Author Comment

by:tech53
ID: 40303529
Hi again.  I have copied the database files over to the new server.  Once I attach the file in SQL management studio, how do I configure the logins?
0
 
LVL 1

Author Comment

by:tech53
ID: 40313434
Any help with the above?
0
 
LVL 29

Accepted Solution

by:
becraig earned 2000 total points
ID: 40313444
Sorry for the delay.

Here are the steps on backing up the current logins and restoring on the new server:
http://msdn.microsoft.com/en-us/library/ee406927.aspx
0
 
LVL 29

Expert Comment

by:becraig
ID: 40347714
Do you need any further help on this or have you gotten everything resolved at this point ?
0
 
LVL 1

Author Comment

by:tech53
ID: 40419222
Apologies.  We are about to do this in the production environment now so will let you know how it goes.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40419722
Great I hope all goes well.
0
 
LVL 1

Author Closing Comment

by:tech53
ID: 40419772
The database and logins migrated perfectly.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40419786
Happy to help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question