How to move a r7 / r8 mart from one SQL Server to another?

I am upgrading my Model Manager and that resides on SQL Server 2005/2008. I am first testing my upgrade by exporting my mart to a different SQL Server database.

Solution:

There is more to exporting a r7/r8 mart than simply taking a backup of the original database and then restoring that on another.  There are additional factors that must also be taken into consideration or else the new mart database will not work.  We list these factors A - H below.  The below processes will require someone in your organization who has DBA experience.  Please involve your DBA in these processes.

A.  Prerequisites/privileges must be in place on the destination database for the mart.  That is so the Security Manager synchronization procedure (m7_User_SynchronizeAll) works properly.  For the details on that, please see TEC446998 What permissions/prerequisites are required to use Model Manager on SQL Server 2005 and later? What privileges are needed for a user to load ERwin models into libraries without database owner privileges?  

B.  Make sure the dbo user is a Model Manager Administrator.

Make sure there is a user in the Model Manager Security that is the dbo user (the database owner, not the db_owner database role). That user must have the Model Manager Administrator profile. That is because that is the user account that will be used to log into the database when later upgrading the Model Manager mart to a more current version.

C.  Adjust the restored backup.

After restoring the backup on the destination SQL Server 2005/2008 database, you will need to make the following adjustments:

1.  Make sure the database name in the m7_Repository_Status stored procedure matches the current database name. The name of the Model Manager database is hardcoded into that stored procedure.  For example, if your source database is 'r8208mart' and your destination database name is 'prod' and you edit the m7_Repository_Status stored procedure you may find,

SELECT @updated = CreationDate

FROM r8208mart..m7Master

So you need to change the database name to instead to be,

SELECT @updated = CreationDate

FROM prod..m7Master

2.  Make sure the name of the new database appears in the RepositoryName column of the m7Master table.  For the SQL example below, we assume the destination database name = prod.

update m7Master
set RepositoryName = 'prod'
go

D.  Create users on the destination database. The DBA will need to create the same named database users on the destination database as existed on the source database.

E.  Licensing. The user who is performing the upgrade will need to install the new Model Manager software on a PC that has the Model Manager license on it (see your ca.olf file; that should have a line in it that has keyword '3FM2' with a future date indicating its expiration is in the future). Then, run the Model Manager software, connect to the destination database as the dbo user (the database owner, not the db_owner database role), and then perform the Model Manager Update.

F.  Post Load Conversion process.
Each of the models in the mart must be opened using the new version of ERwin and saved back to the mart one-time only. That will fully convert the models to the new version of the ERwin software. You don't have to do that for all the models if you don't want to. Just the models you are interested in testing or working with right away.

G.  If you are working with Model Manager r8.x, please make sure the database name in the m7_SerialNumber procedure is the same as the new database name. The name of the Model Manager database is hardcoded into that stored procedure. This step only applies to Model Manager versions r8 and above.  For example, we assume the destination database name = prod.  Your m7_SerialNumber procedure may show,

SELECT @serial_number = SerialNumber

FROM r8208mart..m7Master

And that would need to be changed to instead be,

SELECT @serial_number = SerialNumber

FROM prod..m7Master

H.  Here we present two potential 'GOTCHA's' that you may run into, and how to correct them:

Gotcha 1:  You do not see any users except sa in the User box in the Model Manager Security Manager.

1.  Create the users using the default database instead of the restored database (e.g. master).

2.  Then use the sp_change_users_login stored procedure to reassociate the logins:

sp_change_users_login 'auto_fix', 'login_name'

Where you would substitute login_name with the name of the user.

For further information, please refer to this article on recovering orphaned users when moving a database from one server to another:  http://www.codeproject.com/database/OrphanedSQLUsers.asp

Also, Microsoft Article ID : 168001 User logons and permissions on a database may be incorrect after the database is restored http://support.microsoft.com/kb/168001/

Gotcha 2.  The Locking Options in the Open Model Manager model dialog is grayed out and unavailable for some users for all libraries.

Solution: The user names in the m7User table (ServerName field) had trailing spaces. That caused Model Manager not to match up when performing name comparisons while loading user specific information during connects. The following query can be run to verify the problem:

      SELECT UserId, DataLength(ServerName), ServerName From m7User

To resolve the problem (make sure you have a database backup) execute the following query from your SQL query tool:

      Update m7User Set ServerName = LTrim(RTrim(ServerName))