Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : MS SQL problems after host migration

[lardog]

My reseller web host (DIY) migrated a few weeks ago to a new data center.
Unfortunately, my MS SQL databases were not migrating properly. (as well as
other things). I have multiple sites that are not functional due to
problems with their migration.

When I looked at some of the databases, the table structure had been
migrated, but not the data and apparently something else.

Some of the databases of this error when trying to access it:
--- error ---
Error for database: DATABASENAME
Microsoft OLE DB Provider for SQL Server error \'80040e37\'
Invalid object name \'TABLENAME\'.
--- end error---

Some of the databases I can actually access but get:

Microsoft OLE DB Provider for SQL Server error '80040e2f'
Cannot insert the value NULL into column 'TABLENAME', table
'DATABASENAME.dbo.TABLENAME'; column does not allow nulls. INSERT fails.
Everything in all databases worked perfectly before the migration.

They tell me that they have done what they can. They tell me that they are
at the mercy of Psoft who assisted with the migration.

Does anyone know of anything that can my host that can help them figure out
the problem.

Any assistance will be GREATLY appreciated.

[joshuayip]

You can try looking at dbforums.com for answers on this one. I have 0 knowledge on this. Sorry =)

Joshua

[RofyHost]

it seems to be a versioning problem, are you sure the new MS SQL server version is the same as the old one ?

[andy18]

do your provider have backup of your database before the migrate ? try restore it from backup and see if it fix up the error.

[monaghan]

Looks like you may no longer "own" the databases.

By default (I'm assuning you're using SQL security rather then NT integrated security) the SQL user will create a unique ID for that server (or domain if using NT security).

If the database is restored or moved to another server, even if the same user name is created it will have a different ID and therefore not have access to the database.

This is something that their DBA should be able to resolve easily, if they don't have a DBA, then I guess they need to hire a SQL consultant ASAP.

[lardog]

hmmm. as far as I know, it was moved from MS SQL 2000 to another MS SQL 2000 (not positive though).

I am under the impression that they used a backup/restore procedure to migrate. Although I have some doubt with this as some databases only had tables in them and not all data.

I am currently under the impression that they do not think paying for a consultant is an option as they are using PSOFT for the migration.

I will ask if there is a database version change and verify what they actually did to migrate (if they know). Is there anything else that I should ask that would help?

[monaghan]

Versioning is not a problem, if they try to restore to a lower version it will refuse, if they restore to a higher version it will up-convert.

Obvious question to ask "Why should I keep my hosting with someone who can't support the database platform they sell ?"

[lardog]

monaghan,

gee, I wish they had you working for them.

I have been with DIY for a year. I really do not want to leave especially since it is a reseller hosting account and I do have other clients (although most sites are mine). But I am getting the hint that if they are allowing databases to be down for weeks at a time...

For the most part, they have been excellent over the last year. They have been loyal to me, so have tried to be loyal to them, but databases down for over a week due to a migration is not good.

[IHSL]

Hi Larry,

Yes, we used PSoft for the migration. Unfortunately, the DB issues you saw are not corrected by a db restore from previous backups.

As you know, the two bigger databases, we were able to force fixes on with manual shifting. Unfortunately some of the others give off errors that are not defined correctly. For instance, an 'invalid object' error message being thrown out, when the object is of course there, is not something that is even corrected by restoration (w/ complete removal, then addition of user/login).

If you'd like to give me a call (ext: 86) and do some more live troubleshooting, please feel free to do so

Originally Posted by monaghan

Obvious question to ask "Why should I keep my hosting with someone who can't support the database platform they sell ?"

How is that an obvious question to ask?

Firstly, we fully support MS SQL, and have done so for a very long time. This migration had unforeseen issues that have so far been subject to troubleshooting, and then fixes applied where possible.

There is some issues that are extremely difficult / impossible to reproduce on another database.

FYI: Microsoft top tier TS support could not give us the immediate answer. I presume you also would say they also should not offer it?

Simon

[IHSL]

Originally posted by lardog
But I am getting the hint that if they are allowing databases to be down for weeks at a time...

For the most part, they have been excellent over the last year. They have been loyal to me, so have tried to be loyal to them, but databases down for over a week due to a migration is not good.

It is not a case of us allowing databases to be down for X number of days (see above, also)

The reason I knew it was you, Larry, is because your DB's are the only one's with issues.

For the most part, we are indeed at the mercy of PSoft for fixing migration issues. As they did the migration, then they would know what part of their move scripts caused an issue, which would give us instant ability to reverse engineer it.

There's a possibility that if we have another live troubleshooting session, much like our midnight one a week or so back, we can get more of the issues sorted out. Issues like the ones you're seeing are almost impossible to fix via TT, as it does obviously go smoother in a live scenario.

Give me a call when you're free for an hour or two, and let's see how many (if not all) issues we can knock over.

Ext 86 will patch you through to me.

Simon

[lardog]

Hi Simon,

I am glad that you found my post.

That sounds excellent. I wish that I could call right now, but I am at my job for the next 7 hours or so.

I will call as soon as I can.

[IHSL]

Originally posted by lardog
Hi Simon,

I am glad that you found my post.

That sounds excellent. I wish that I could call right now, but I am at my job for the next 7 hours or so.

I will call as soon as I can.

Thankfully, a friend of mine pointed this out to me.

I look forward to speaking with you later tonight, and hopefully get these issues sorted for you.

Simon

[monaghan]

Originally posted by lardog
monaghan,

gee, I wish they had you working for them.

I have been with DIY for a year. I really do not want to leave especially since it is a reseller hosting account and I do have other clients (although most sites are mine). But I am getting the hint that if they are allowing databases to be down for weeks at a time...

For the most part, they have been excellent over the last year. They have been loyal to me, so have tried to be loyal to them, but databases down for over a week due to a migration is not good.

If the hosting is generally good, then no rush to move, however, I can see the frustration of the database move. I guess the source server is no longer available ? If there's not been a problem with other customers (as suggested in other posts), then possibly there could have been an issue on the old server.

If you still have access, then try BCP to export the database content and script the database structure. Recreate the database from the script(s) and BCP the data back in. (Their DBA will know how to do this). Alternatively if the old server is still available, they can detach the database, copy the files to the new one and attach the database, then resolve any permissions issues (you'd still want to investigate the root cause though)

[monaghan]

How is that an obvious question to ask?

Firstly, we fully support MS SQL, and have done so for a very long time. This migration had unforeseen issues that have so far been subject to troubleshooting, and then fixes applied where possible.

There is some issues that are extremely difficult / impossible to reproduce on another database.

FYI: Microsoft top tier TS support could not give us the immediate answer. I presume you also would say they also should not offer it?

It was an obvious question for me to ask as I often have to fix SQL systems that have supposedly had a DBA configure the system. I don't know your skills and don't want to get into a discussion on this, but my experience with most SQL Server DBA's is that they can drive Enterprise Manager for basic admin but if you ask them to enable a trace flag or to analyse a slow running query they're lost. I don't claim to know everything, but I've been running SQL Server since 6.0 and have resolved many an issue along the way.

Surely the MS guys would have had remote access to the box, from my experience with MS support they should have been able to come up with a solution. Have they detached the database and taken a copy to reproduce in the lab ? If not perhaps this is the route to go.

[IHSL]

monaghan,

As stated, they [MS] could not give immediate resolution.

DBA's have indeed looked at the issue, and if a resolution was forthcoming, it would have been applied.

As with the fixed databases of Larry's, the best thing in these types of instances, is live troubleshooting with Larry, whom I [and our technicians] will happily conference with, through to resolution.

My point in my reply to you, was to not 'knee-jerk' with unfounded claims/statements. We do not pretend to be able to fix every issue at a glance. We can fix almost any issue, and for those that we can not, we call in Microsoft's support team.

If we still can't fix Larry's issue(s) after another troubleshooting session, then we'll bring in a developer who can. Larry is a valued client, whom has already stated he has had a great year with us, and as always, we'll do everything we can to make sure he stays in that frame of mind.

Simon