SQL server transfer woes

[neonetman]

I've just installed a new Server 2016/Sql Server 2014 box to move our DBs onto:-

 

* Firewall turned off

* remote connectivity turned on

 

I'm trying to copy a database from our old (Server 2008/SQL 2008) box to our new one and am hitting problems:-

 

* Using SSMS on the old server, it cannot connect to the new server

* Using SSMS on the new server, I can connect to the old server and initiate the copy, however it fails on the execute step, stating it cannot connect to the old server

 

As SQL server is not one of my strengths I'm struggling a bit. Can anyone help with this please

 

The project as it stands involves moving all the DBs to a temp server whilst we install the new OS and SQL on the original box, then move all the DBs back again.

[Steve21]

Did you check the ports it's set to connect on under the bindings? I remember it changed to dynamic somewhere in the past but can't remember when.

 

Under the SQL config manager (not SSMS) check the SQL Network Config -> Protocols for "server" and then the IP tab for what it's set to access on

 

Steve

[neonetman]

Set to Port 1433 and dynamic is blank

[Steve21]

And is the browser service running? Else you'd have to use that port number when connecting

 

Steve

[neonetman]

OK - so I've got a bit further now, but have hit another issue. I'm getting a 12550 error in the event log which reads:-

 

* InnerException-->Windows NT user or group '{removed for security}' not found. Check the name again.

 

The username relates to a user who has long since been deleted. I've looked in SSMS under both Security/Logins as well as under {database name}/security/users and can't see any refernece to this use.

 

I've tried re-adding this user in AD, which seems to resolve it but it then fails on a different deleted user.

 

Obviously I need to find and remove the references to these former technicians accounts, but I have no idea where to look now

[TwistedHelixis]

Just a though and I am probably wrong but could you not install sql 2014 on your old server, then do an internal migration from SQL 2008 to SQL 2014 which should be easy (ish). Then install sql2014 on your new server and do a backup and restore to move the database.

 

Any

[Steve21]

Those errors are normally if you're not running the SQL services (old server) as a domain account, as the local account can't connect to the other server.

 

What's the service running on both sides?

 

Steve

[neonetman]

Because I'm doing an OS upgrade at the same time, I'd be happier to migrate databases properly to a temporary server prior to rebuilding the original box.

 

It's just these ghost user accounts causing a niggle now

[neonetman]

Has anyone got any thoughts on this?

[Steve21]

Did you check my above post? Think you cross posted it.

 

Steve

[neonetman]

The services are running as a dedicated domain admin account