migrating sharepoint 2010 to 2013 - issues copying SQL database

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.
LVL 1
tech53Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
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
becraigCommented:
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
nemws1Database AdministratorCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tech53Author Commented:
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
tech53Author Commented:
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
becraigCommented:
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
tech53Author Commented:
Ok. I've done that before except for the logins. Databases are quite large so I'll try it in the morning. Cheers.
0
becraigCommented:
Any further issues or are you all sorted out ?
0
tech53Author Commented:
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
becraigCommented:
Sounds great, just wanted to make sure we did not abandon you :~)
0
tech53Author Commented:
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
tech53Author Commented:
Any help with the above?
0
becraigCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
becraigCommented:
Do you need any further help on this or have you gotten everything resolved at this point ?
0
tech53Author Commented:
Apologies.  We are about to do this in the production environment now so will let you know how it goes.
0
becraigCommented:
Great I hope all goes well.
0
tech53Author Commented:
The database and logins migrated perfectly.
0
becraigCommented:
Happy to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.