Microsoft SQL Server is a system for managing relational databases (DBMS), developed by the Microsoft Corporation. The main query language used is Transact-SQL, created by Microsoft and Sybase together. Transact-SQL is an ANSI/ISO-standard realization of a Structured Query Language (SQL), with some expansions. It is used for small and medium-sized databases, and in the last five years has started to be used for large databases on an enterprise scale, competing with other DBMS in this segment of the market.
Microsoft SQL Server Express is a freely distributed version of SQL Server. This version has several technical limitations, making it unsuitable for building large databases. However, it is perfectly suited for running software on a small-company scale.
Microsoft SQL Desktop Edition is another freely distributed version of SQL Server. This version is designed for small user databases. This DBMS is not recommended for storing large volumes of data.
Configuring Microsoft SQL Server
Installation and customization of the Microsoft SQL Server must be carried out by the administrator themselves.
All the operations described in this article were carried out under Microsoft SQL Server 2005 with the help of the freely distributed application for database management Microsoft SQL Server Management Studio Express. Settings for other versions of DBMS (SQL Server 2000, SQL Server 2008) are analogous to those presented here and do not differ significantly.
Microsoft SQL Server Management Studio Express can be downloaded from the official Microsoft website:
http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8- 5A0F62BF7796&displaylang=en
Before you start, you should enable the remote connection to the database server. For this, you will need to use the SQL Server Configuration Manager application.
You can find SQL Server Configuration Manager here:
Start->Programs->Microsoft SQL Server 2005->Configuration Tools-> SQL Server Configuration Manager In the open window, go to the Protocols for MSSQLSERVER tab (Figure 1).
By default, the Named Pipes and TCP/IP protocols are disabled; therefore you will need to enable them by right-clicking and selecting Enable from the menu that appears.
62
Figure 1.
Next, start «Microsoft SQL Server Management Studio Express» and connect to the database server, using the special icon in the top left corner. In the window that appears, select the database server you need to connect to, and choose the type of authorization. It is recommended that you use Windows authentication, as this method of authentication is more reliable than the built-in Microsoft SQL Server. (Figure 2)
63
Figure 2.
Figure 3 shows the main program window. In the left-hand part of the window there is an object browser, in which a list is displayed of all the servers the program is connected to at that moment. The Databases container shows a list of databases located on the server.
To create a new database, right click the Databases container and select «New Database…».
Figure 3.
In the window that appears, you will need to set the name of the database being created, as shown in Figure 4. In this window you can also set the name of the database owner and the path of the database files, and configure the function to automatically increase the size of the database file.
64
Figure 4.
On the «Options» tab, you will need to select the sort parameters. It is recommended that you use as a sort parameter SQL_Latin1_General_CP1_CL_AS, as shown in Figure 5.
65
Figure 5.
Once you have set all the required parameters, click OK to start creating the database. If errors arise during the process of creating the database, the program will bring up a message with a detailed description. To fix any errors that arise, you must follow the directions given in these messages.
If the database is created successfully, a new database icon will appear in the Databases container, as shown in Figure 6. The actions described above are completely sufficient for the SurfCop to operate normally with the new database, although you may set additional parameters if you wish.
66
Figure 6.
The procedure for creating a new Microsoft SQL Server database is now complete.
In case you cannot use Windows Authentication for some reason, you can enable integrated authorization of Microsoft SQL Server.
To use integrated authorization of Microsoft SQL Server you simply enable the «SQL Server and Windows Authentication mode» option, as depicted in Figure 7.
67
Figure 7.
Data Center Configuration
Once the new database is created, Data Center should be set up in order to work with it. For this, you must open the program’s management console, go to the «Configuration» page shown in Figure 8, and select «Data Storage Settings».
68
Figure 8.
69
Figure 9.
On the second tab, specify the parameters for connecting to the database (Figure 10).
To avoid the need to enter the connection string manually, the program has a special tool, to use which you simply click the button by the connection string field.
70
Figure 10.
The dialog that opens, shown in Figure 11, allows you to select the type of OLE DB provider, through which the connection should be set up.
71
Figure 11.
On the second tab, you will need to enter the connection parameters, as shown in Figure 12, where,
WIN2003 – name of MS SQL server
Activity – name of database created earlier, to which the connection will be made
Warning! To link to the database server, it is recommended that you use the Windows tools for authorization.
If MS SQL tools are used for authorization, the login and password of a user with the necessary privileges to work with the database must be entered in the Username and Password fields, and the «Allow saving password» option must be enabled.
Blank password – means that a blank password is used to access the database (you can protect the database with a password if you wish; in this case, this option should be disabled).
72
Figure 12.
To test whether the connection parameters are correct, click «Test Connection».
Once you have pressed «Test Connection», the program will attempt to connect to the database, and if the connection settings have been specified correctly, the message shown in Figure 13 will appear.
Figure 13.
Next, click «OK».
Once you have entered all the required parameters, a connection string will be generated automatically, as shown in Figure 14. The contents of the string may change, depending upon the information entered earlier.
73
Figure 14.
After you click «Test Connection», the program will attempt to connect to the database, and if the connection settings have been specified correctly, the message shown in Figure 15 will appear.
Figure 15.
When the connection testing is complete, click «OK».
A message will appear notifying you that Data Center must be restarted for the new parameters to be applied (Figure 16).
74
Figure 16.
Click «Yes», and Data Center will restart automatically. Once Data Center is restarted, all changes will take effect and the program will begin using the new database to store information.
The status of the database can be viewed on the «Statistics» tab of the data storage management dialog (Figure 17).
Figure 17.
Conclusion:
Microsoft SQL Server is a reliable and relatively straightforward DBMS. Microsoft SQL Server DBMS is recommended for use in medium and large-sized companies.
75
MySQL
MySQL is a free database management system. It is characterized by high speed, stability, and ease of use. It is the ideal solution for small and medium-sized companies.
Configuring MySQL
Installation and configuration of the MySQL server should be carried out by the administrator themselves.
All the operations described in this article to set up MySQL were carried out with the help of free web application phpMyAdmin.
Official phpMyAdmin website:
http://www.phpmyadmin.net/home_page/index.php
Detailed instructions on how to install and configure this application can be found on the website. To start phpMyAdmin , open your browser and enter the following address in the address bar: http://computer_name/Tools/phpMyAdmin/
where,
computer_name – is the name of the computer on which phpMyAdmin is installed. In the browser window, the main page will load, shown in Figure 1.
76
Figure 1.
First of all, you need to create a database. For this, you must choose a name for the database and enter it on the main page, select a method for the lines to be sorted and click Create.
The database creation form is shown in Figure 2. Activity - name of database being created utf8_unicode_ci - method for sorting lines
It is recommended that you choose a simple and intuitive name for the database, for example, «Activity».
77
Figure 2.
On selecting Create a message will appear, notifying you that the database was created successfully.
Figure 3.
The next step is to create a database user and assign them the privilege to manage the database.
To go to the edit users and privileges pages, you will need to return to the main page and select «Privileges».
78
Figure 4.
On the page that opens, a list of existing users will appear (Figure 5). The properties of any of these users can be edited as you like, but we recommend you create a new user to manage the database you have just created.
Creating a new user may increase the security of the database significantly, and also make accessing it simpler.
On the page that is open, select the «Add a new user» link.
Figure 5.
On the form that follows, you must choose and enter a username, the IP address of the computer from which you will be connecting, and a password.
79
Figure 6.
The following should be taken into account:
Data Center and the MySQL server may be installed on different computers.
In the Host field you must specify the IP address of the computer on which Data Center is installed. It is from this computer that connections to the MySQL server will be initialized.
If the connection to the database will be made from a local computer, you may enter «localhost» in the Host field.
If you need to allow access to the database to all users from a given computer, simply specify «Any user» as the «Username» value and give the IP address of the computer in the «Host» field.
The user password may be left blank, but we strongly recommend for security reasons NOT to use a blank password.
Besides completing the fields mentioned above, on this page you must select the privileges to be assigned to the user you are creating. We recommend you select all privileges by clicking «Check All». Once you have completed the form, click GO.
80
A message will appear, notifying you that the SQL request to create a user was completed successfully.
Figure 7.
Data Center Configuration
Once the new database is created, Data Center should be set up in order to work with it. For this, you must open the program’s management console, go to the «Configuration» page shown in Figure 8, and select «Data Storage Settings».
Figure 8.
81
Figure 9.
82
Figure 10.
Here:
192.168.0.166 – IP address of MySQL server
3306 – port through which the connection will be made (3306 used by default) User1 – name of the user created earlier with the help of phpMyAdmin
Password – password of the user created earlier with the help of phpMyAdmin Activity – name of the database created earlier, to which the connection will be made To test that the parameters are correct, click «Test Connection».
Once you click «Test Connection», the program will attempt to connect to the database, and if the connection settings have been specified correctly, the message shown in Figure 11 will appear.
83
Next, click «OK».
A message will appear notifying you that Data Center must be restarted for the new parameters to be applied (Figure 12).
Figure 12.
Click «Yes», and Data Center will restart automatically. Once Data Center is restarted, all changes will take effect and the program will begin using the new database to store information.
The status of the database can be viewed on the «Statistics» tab of the data storage management dialog (Figure 13).
Figure 13. Conclusion:
84
it is free of charge.
the server is very simple to set up.
setting up the program to store information in the MySQL DBMS may significantly speed up the process of writing and accessing data, and also increase reliability.