05-17-2012 12:14 PM
We're moving our server when we upgrade to 7.4. We're also upgrading to SQL 2008.
Is there an official process to complete this operation? I've done the whole restore process lots of times, but I am concerned with keeping our scheduled jobs and user accounts in tact.
Is it OK to move the System databases from 2005 to 2008?
I found an article from Microsoft on the topic, http://support.microsoft.com/kb/918992 but I am not sure if Sage has any specific requirements or cautions.
05-17-2012 01:17 PM - edited 05-17-2012 02:59 PM
I am not sure about moving system databases from your 2005 box to your 2008R2 box. I don't know if it can be done safely or not. Google would probably find the answer though. With regard to Sage 500, I'm not quite clear as to why you would want to do it. As you probably know, your user accounts will be created on the new server the first time you sync the Sage 500 database on it. The few jobs that the Sage 500 DB Tools install on the SQL Server will be automatically created on the new server as well.
When I reformatted our server taking it from W2003R2/SQL2005 to 2008R2 I manually recreated all of my maintenance and backup jobs. There is probably an easy way to export/import them, and it might be okay to copy your system databases over, but I prefer to avoid that sort of thing if I can. I would rather create everything fresh on a new installation when that's an option. It's time consuming, but I have a greater comfort-level with that approach.
EDIT: I see now that you don't indicate that you are actually moving to a new physical box, so my information may not be relevant. As for doing an in-place SQL upgrade, I did it when we went from 2000 to 2005, but not from 2005 to 2008R2 (reformatted the box for that).
05-17-2012 02:04 PM
I just did the sync like Ralph. I wasn't interested in moving the system databases. So I just scripted out the sql jobs and put them on the new server. I think all but 1 worked doing it that way. It was also an opportunity to tighten security a bit.
05-18-2012 09:37 AM
If you are using Data Import Manager take a look at the installation guide(Install.pdf), see Data Import Manager Database Upgrade and Relocation section in Appendix B. The SSIS packages created in the MSDB may need to be recreated on the new instance of SQL Server. In addition, you may need to reconfigure the MAS 500 Credential.
05-22-2012 11:53 AM
Thanks for the replies!
I forgot the sync utility recreates all of the users, so I've gone with the suggestions of running that and then just recreating the SQL jobs. We don't have many, so it makes more sense to recreate.
06-28-2012 09:24 AM
We're in the process of testing our upgrade from SQL 2005 to SQL 2008 R2 and from 7.3 March 2011 to 7.4 March 2012.
During the last upgrade we relied on the SQL object modified date to know which SQL objects were affected by the upgrade so we could compare to our list of customized objects.
This time, however, the modified date was affected on over 90% of the SQL objects. We spot checked a few and determined that nothing in the SQL object was changed, yet the modified update was affected.
Has anyone else noticed this during the upgrade from 7.3 to 7.4. The only thing I can think is that Sage is dropping and re-creating objects unnecessarily.
06-28-2012 09:38 AM
There is a text file StrtChng.txt in C:\Program Files\Sage Software\Sage MAS 500 Database Utilities\DBUpgrade\7.40. it has a list of tables that are changed from 7.3 to 7.4.
Unless the table structure has changed, the database upgrade process will not drop/re-create the table.
06-28-2012 09:49 AM
06-28-2012 10:05 AM
We use a database compare tool to guarantee that we know all the changes that occur from one version to the next. If we have known customizations in the db, we will be able to focus on those objects.
There are two compare tools that I am familiar with:
They are pretty straigforward to use. We take a native install with SOA data and then do the compare. Don't really care about the data just the schema.