July 17, 2007 at 1:26 pm
Attempting to use the detach / reattach method of porting the database. Simple setup - no triggers, no replication, no publication, using clone of production data base as proof of concept.
The Developer working with me attempted to connect to the new database using his version of Delphi-coded application, and then diagnose issues with Delphi code.
Problems/issues/concerns
1. He was unable to log in. As part of the login process, a record is inserted into table PRG_Log, letting the database set the identity value. In SQL 7, this table has the field 'blank' as primary key, with Identity 1,1 BUT in SQL 2005, primary key field is indicated, but NO identity settings.
2. Other tables are missing primary keys as well as identity settings.
3. Security permissions are not existing - error messages indicated user has SELECT permission denied on two different tables.
4. Correctly received 'Invalid column name' because of version skew between Delphi code and database version [this worked correctly!].
Using SA and remote terminal into console (as if local on server), I insert the new field into the table, populate the field for existing records, and then change it to not allow nulls. So far so good. I go to the PRG_Log table, and attempt to update the field to have its identity property matching the SQL 7 version - 1,1. I receive a warning that the file is large, and that the file will be unavailable while updating. I then receive a timeout message indicating (per BOL) that I am unable to connect to the database.
1st QUESTION: Why am i receiving a connection timeout error message? (Error message follows: 'PRG_Log' table - Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.)
2nd QUESTION: When I destroy the primary key, and then reattempt the step above, same error message. When I then attempt to restore the key, same error message.
3rd QUESTION: Since I have other tables which seem to also be missing the primary index entries and the Identity aspects on the primary key fields, what would I be looking for in the way of tests I could run to verify the correct transfer of all appropriate content into the new server/platform/hard drive?
4th QUESTION: Is there a way, other than assigning privileges to each individual user ID, of providing the basic access rights of 'SELECT', 'INSERT' and 'UPDATE' or db_datareader and db_datawriter? Are there additional attributes to be mapped, when users are permitted access ONLY via the Delphi interface?
I apologize for the congested question, but this should be a good beginning for a white paper or article, when all is said and done, for those few folk who are still on SQL 7 (remember the survey of a couple months back?) and looking to migrate forward to 2k5. [evaluation copy of standard version 9.0.1399).
TIA.
Steve
July 17, 2007 at 3:06 pm
I think that backup/restore is a better method to use for database migration from SQL 7.0 to 2005.
I don't see how detach/attach could cause the problem you described.
July 17, 2007 at 4:49 pm
I don't know about Delphi or SQL 7.0, but we recently have been migrating from SQL 2000 to SQL 2005, so I have a couple of general pointers.
First, I agree with Michael that the backup/restore method would probably work better for you. (I've actually seen the missing identity index attributes for a primary key after a detach-move-attach, but I didn't become involved until after the fact, and a backup/restore fixed the issue).
Your security/permissions issues could be due to db users orphaned from SQL logins. If you google "sp_help_revlogin" you should find a script for transfering logins between instances of SQL server, but I think it only works for Instances of the same version. Otherwise, you can use sp_helpuser and sp_change_users_login to associate orphaned users with new logins.
Hope that helps...
July 18, 2007 at 1:28 am
Also remember to script jobs and move there. And change the compatability mode to 90 for all databases in sql 2005 so that you can take the advantage of sql 2005 features.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 18, 2007 at 7:20 am
Interestingly enough, I goofed in my original statement. The failures were encountered on a database that had been restored from a backup.
When we pointed the application to the attached files database version, the only problem we encountered was related to 'orphaned' SQL Server user ids. using
------- sp_change_users_login REPORT
followed by
------ sp_change_users_login UPDATE_ONE 'loginid' 'loginid'
managed to get the developer in using the application, and all features within the app seem to be working.
All that remains to do is, as was noted above, to port the jobs over.
regarding the compatibility, i'm not convinced that we're ready to go to 90 yet - that testing remains to be performed. But at least we know this puppy is working. For my money, the detach / copy or move / attach to 2k5 is definitely the way to go. At least, based on my experience so far. Remember though, this was a very elementary implementation of SQL Server features - SQL Server security, stored procedures and functions, and backups. No replication, no publishing, etc etc etc.
thanks again for all the good advice, both here and in other threads!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply