CONFIGURING SQL SERVER FOR METRIX

(1)

Rules Based Productivity

SQL SERVER

(2)

Configuring SQL Server for Metrix Metrix SQL Server Guide 11-22-2013 • 4:33 PM

Copyright and Contact Information

Metrix® SQL Server Guide (version 2013.0)

Documentation:

Developers: Lisa Chambers, Michael R. Saia

Company, brand, or product names are for identification purposes only and may be registered trademarks of their respective holders.

Metrix software:

Includes JavaMail v 1.4.1, source can be found at:

http://www.bouncycastle.org

w3.efi.com/about-efi/contact-us or Email us at:

[email protected] or Call us at:

(3)

Configuring SQL Server for Metrix

Metrix SQL Server Guide 11-22-2013 • 4:33 PM

Preface

This Guide is provided in order to share the knowledge gained through Testing and Quality Assurance in migrating Metrix data and connecting to Microsoft SQL Server. However, EFI does not support the SQL Server itself. Microsoft SQL Server is a very com-plex product, and requires the services of a knowledgeable Database Administrator to manage and maintain it. To illustrate this point, here is a small portion of the Certification Test for Microsoft SQL Server 2008:

(5)

Installing and Configuring MS-SQL Server 2008

¨

See the Microsoft website at http://technet.microsoft.com/en-us/library/ms143506.aspx

for SQL Server System Requirements.

1

The general Installation options you select are dependent upon how your company intends to use SQL Server. The critical installation option that enables SQL Server 2008 to

connect with Metrix is the Database Engine Configuration. You must choose Mixed

Mode Authentication to enable SQL Server to communicate with Metrix.

Enter a password for the SQL Server System Administrator (sa) Account and confirm it. Write down the password, as you will need to enter it later when you edit the

(6)

(7)

(8)

(9)

5

In the Configuration Manager select SQL Server Network Configuration, and then

Protocols.

6

Right click on TCP/IP, and then left click on Enable. You will be warned that the changes you are making will be saved, but will not take effect until the service is restarted. Click on

(10)

(11)

8

Click on the IP Addresses tab and scroll to the bottom of the window. Under IPAII, type

1433 in the field to the right of the TCP Port option.

9

Now click on Apply, and you will be warned that the changes you are making will be saved, but will not take effect until the service is restarted. Click on to close the warning box, then click on to close the TC/IP Properties windows.

(12)

Configuring Windows 7 Firewall Settings

¨

Instructions for setting Windows XP Pro firewall setting follow these instructions. Other firewall software will require similar settings. If you need help with the firewall settings in other versions of Windows, please contact Microsoft.

Metrix communicates with SQL Server via port 1433. If this port is being blocked by a firewall on the SQL Server, then Metrix will fail and report that it can’t find the SQL Server. Follow these steps to change the settings in the built-in firewall of Windows 7.

1

Go to Start > Control Panel > Windows Firewall and open the Windows Firewall dialog.

(13)

3

In the left-hand column click on Inbound Rules.

4

In the right-hand column click on New Rule. This will begin the New Inbound Rule

(14)

(15)

(16)

(17)

(18)

(19)

Configuring Windows XP Pro Firewall Settings

Metrix communicates with SQL Server via port 1433. If this port is being blocked by a firewall on the SQL Server, then Metrix will fail and report that it can’t find the SQL Server. Follow these steps to change the settings in the built-in firewall of Windows XP. Other firewall software will require similar settings.

1

Go to Settings > Control Panel and open the Windows Firewall.

If the firewall is set to Off, then the following steps will not be necessary as the firewall is completely disabled.

(20)

(21)

Creating an Empty Metrix Database on the SQL

Server – WITHOUT Data Migration

Follow these steps ONLY if you are installing Metrix for the first time and do not have any data to migrate. The procedure below will create an empty Metrix database in SQL Server 2005 or SQL Server 2008.

1

Launch the Run command from the Windows Start menu.

2

Type sqlcmd -E and press ENTER.

¨

Make sure to enter a space before the -E and to capitalize the E. The CMD program will drop down a line and insert 1>.

3

Type create database metrix and press ENTER. The CMD program will drop down another line and insert 2>.

4

Type go and press ENTER. When the CMD program drops down a line again and brings up another 1> type quit and press ENTER.

(22)

Connecting Metrix to SQL Server WITHOUT Data

Migration

1

Supplied with this document is a file called

Connect_to_Empty_SQLServer_Database.txt.

Open it with a text editor such as Notepad or Wordpad and copy the 4 lines in the file. They are:

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc.URL=jdbc:sqlserver://YOUR IP:1433;DatabaseName=metrix;responseBuffering=adaptive;

jdbc.user=sa

jdbc.password=YOUR PASSWORD

(23)

3

Paste the 4 lines at the bottom of the file. Replace YOUR IP in line 2 with the IP address of your SQL Server computer, and replace YOUR PASSWORD with the password you used when installing the SQL Server.

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc.URL=jdbc:sqlserver://YOUR IP:1433;DatabaseName=metrix;responseBuffering=adaptive;

jdbc.user=sa

jdbc.password=YOUR PASSWORD

4

Now add a # symbol to the first 4 lines that begin with jdbc – this will comment out the lines referring to the old Metrix database and effectively disable them.

# jdbc.driver=org.hsqldb.jdbcDriver

# jdbc.URL=jdbc:hsqldb:hsql://YOUR IP:9939

# jdbc.user=sa # jdbc.password=

(24)

6

Save the metrix.properties file and close it.

7

Launch the License Server and a Metrix client. There is no need to ever launch the Metrix Data Server again – Metrix will now always connect to the SQL Server. You can now remove the Start Metrix Database Server and Stop Metrix Database Server icons from the desktop since they will no longer be necessary.

All necessary changes are now complete. The procedures for launching and quitting the Metrix License Server are unaffected and remain the same.

(25)

Connecting Metrix to SQL Server and Migrating

Data from a Windows computer

1

In order to migrate the data from the Metrix database to the SQL Server database, you will need 2Gb of memory on the Metrix Data Server computer. Ensure your Metrix Data Server meets the minimum system requirements to perform the migration.

2

Quit any Metrix clients and run Stop Metrix Database Server.

3

Connect_to_SQLServer_Database_and_Migrate_Metrix_Data.txt.

Open it with a text editor such as Notepad or Wordpad and copy the 4 lines in the file. They are:

jdbc.driver2=com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc.URL2=jdbc:sqlserver://YOUR IP:1433;DatabaseName=metrix;responseBuffering=adaptive;

jdbc.user2=sa

jdbc.password2=YOUR PASSWORD

4

In order to connect Metrix to the SQL Server database and migrate your existing data, you need to copy the 4 lines into the metrix.properties file, found in the Metrix folder at the root level. Using a text editor such as Notepad or Wordpad, open the

(26)

5

jdbc.user2=sa

Your metrix.properties file should now look like this, except that it will contain your IP address and your password:

(27)

8

Open the Metrix folder and double click on the Start_DbMigrator.bat file. It will display a long list of procedures as it performs the migration. When the process is completed, it will report SUCCESS. The data has now been migrated from the existing Metrix Data Server to the SQL Server database.

9

Stop or Shutdown the Metrix Data Server.

10

Using a text editor such as Notepad or Wordpad, open the metrix.properties file again. Add a # symbol to the first 4 lines that begin with jdbc – this will comment out the lines referring to the old Metrix database and effectively disable them.

(28)

11

Remove the 2 just before the = in the remaining 4 lines that begin with jdbc (the ones you copied and pasted into this file earlier). The metrix.properties file should now appear similar to this, except that it will contain your IP address and your password:

12

13

Launch the License Server and a Metrix client to make sure you can access your project data. There is no need to ever launch the Metrix Data Server again – Metrix will now always connect to the SQL Server database. You can now remove the Start Metrix

Database Server and Stop Metrix Database Server icons from the desktop since they

will no longer be necessary.

(29)

Connecting Metrix to SQL Server and Migrating

Data from a Mac computer

1

In order to migrate the data from the Metrix database to the SQL Server database, you will need 2Gb of memory on the Metrix Data Server computer. Ensure your Metrix Data Server meets the minimum system requirements to perform the migration.

2

Quit all Metrix clients and run Stop Metrix Database Server (Mac) or Shutdown Metrix

Database Server (Windows).

3

Connect_to_SQLServer_Database_and_Migrate_Metrix_Data.txt.

Open it with a text editor such as TextEdit and copy the 4 lines in the file. They are:

jdbc.user2=sa

4

In order to connect Metrix to the SQL Server database and migrate your existing data, you need to copy the 4 lines into the metrix.properties file, found in the Metrix folder in your Applications folder. Using a text editor such as TextEdit, open the

(30)

5

jdbc.user2=sa

Your metrix.properties file should now look like this, except that it will contain your IP address and your password:

6

7

Start the Metrix Data Server.

(31)

9

To change the directory to the Metrix folder, type cd followed by a space. Find the Metrix folder in the Applications folder, drag it into the Terminal window, and press ENTER. This will enter the correct path into the Terminal application.

10

Type sh DbMigrator.sh and press ENTER, which will start the migration process.

(32)

11

Run Stop Metrix Database Server (Mac) or Shutdown Metrix Database Server (Windows).

12

Using a text editor such as TextEdit, open the metrix.properties file again. Add a

# symbol to the first 4 lines that begin with jdbc – this will comment out the lines

referring to the old Metrix database and effectively disable them.

# jdbc.user=sa # jdbc.password=

13

Remove the 2 just before the = in the remaining 4 lines that begin with jdbc (the ones you copied and pasted into this file earlier). The metrix.properties file should now appear similar to this, except that it will contain your IP address and your password::

14

(33)

always connect to the SQL Server database. You can now remove the Start Metrix

Database Server and Stop Metrix Database Server icons from the desktop since they

will no longer be necessary.

(34)

CONFIGURING SQL SERVER FOR METRIX

Rules Based Productivity

SQL SERVER

Copyright and Contact Information

Contents

Preface

Installing and Configuring MS-SQL Server 2008

¨

1

5

6

8

9

Configuring Windows 7 Firewall Settings

¨

1

3

4

Configuring Windows XP Pro Firewall Settings

1

Creating an Empty Metrix Database on the SQL

Server – WITHOUT Data Migration

1

2

¨

3

4

Connecting Metrix to SQL Server WITHOUT Data

Migration

1

3

4

6

7

Connecting Metrix to SQL Server and Migrating

Data from a Windows computer

1

2

3

4

5

8

9

10

11

12

13

Connecting Metrix to SQL Server and Migrating

Data from a Mac computer

1

2

3

4

5

6

7

9

10

11

12

13

14

Database Backups