MS SQL Server Database Management

(1)

MS SQL Server Database Management

Creating a New MS SQL Database

... 2

Connecting to an Existing MS SQL Database

... 3

Migrating a GoPrint MS SQL Database

... 5

Troubleshooting

... 11

Published April 2011

GoPrint Systems

(2)

MS SQL Server 2005/2008 Database

Important: Currently GoPrint supports SQL Authentication

Two methods are available when creating a new database. The first method involves using the GoPrint Control Node to create a new MS SQL database. The second is connecting to an existing MS SQL Database.

The GoPrint Node Control Center is a web-based administrative feature used to create and manage database connections.

To create a new GoPrint database, from the server open a browser and enter:

https://localhost:7767

This will bring up the GoPrint Node Control Center: Click Configure Database

Step 1 – Create Database Make the necessary changes: ODBC Driver: Select JDBC: SQL Server (JTDS)

Server: enter hostname Database Name: goprint User ID: sa

Password: xxx123 Click: Create Database

Note: If using virtual SQL Server instances, add ';instance=xxx' to the Database setting where xxx is an instance name.

(3)

Step 2 - Start the GTX Services

To start the GTX service click Services then press the Start button

Connecting to an existing MS SQL Database

When it’s necessary to create and connect to a MS SQL database using some other SQL user account instead of the built-in sa account, then the following steps must be performed:

Step 1 - Create a new database in MS SQL Server

Step 2 – Create the SQL user account

1. Check SQL Server Authentication

2. Uncheck Enforce password expiration and User must change password at next login 3. Set the users Default database

(4)

Step 3 – Set the appropriate User Mapping

1. Map the user login to the goprint database

2. Grant the dbo_owner role for the user

Step 4 – Set the database connection Note: Since the database has previously been created in MS SQL Server the Create a Database option is skipped in favor of simply testing the connection. Make the necessary changes:

JDBC Driver: JDBC:SQL Server (jTDS) Server: Remote DB Server name Database Name: Goprint DB User ID: SQL username Password: Password Click Test Connection

(5)

Step 5 – Populate the Database Tables

At this point, it’s necessary to populate the empty database with the GoPrint tables. To do so, navigate to the Services section and start the GTX Service.

Doing so, will run the database creation scripts in the

background and therefore create the required tables.

Migrating a GoPrint database to a new SQL Server

Version: MS SQL Server 2005 -2008

Overview: The task involves backing up the existing database, moving it to the new server, then creating a new database and restoring the contents of the back up to the newly created database.

Step 1: Open Microsoft SQL Server Management Studio

Step 2 – Back up the existing database

(6)

The Back up Database Dialog box appears:

Select:

1 Backup type: Full 2 Destination:

Backup to: Disk

Click the “Add” button

Select the path and file name for the database back up file.

(7)

Step 3 - Create a new database on the new server using the SAME exact database name as used on the current server.

(8)

Press the Add button to locate the path to the back up file.

(9)

Important: it’s required to check: Overwrite the existing database checkbox

(10)

GoPrint Configuration

Overview: The task involves applying the necessary MS SQL Server hostname, updating corresponding SQL User ID’s and passwords, and restarting the GTX services.

Step 1 - From the GoPrint server, open a browser to: https://localhost:7767

Step 2 - Click on Configure database

Make the necessary changes: JDBC Driver: SQL SERVER (JTDS) Server: Enter the hostname of the new MS SQL server

Database Name: should be the same!

User ID: sa or goprint

Password: an updated password! Important: If a MS SQL user account other than the built-in sa account is being used, the account will need to be created and granted DBO rights to manage the GoPrint database.

Step 3 – Press Apply

Step 4 – Press Test Connection

(11)

Step 5 - Click on Services from the Node navigation menu

1. Stop the GTX service 2. Start the GTX service

Once the GTX service appears as running, press the GoPrint Control Center option to bring up the admin login page.

Once logged in, review the system configuration to ensure all items have been restored from back up.

Troubleshooting

Connection fails

1. Ensure the MS SQL server’s hostname is resolvable from the GoPrint server. 2. If login failure, check the sa password. If the sa account isn’t being used,

check to ensure the assigned SQL DBO account has been created and granted Rights to manage the GoPrint database.

3. If another port other than the default MS SQL port (1433) is used, then the port and Instance must be entered under the Node server path.

Example:

Servername;databaseName=goprintgs5;instanceName=GoPrint;<port>

4. A Firewall is blocking the MS SQL port either under the Windows Firewall or over the network or the port is in use by another application.

MS SQL Server Database Management