SQL Server Installation

Installation of SQL

Prior to making any system changes, it is imperative that a complete system backup of the Application database be completed and moved to a secure location.  The steps provided in the section  ‘Installation of SQL’ are suggestions provided by Imron Corporation and should be used in conjunction with the sites IT Administrator / Database Administrator’s policies.

The instructions in this section assume SQL Server has not previously been installed, if SQL exists please proceed to the section labeled ‘Migrating Data’.

  • If an enterprise edition of SQL is not available, download Microsoft SQL Server Express by performing a left mouse click on the link shown.
  • Select options to install Microsoft SQL Server Express, SQL Management Studio and SQL Configuration Manager.
  • Start “SQL Server Configuration Manager”, this can be accomplished by clicking on the Windows Start Menu, All Programs, Microsoft SQL 2008, Configuration Tools, SQL Server Configuration Manager.
  • Click on the “SQL Server Network Configuration” section.
  • Click on ‘Protocols for SQLEXPRESS’.
  • Enable the TCP/IP protocol, to enable the TCP/IP protocol right click on the TCP/IP protocol name from the column on the right and select Properties. From the Protocol Properties page, set the Enabled to a value of Yes.
  • Click on the “IP Addresses” tab and ensure “TCP Dynamic Ports” are enabled by setting them to a value of zero.
  • This will allow communication default Port 1433. Use the TCP Port 1433 where required.
  • Under the ‘SQL Native Client Configuration’ section, enable TCP/IP in the “Client Protocols section.
  • Create exceptions in the Windows Firewall (If used) for TCP port 1433 for the local Network.
  • Open SQL Server Management Studio, to open ‘SQL Server Management Studio’ click on the Windows Start Menu, All Programs, SQL Server Management Studio.
  • Go to the server properties and select “Connections”. Tick the box next to “Allow remote connections to the Server”.
  • From the server properties, select ‘Security’. Select the radio button for ‘SQL Server and Windows Authentication mode’.
  • Enable the SQL Browser, this step is imperative if network clients will be connecting to the SQL database.
  • During installation, the SQL Server Database Engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode. This topic describes how to change the security mode after installation. If Windows Authentication mode is selected during installation, the sa login is disabled. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login will remain disabled. To enable the sa login, use the following.

To enable the sa login by using Management Studio

  • In SQL Server Management Studio, expand Security, expand Logins, right-click sa, and then click Properties.
  • On the General page, you might have to create and confirm a password for the sa login.
  • On the Status page, in the Login section, click Enabled, and then click OK.

Migrating data

  • Before migrating Application from an MS Access database to SQL Server, it is important to first close all applications from all computers.
  • Make sure that you have access to the SQL Server database that you wish to migrate your MS Access database to. You will need to know the SQL Computername, Database Name, SQL Username and Password.
  • Once you have verified that you have SQL Server installed, make sure that the MSSQLServer service is running on the SQL Server computer. To verify this, open Control Panel>Administrative Tools>Services from the computer that has SQL Server installed.
  • From Management Studio Create a new database.
  • Run the Access2SQL utility and enter your SQL Server Computername, instance and logon information. Select the [Use and Existing Database] for the Database property. Click the green arrow to begin the conversion process.
  • The Access2SQL Utility will read in all of the Applications MS Access database tables and create these in the new Application SQL Server Database. This process can take a long time based upon the amount of history and number of personnel records that the site has.

Note: Event database file sizes close to or above 2GB should not be used with this utility. Archive and compact the Events.mdb file to reduce the size or backup and replace it with a new Events.mdb file.

  • Once the data import is complete, click OK to allow the Access2SQL Utility to automatically configure the Application to look to the SQL Server database instead of the MS Access database.
  • After this step has completed, the application can be started by double clicking on the IS2000 Icon located on the desktop.