Migrate Data SQL 2000 SLX7.0 to SQL 2008 SLX7.5 [ Edited ]

[jjbingen]

We are in the process of upgrading from SLX7.0 built on SQL 2000 to SLX7.5 built on SQL 2008.  We would like to migrate our data from 7.0 to 7.5.

I have found very few guides by Sage or online about how to do this- so thus this post.  I have attempted a few ways to migrate the data.  My attempts are below:

1. The Backup/Restore method does not appear to work because 7.0 has some unique key applied to the SLX7.0 DB and will not restore into the SLX7.5 DB.
2. Creating my own 'SalesLogix' DB then restoring to this does not appear to work either.  This method restores the data into 7.5 but then I can no longer login into Administrator so the restore must break something... it gives 'connection failed' on .UDL test. This method also does not contain all the 7.5 tables/columns.  
3. The SSIS> Import/Export Data seems like the best tool to use for this.  DB integrity is maintained for 7.5 and 7.0 data is imported.  This in theroy is exactly what I am looking for.  I recieved two errors when I attempted to import the data.  The frist one was 'Violation of primary key constraint'  This error occured when data already existed in the 7.5 DB.  After removing all the data from the 7.5DB this error went away but only to be replaced by another one... 'the value violated the integrity constraints for the column.'  This error is hard to find good information on and the logs in SSIS are cryptic to read and hard to figure out exactly what it is pointing at. I would be more than welcome to post my the log file if this will assist.  I believe it has something to do with importing null values into 7.5.  I have not found a way to turn this is off using the SSIS wizard.  If anyone knows how to do this...?

So if anyone has any suggestions on an migration method...?

Thanks for any help.

Message Edited by jjbingen on 04-15-2010 09:43 AM

Message Edited by jjbingen on 04-15-2010 09:44 AM

[DanCarvin]

1 or 2 should work provided you are carefully following the upgrade documentation.   As for the error you mentioned in #2, there are some extra steps required when setting up the connection in Connection Manager when using SQL 2008.  This is described in the implementation PDF.   Don't forget to run Integrity Checker before starting the upgrade (and make a backup before doing ANYTHING).

[jjbingen]

Perhaps I missed it but I looked in the 'Sage SalesLogix Implementation Guide' and the 'Upgrading Sage SalesLogix from Version 7.0.x to 7.5' and neither mentioned my enviroment.  Perhaps I missed it?

We have an old box where Server03 SQL 2000 and SLX7.0 sit and a completly new box where Server08 SQL 08 and SLX7.5 sit.  We would like to restore data form the 7.0 box to the 7.5 box.

I followed the implentation guide for 7.5 but it did not mention moving/migrating data... unless of course if I missed this?

Thanks.

[MikeSpragg]

You didn't miss it - it doesn't exist (this is simply a SQL2000 to 2008 upgrade - and is covered in Microsoft's documentation).

But, very easy - just backup on SQL2000 then restore on 2008. Then follow the guide for SQL2008 in Sage's documentation (search for 2008 - you should find everything you need).

[jjbingen]

I can not back up SQL 2000 and restore to SQL 2008.  The error message says: The backup set holds a backup of a database other than the existing 'salesladies' database.  This makes sense because it is a different DB.

SSL server won't be able to connect to the 'old' DB because it is not tied up with SSL 7.5.  No guide tells you just how to import data from SQL 2000 will maintain the new tables in SQL 2008/SLX 7.5.

How do you maintain the SLX7.5 DB but restore it with SLX7.0 data?

Am I still missing something?

[RJLedger]

Just do a normal (Enterprise manager) Full SQL backup of the SalesLogix db in SQL2000...

You then do a normal "restore database" in SQL 2008 Management studio (using that sql backup)

Don't forget to create the sysdba user in SQL2008 as well as "fix" the sysdba after doing a restore.

[jjbingen]

Closer... I have restored the SalesLogix DB.  All of our data is present and accounted for.

I am now attempting to log into Administrator using the admin password.  No what I enter in: 'Database login failed: Catastrophic Failure' error appears.  When I use the test UDL the error message is 'test connect failed.'

I have attempted to use the sysdba 'fix' and made it through almost all the scripts on the below website expect for the one that matters...

http://crm.ittoolbox.com/groups/technical-functional/saleslogix-l/automate-attachment-process-for-re...

[code]

osql -Usa -P{password}
1> use saleslogix

2>sp_change_users_login 'Update_One', 'sysdba', 'sysdba'
3>go

[/code]

It gives me an incorrect syntax near 'sp_change...'  The syntax is correct.  I copied and pasted the code into the command prompt...

The saleslogix DB is using the correct sysdba account/password since I can connect to it. 

Any other suggestions or ideas?  We are very close...

Thanks for all your help thus far.

[DanCarvin]

You're almost there.  Connect to the DB as user sa or Windows authentication, THEN run the fixsysdba command.

[jjbingen]

No luck.  Recreated the connection and the same error message in UDL test file/login into admin....

Throught it should be insteresting to note.  If I leave the password field blank on the SLX admin login it says 'database login failed...'  If I enter in the 'password' or any text it says: 'database login faile: login failed for domain\username'  So it must be trying to connect to something?

Thanks.

Message Edited by jjbingen on 04-16-2010 02:12 PM

[jjbingen]

Any other methods of upgrading the data?  Maybe using the SSIC tool?

Any reasons how to fix the admin connection back to the SLX ?  Is this information stored in a table in SalesLogix DB?