restoring mssql - user problem/bug in HC

hi
 
we are playing around with HC still and now came the turn to mssql :) we created a 2008 server and it seams to work fine - until we restore an existing DB into the newly created in HC. The usecase is that we are moving a existing website from our old hosting setup or a new customer who has a exsisting website running somewhere else.
 
1. create the site in HC
2. create the database in HC with a user
3. we are then connecting to the mssql server directly and overwriting the database with the backup from the old site - ths might be a mssql2000 or mssql2005 even.
 
now because of this, the user created in HC for the database does not work. But we cannot remove the user and recreate the user or just change the password.
 
We can create a new user, but it leaves us with a lot of "dead users" in HC.
 
 

I think you better upgrade your SQL Server 2000 and 2005 DBs first to SQL 2008 before migrating these DBs into HC because there are issues with most of the softwares from Microsoft with their upgrades and not only with SQL Server in particular. I hope that by first upgrading DBs manually to SQL 2008 and then uploading the backup file to HC would work for you.

FYI: Sql 2008 Express edition allow to have db size 10 GB per db.
Personally I do prefer sql 2008 as 10 gb size per db is sufficient enough .

unless something has changed its not leagal to do hosting on an express edition?

anyway, we cannot upgrade the mssql2000 server or even the 2005 server we are getting customers from. We can only get the backup files and then restore from them. It seams to be ok except for the user issue.

This was actually also an issue in HELM - our current hostingpanel. I think its like this in genereal. When you get a backup file from somewhere it contains information about the user/login mapping (im not an expert here, forgive me if im naming it wrong or if im just plain wrong :) )
So if the controlpanel already has created the database AND a user and then you restore, then the problem seams to be that the first login is no longer associated with the database.

We has this, as I mentioned, also in HELM. If we created a database and a user for a customer, and then later got the backup, then we did 1) a restore, then 2) deleted the user in HELM and 3) recreated the user again and everything was working.

Im guessing !!! that deleting a user is a 2 step thing? 1) delete the mapping to the database and 2) delete the actual user login? And maybe HC fails doing 1) first and then does not continue to 2)? Maybe the reason why HELM is "working" is that they delete the problems they get in step 1) and then simply does step 2) anyway?

There is no term involve for legal or illegal to use sql express. How often you would see a database size increase than 10 GB.. Atleast I haven't seen a huge db size yet from any of my user yet.

BTW: If you do restore sql 2000 db in sql 2008 manually then still you may face issue with db user. As sql don't restore the db user password. I have suffered with this problem while upgrade from sql 2005 to sql 2008.

If somehow you manage db manually in the sql 2008 then you can make db entry manually in the HC database ;). This way there wouldn't be any conflict with db user etc.

As of early 2006, the Express Edition had a clause in its EULA not permitting the use in commercial hosting services.
This may have been removed.

you cannot provide hosting on MS products unless you are SPLA licenced and Im thinking again that you cannot as a SPLA partner use Express edition. You customers, if they have a dedicated server can probably install Express them self.

BUT IM NOT AT ALL SURE - who really knows whats going on in MS heads :) we try to limit what MS hosting we have...

anyway, it can only handle 1GB RAM and other limitations so the webedition seams a better and safer to use.

anyway, back the to user issue. We restore manually on the server. We could clean up manually also, but Im thinking not just we, but also others, maaaaaybe even HC (even if Im very unsure about that) might have an interest in that HC could handle this - deleting a user that might not acutally be in the database anymore. Some more "fault tolerant" code?

patrick

There is no term involve for legal or illegal to use sql express. How often you would see a database size increase than 10 GB.. Atleast I haven't seen a huge db size yet from any of my user yet.

BTW: If you do restore sql 2000 db in sql 2008 manually then still you may face issue with db user. As sql don't restore the db user password. I have suffered with this problem while upgrade from sql 2005 to sql 2008.

If somehow you manage db manually in the sql 2008 then you can make db entry manually in the HC database ;). This way there wouldn't be any conflict with db user etc.

I agree to this solution as it helped me too to migrate DBs from SQL 2k5 to SQL 2k8 when I faced this issue. And yes, it is an issue at Microsoft end about user's restore and mapping and not because of control panel. Even you would face such issue with all other good control panels in Windows market

nobody is saying its a problems started or caused by HC.

Im suggesting that HC could build a solution to this problem since the problem is real for me at least and I hope others would like to see HC be able to handle it.

We alllll agree that this is a problem with the way databases are restored. This is even a problem if you get a backup from a customer how was running 2005 somewhere else and you then have to take over the hosting and restoring it on a 2005 server also.

There are two simple "solutions" in my view, HC maybe have better ideas if they care at all of cause :) 

1) when creating a database in HC let me create the user and DB in two different steps. Then I can restore between the steps and then I will not have failded users haning in HC.

Or 2) allow me to delete a user "by force" in HC if the "soft" delete fails.

Im thinking the problem extends to when some day the customer want to stop using my services, then I cannot delete the user, so I cannot delete the database, so I cannot delete the website? (have not tested). So some "force this out of my HC database"-code will sometimes be a good idea.

When it comes to the comment ..."Even you would face such issue with all other good control panels in Windows market" - yes, as I wrote, HELM that we know very good, have ofcause the same issue since its not HELMs fault, but they just made HELM work in the sense that You could remove the user and recreate it and "fix" it this way. Actually you also create the databse and user in two different steps in HELM so even better :)