How To Upgrade a local MSDE database to an SQL Server

(1)

How To – Upgrade a local MSDE database

to an SQL Server

Version no: 1.0

Stand: July 7, 2005

(2)

Version:

1.0 Date:

July 7, 2005

First Draw-up Date:

July 7, 2005

Author:

Thomas Lemmer

Version:

Revision date:

Revision history:

Technologies concerned:

Reviewer:

Marja Pals

File name:

HOW TO - Upgrade a local MSDE database to an SQL Server.doc

Index:

1. Upgrade from a local MSDE database to an SQL Server

2. Advantages of SQL Server Instances

(3)

© NT-ware If you don’t have the password for the SQL Server connection during the installation ready to hand, the

administrator has to create the new database in the following way.

Also those steps are required, if you want to upgrade from a local MSDE database to an SQL Server. - Start the SQL Server Client Network Utility with Start/Run…/cliconfg [Enter]

- Activate the TCP/IP-protocol as the only enabled protocol

- Confirm with OK

Load the following script under

http://www.nt -ware.com/mom/sql/momdb.sql

Open the command shell and navigate to the installation folder of uniFLOW (in general: c:\ProgramFiles\uniFLOW\MSDE).

Execute the following command:

osql –S <SQL-Servername> -U <loginname(sa-Account)> -I momdb.sql This command will create the database.

Test the proper function of the database with the following commands: - Open a command shell

- Type: osql –S <SQL-Servername> -U PbaIp –P ntwsqlpwd [Enter] - Type: use DsPcDb [Enter]

- Type: go [Enter]

With those commands, you should be able to connect to the database.

To enable uniFLOW to access the database via ODBC link, the following steps have to be performed: - Open Start/All Programs/Administrative Tools/Data Sources (ODBC)

- Choose the tab ‘System DSN’

(4)

- Choose the SQL Server on which the uniFLOW database has been installed.

- In the next window, choose: ‘With SQL Server authentication using a login ID and password entered by the user.’ and of course the Login ID and the Password. Now click on Next.

(5)

© NT-ware - After a successful test, you’ll get the following message:

To prepare the websites for the connection to the SQL Server, one has to change some registry entries. Therefore open the registry and navigate to the key:

HKEY-local_Machine\Software\NT-ware \Mom

Change the value with the entry ‘Connectionstring’ in the following way: Before:

Provider=SQLOLEDB.1;Persist Security Info=True;User ID=pbaip;Password=ntwsqlpwd;Initial Catalog=DsPcDb;Data Source=(local);Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096

After:

Provider=SQLOLEDB.1;Persist Security Info=True;User ID=pbaip;Password=ntwsqlpwd;Initial Catalog=DsPcDb;Data Source=<SQL-Servername>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096

(6)

[In our example the name of the SQL Server is canoflow with the instance uniFLOW. (Please have a look at §2 Advantages of SQL Server Instances)]

Navigate to the key:

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MomSvc And delete the Value data of DependOnService

If the key also exists under:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MomSvc and

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet00X\Services\MomSvc it also should be deleted.

For the website access to the database, the following ‘global.asa’ files have to be modified. Therefore navigate via Explorer to the folder:

C:\Program Files\uniFLOW\WebClient

- Mark the file ‘global.asa’ and delete the attribute ‘Read-only’munder properties. - Open the file with an editor (i.e. notepad).

- Change the string ‘local’ with the name of the SQL Server. (Note: If you should use special characters with the server name like \ or }, which is created in general with a SQL instance, the special character will be identified only with a prefixed \ (backslash).

- After changing, please set the file back to ‘Read-only’. - Please also proceed in the same way with the following files: C:\Program Files\uniFLOW\WebQueueMan

C:\Program Files\uniFLOW\WebReleaseMan C:\Program Files\uniFLOW\WebServer

(7)

The biggest advantage is the simultaneous usage of multiple independent SQL Servers which can be installed with just one License. A maximum of 16 instances per SQL Server license is possible. All instances need to be installed one by one with the setup routine. Therefore the installation CD is required.

Note: There is only one standard instance per SQL Server and Computer possible. Therefore you can use an unchanged standard installation of SQL Server version 6.5 or 7.0 or an installation of SQL Server 2000, which has been installed as standard instance. In all cases the standard instance uses the same service names, registration structures, network monitor points and other standard settings, which are used in SQL server 7.0.

If you install a standard instance of SQL server, the service names remain MSSQLServer and SQLServerAgent. If you install a named instance of SQL server, the service names are changed as follows:

- MSSQL$InstanceName for the MSSQLServer-service. - SQLAgent$InstanceName for the SQLServerAgent -service.

The services Microsoft Distributed Transaction Coordinator (MS DTC) and Microsoft Search will be installed only once and can be used at the same time by all installed instances of SQL Server.

Standard instance of SQL Server:

This instance is identified by the network name of the computer on which it is running. Applications, which use client software of earlier versions of SQL Server, can make a connection to a standard instance.

Servers with SQL Server version 6.5 or SQL server version 7.0 can be used as standard instances.

Named instance of SQL Server:

This instance is identified by the network name of the computer and an instance name in the format <

computername>\<instancename >. Applications must use SQL Server 2000 client component, in order to make a connection to a named instance.

A computer can run as many as desired named instances of SQL Server at the same time.

A named instance can be implemented at the same time with an existing installation by SQL Server version 6.5 or SQL Server version 7.0. The instance name may not be longer than 16 characters.

A new instance name must begin with a character, an ampersand (&) or an underscore (_) and can contain letters, numbers and other characters. SQL Server system names and reserved names may not be used as instance names. The term "default" may not be used as instance name, because it is a reserved name of the setup program. For more information about the SQL Server and its instances, please refer to the SQL Server Online documentation which is delivered and installed with each SQL Server installation.