[Article] Using MS SQL 2000 as the Repository

by Clint Roberts
BOSS Staffing

(Dave note: this was originally published on Servoy Magazine November of 2003. As such, some of the references are outdated. However, the concepts and many of the details are still relevant. As a Mac dude myself, if I ever had to deal with MS Server this is where I would start!)

The Servoy Repository is a central storage place for all Servoy security, methods, forms, objects, images, etc. It is the place where Servoy stores everything but the data for your solution.

One of the most challenging aspects of Servoy is rather simple to overcome with a quick rundown of what Servoy is.

Developers accustomed to Enterprise-class development with industry standard technologies will feel quite at home with many of the concepts of Servoy. Developers used to Workgroup-targeted development using proprietary scripting, file formats, database engines, etc.. may be confused at first.

Servoy is a Java-based rapid application development tool which gives developers an unprecedented ability to effortlessly connect to a single or multiple JDBC or ODBC sources and quickly design solutions using schema, business logic, and forms. Unlike development environments such as VisualBASIC, REALbasic, 4D, Omnis, FileMaker Pro, Servoy stores all business logic, forms, and other types of objects in a database called, "The Repository."

No special executable file or schema-layout combination file is generated. None is needed. Servoy leverages the power of JDBC and ODBC compliant Enterprise-class relational database management systems to store not only schema, as expected, but also all security, methods, forms, objects (buttons, images, field placement info, etc.). The result is a very portable solution that can run on any operating system supporting Java 2 technology from Sun Microsystems.

Servoy ships with the Firebird relational database server to contain the default schema, etc. and data if desired. On installation, the Repository exists by default in the Firebird server. However, any JDBC or ODBC compliant database server may be used to store the Repository. Thus, you can delete the Repository existing on the Firebird server and have it recreated on a database server that you have already deployed or feel more comfortable with.

Note that the steps that follow are conceptually similar for many Enterprise-class database servers. And, note that Servoy Server and the Repository do NOT have to exist on the same machine. Using an Enterprise-class database server as the repository does not mean that you must install Servoy Server on it. In fact, it is best not to in order help in distributing server load.

In short, your Servoy Repository can exist on a Sybase, Oracle, MS-SQL, MySQL, Postgres, OpenBase, FrontBase, etc. server that you choose.

*Please Note: If you decide to delete the Repository from the Firebird server and you have existing solutions residing there, you will delete you solutions. It is important that you migrate any existing solutions BEFORE taking this action.

Creating a New Repository (Using MS-SQL Server 2000 SP 3a)

Step 1: Launch SQL Server Enterprise Manager.

Step 2: Expand Microsoft SQL Servers.

Step 3: Expand the Microsoft SQL Server you wish you administer.

Servoy requires five databases to run. You will need to create 5 new databases with the following names:

1. repository_server
2. log_server
3. migration_server
4. user_data
5. example_data

For purposes of this article, we will also create a database called crm to hold the default crm solution that ships with Servoy.

Step 4: Right+Click on Databases and choose to Create a New Database.

Step 5: In the General tab of the Database Properties window, enter the name of the database you wish to create.

Step 6: Click the Data Files tab of the Database Properties window. Ensure that you are satisfied with the File Properties for the Database.

Step 7: Click the Transaction Log tab of the Data Properties window. Ensure that you are satisfied with the File Properties for the log for this database.

Step 8: Click the OK button to create the database with the properties you defined.

Step 9: Repeat steps 4 through 7 until all six databases are created:

1. repository_server
2. log_server
3. migration_server
4. user_data
5. example_data
6. crm

After creating the 6 databases, your SQL Server Enterprise Manager database console will display the six new databases (as well as any others that this SQL Server is hosting).

The next step is to assign appropriate permissions to the databases you have created. Expand the Security folder in the Tree.

Step 10: Right+Click on the Logins folder and choose New Login.

Step 11: You may either choose to use Windows Authentication, or a SQL Server Authentication to access the databases. Using Windows Authentication will ensure that the user has passed both authentication with a Windows domain server and has appropriate access to Microsoft SQL Server. Using SQL Server Authentication bypasses Windows domain server authentication altogether, relying solely on authentication with Microsoft SQL Server.

Step 12: Click the Server Roles tab and assign the Server Role this user you have set up has to the database you have created. While the above image displays all Roles being checked, the System Administrators Role serves as the master role, and includes all permissions all other roles in the list includes.

Step 13: Click the Database Access tab. Choose to grant access to the six databases you created. Additionally, choose which role this user will have for the each Database. While the above image displays the user in multiple roles, the roles of "public" and "db_owner" are more than sufficient (perhaps even too much of a permission grant for deployment).

Step 14: Click the OK button to create the new Login and assign the appropriate Role and permissions across the six Databases you previously created.

Choosing MS-SQL Server 2000 as Your Repository

Before proceeding further, please ensure that any solutions hosted on your Firebird-based Repository have been backed up. If they have, let's continue.

The first step toward instructing Servoy to use MS-SQL 2000 as your Repository is to instruct it to no longer use Firebird as your repository.

Enter Preferences by selecting the Edit menu and choosing Preferences. In the Preferences window, select the DB Servers tab.

There are at least 5 servers listed:

1. example_data
2. log_server
3. migration_server
4. repository_server
5. user_data

You may have other DB Servers such as crm listed as well.

Choose to remove references to these servers one by one by:

1. Selecting the server
2. Clicking the remove button

After you have removed all references to the Servers, we must setup each of the five servers mentioned above.

Before doing so, it is important to close the Preferences window by clicking OK to accept the changes you have made.

Reenter Preferences and choose the DB Servers tab again.

Set up example_data by choosing Auto Setup Server from the pop-up menu and selecting MS SQL from the choices available.

In the Server config window that pops-up, enter the server name "example_data". Also enter the same User name and Password that you chose for the database when you set it up. In the Database Server URL, make sure to enter the appropriate IP address, port number and database name. 1433 is the default port number for MS-SQL Server.

Click OK to accept the DB Server.

Set up log_server by choosing Auto Setup Server from the pop-up menu and selecting MS SQL from the choices available.

In the Server config window that pops-up, enter the server name "log_server". Also enter the same User name and Password that you chose for the database when you set it up. In the Database Server URL, make sure to enter the appropriate IP address, port number and database name. 1433 is the default port number for MS-SQL Server.

Click OK to accept the DB Server.

Set up migration_server by choosing Auto Setup Server from the pop-up menu and selecting MS SQL from the choices available.

In the Server config window that pops-up, enter the server name "migration_server". Also enter the same User name and Password that you chose for the database when you set it up. In the Database Server URL, make sure to enter the appropriate IP address, port number and database name. 1433 is the default port number for MS-SQL Server.

Click OK to accept the DB Server.

Set up repository_server by choosing Auto Setup Server from the pop-up menu and selecting MS SQL from the choices available.

In the Server config window that pops-up, enter the server name "repository_server". Also enter the same User name and Password that you chose for the database when you set it up. In the Database Server URL, make sure to enter the appropriate IP address, port number and database name. 1433 is the default port number for MS-SQL Server.

Click OK to accept the DB Server.

Set up user_data by choosing Auto Setup Server from the pop-up menu and selecting MS SQL from the choices available.

In the Server config window that pops-up, enter the server name "user_data". Also enter the same User name and Password that you chose for the database when you set it up. In the Database Server URL, make sure to enter the appropriate IP address, port number and database name. 1433 is the default port number for MS-SQL Server.

Click OK to accept the DB Server.

Set up crm by choosing Auto Setup Server from the pop-up menu and selecting MS SQL from the choices available.

In the Server config window that pops-up, enter the server name "crm". Also enter the same User name and Password that you chose for the database when you set it up. In the Database Server URL, make sure to enter the appropriate IP address, port number and database name. 1433 is the default port number for MS-SQL Server.

Click OK to accept the DB Server.

Next, click the Advanced button.

Select the MS-SQL Server driver from the pop-up list
("com.microsoft.jdbc.sqlserver.SQLServerDriver" if you are using the MS-SQL Server 2000 SP1 JDBC driver supplied from Microsoft).

Change the datetime Type Option from "timestamp" to "datetime".

Click OK to accept the change. This will instruct Servoy to communicate with MS-SQL Server in a data-type compatible way when handling data types of datetime.

Click OK to close the Preferences dialog and accept the DB Servers you have setup. Also, quit and relaunch Servoy.

On relaunch, you will encounter an error stating, "Cannot start repository, it may not exist or is not up to date." Click OK to accept this message.

This error indicates that Servoy has not yet populated the databases you have instructed it to with tables, fields, and other data that it needs to run efficiently.

Choose Repository... from the File menu.

In the Repository window you will notice an entry called "root" in the Solutions tab. If everything were setup properly, you may see some solutions listed here.

Click the Check Repository button in the lower left-hand corner to instruct Servoy to check the DB Servers for the Repository.

When prompted to create or upgrade the Repository, choose OK.

Servoy will setup the necessary tables, fields, and populate them with the necessary data in order to run efficiently. Please be patient where this may take a moment.

When successfully setup, click the OK button to continue.

You will now notice "Repository" listed under the Solutions tab. Click the Import Solution button to import a solution.

Navigate to the examples folder. Double-click on the examples folder to enter it.

Choose the crm.servoy solution. Click the Open button to open the CRM solution.

Once successfully imported, click the OK tab to continue.

Click the OK tab to exit the Repository window.

Select Open in the File menu and choose to open the CRM solution.

A Quick Followup

Following setup of the Databases, and directing Servoy to use Microsoft SQL Server as the database backend for the Repository, visit SQL Server Enterprise Manager again. You will notice that Servoy has created all of the necessary Tables and Fields in each of the Databases.

Congratulations, MS SQL Server is now your Repository!