Moving Database to New Server 1

[impress]

Ok I installed a new sql server and moved the database over to the new server. I cant rename it to same as old ... what i need to do is point the Macola to the new sql server. How do I accomplish this?

 

[keain]

This is for Progression.

There are several things you will need to do. You did move over all the Macola databases, didn't you? Not only data_xx, but PWE, MSLLOCKDB (see below), and SCREENS, right?

First, you will need to edit the macsql.cfg in your Macola root directory and change each instance of the old server name to the new server name.

Then, edit the PWE.tam file (x:\macsql\pwe) to reflect the new server name. The file may be read-only, so check the attributes before you change.

Macola users aren't going to exist in the Master on the new database, so the first thing you'll want to do is run

sp_dropuser supervisor_sql in Query Analyzer against each Macola database. If this returns a message that user supervisor_sql does not exist in the database, that's fine, move on to the next db.

Once that completes, open the user7.sql script in Query Analyzer and run it. The script can be found in x:\macsql\sql scripts). It has a use screens statement, so it doesn't matter what db you run it against.

This step is optional, but I find it can be beneficial. Delete the msllockdb (or don't copy it over in the first place). Then, in Query Analyzer, run the Lockbd7.sql (x:\macsql\sql scripts) against the master database. Once that completes, run the msl_locking script in Query Analyzer against the newly created msllockdb.

At that point, you should be able to log into Macola. From there, you should go into System Manager\Maintain\Visual Menu Builder and run Tools\Synchronize with SQL (tools is on the menu bar).

If you had aliases set up in the client network utility pointing to the old server, either remove them or point them to the new server.

 

[kscheeler]

You also need to create two stored procedures in the MASTER database. Run the following scripts in query analyzer against the MASTER database to do that.

--------------------------------------------------------


CREATE PROCEDURE sp_MacMSSLockEraser
@dbname varchar(30),
@cur_spid smallint = NULL
AS
/* This Procedure will remove all orphaned records out of the
the MacLocks table of the specified database. This will be executed
each time a user launches a new app using the MacMSS.DLL
*/
DECLARE @lspid varchar(11)
SET @lspid = CAST(@cur_spid AS varchar(11))

BEGIN
PRINT @dbname
EXEC("delete from " + @dbname + "..MacLocks where SessionID not in
(select spid from master..sysprocesses where program_name = 'MacMSS')")
EXEC("delete from " + @dbname + "..MacLocks where SessionID =" + @lspid )
END

GO
-------------------------------------------------------

and then this one.

-------------------------------------------------------


/****** Object: Stored Procedure dbo.sp_MSLOpenTableEraser Script Date: 3/7/2001 9:38:28 AM ******/

CREATE PROCEDURE sp_MSLOpenTableEraser
@dbname varchar(30),
@cur_spid smallint = NULL
AS
/* This Procedure will remove all orphaned records out of the
the MacLocks table of the specified database. This will be executed
each time a user launches a new app using the MacMSS.DLL
*/
DECLARE @lspid varchar(11)
SET @lspid = CAST(@cur_spid AS varchar(11))

BEGIN
PRINT @dbname
EXEC('delete from ' + @dbname + '..MacOpenTables where SessionID not in
(select spid from master..sysprocesses where program_name = ''MacMSS'')')
EXEC('delete from ' + @dbname + '..MacOpenTables where SessionID =' + @lspid )
END


GO
----------------------------------------------------------

Also, if you've set up custom DSN's, you'll need to change the server that they point to as well.

You don't mention whether you're keeping the current drive letter that Macola's mapped to or using a new one. If you're going to change it, you need to edit the prosql7.cfg file in the \macsql folder and change the path to the msl folder.

If you have flexibility installed, you should "remove" Macola from the workstations and then reinstall both Macola and flexibility. Flex has pointers to the server name in the registry which has been mentioned in previous posts.
* Delete the registry key hklm\software\macola
* Delete the folder c:\program files\common files\macola shared
* Delete he file c:\windows\macola7.ini.
* Reboot
* Install Progression and then Flexibility.

Kevin Scheeler

 

[keain]

Running the two scripts Kevin mentioned is necessary only you port over the MSLLOCKDB. If you recreate it from scratch using lockdb7 and msl_locking it's not necessary to run the scripts.

 

[dgillz]

Also take note that the mapped drive letter is stored in the company file. So if your mapped macola drive was M bfore, it needs to still be M on the new server, or else you need to change the company file.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[keain]

I guess one question we should have asked is, how many servers do you have for Macola? Do you have one server where the Macsql dir and the databases are on the same server, or two servers, where the Macsql dir is on one server and the dbs are on a second?

If it's two servers, you don't need to worry about the drive mapping if the only server you changed was the db server. If it's one server, or two servers and you're replacing both servers, then you need to worry about the drive mapping and prosql7.cfg.

 

[impress]

Its on a file server and sql server is on a different server. I followed the directions you guys provided on a test env.... It all worked perfectly Thank You!

 

[kscheeler]

keain,

I should have read your thread more thoroughly and I would have realized your solution would have created the sp's in the MASTER database.

Kevin Scheeler