Introduction:


By default, WIN‑911 utilizes a shared instance of SQL LocalDB for its Log Viewer data storage. However, WIN‑911 can be configured to instead utilize an instance of SQL Server for this data. There are advantages and disadvantages to either configuration. This document will attempt to review these considerations and provide instructions for configuring an alternate data storage solution.


Technical Considerations:


The major benefit of LocalDB is that it is local; while the major drawback of LocalDB is that it is local! A SQL LocalDB instance generally has all the capabilities and constraints of SQL Server Express except that it is local to its owner Windows session. LocalDB databases cannot be accessed remotely; however, they can be shared with other local Windows sessions (WIN‑911 shares its “WIN911LDB” instance as “WIN911ALL”). Since LocalDB is guaranteed to be locally available to the Dispatcher and is owned by the same windows session as the Dispatcher, this helps ensure that the Dispatcher will always be able to log its activity. Contrariwise, an instance of SQL Server may be running across the network, making Dispatcher logging vulnerable to network disruptions and failures of another physical machine. Please note that failures to log by the Dispatcher will be logged to the Windows Event log as a warning and will otherwise not disrupt the normal processing of alarms by the dispatcher – notifications will still be sent. Additionally, the lack of network access to LocalDB provides a high degree of security for your Dispatcher activity log without the maintenance of an SQL server instance.


Use Cases:


A common motivation for moving the WIN-911 Log Viewer data storage is to allow access to the WIN‑911 Log Viewer utility from machines remote to the WIN‑911 Dispatcher host machine. Another valid use case would be to take advantage of larger data limits allowed by higher editions of SQL Server for especially large or busy systems or systems for which the log must be maintained longer. Additionally, systems with low or infrequent Dispatcher activity in which the WIN-911 Service user session is not logged in should consider moving from LocalDB because LocalDB attempts to stop instances whose owners are logged out and which are not active. Finally, systems with limited resources might consider an alternate data storage solution as a means to free memory and processor for the Dispatcher.


Steps:


Copy Log Database


  1. Close any instances of the WIN-911 Log Viewer that are open. 
  2. Browse to C:\ProgramData\WIN-911 Software
  3. Copy Log.mdf and Log_log.ldf into your SQL Server instance DATA folder. 
    1. If the WIN-911 installer installed SQL Server Express 2014, your DATA folder will be located here, C:\Program Files\Microsoft SQL Server\MSSQL12.WIN911\MSSQL\DATA
    2. If you're not sure where your DATA folder is located, connect to your target SQL instance using SQL Management Studio, right-click the instance name on the top left, select Properties, select Database Settings, you will see the location of your DATA folder under Database default locations
  4. Rename the two files you copied with a unique name. E.g. Log.mdf -> ComputerName-911.Default.Log.mdf, Log_log.ldf-> ComputerName-911.Default.Log_log.ldf where ComputerName is the name of the computer running WIN-911.


Attach Log Database to SQL Server Instance


  1. Connect to the SQL Server instance where you'd like to store your WIN-911 log database using SQL Management Studio.
    1. If the WIN-911 installer installed SQL Server Express 2014 and you'd like to store your log database in the same instance, the name of the instance will be WIN911.
  2. Right-click the Databases folder in the Object Explorer window on the left and select Attach...
  3. Click the Add button and you will see a file browser dialog.
    1. The default location should be your SQL Server instance DATA folder, if not, browse to the DATA folder where you copied your log database. 
  4. You should see the database file Log.mdf, select it and click OK
  5. The Attach Databases dialog will appear. On the top pane titled Databases to attach:, you will see a column titled Attach As. Click this field and rename LOG.MDF to a unique database name, e.g. ComputerName-V8.Default.Log, where ComputerName is the name of the computer running WIN-911.
    1. You don't need to add the .MDF file extension to your database name.
  6. Click OK and your log database will be attached to your SQL Server instance.


Edit SQL Security Settings


The WIN-911 Dispatcher Runtime, which runs as a Windows Service, is the only process which writes to the WIN-911 log so you must give the Windows user running the service read/write privileges. You do this by assigning the user the SQL DB role "db_owner" for the Log database. You can locate the correct Windows user by opening the Windows Services list and checking which user is listed under the Log On As column for the WIN-911 Dispatcher Runtime service.

The WIN-911 Log Viewer is used to view the WIN-911 log and will connect to the log database using the Windows user it is launched under. Meaning, if you are running the WIN-911 Log Viewer with the user which is running the WIN-911 Dispatcher Runtime service, you will have no problems connecting. However, if you are running the WIN-911 Log Viewer under a different Windows user than the user running WIN-911 Dispatcher Runtime service, you may need to grant the user read privileges. You can do this by adding the Windows user to the SQL Logins and assigning the SQL DB role "db_datareader" for the log database. If you would like every user in Windows to be able to read the log, then you can assign the Authenticated Users group the "db_datareader" SQL DB role. 


WIN-911 Dispatcher User

  1. Connect to the SQL Server instance you attached your WIN-911 log database.
  2. Expand the Security folder and then the Logins folder
  3. If you're using the SQL Server instance that was installed by WIN-911, you should see the Windows user you need to assign the "db_owner" role to. Right-click the user and select Properties
    1. If you don't see the correct user you will need to right-click the Logins folder and select New Login...
    2. The Login Properties dialog will appear. Use the Search button to locate the correct user then continue to Step 4.
  4. Select the page on the left titled User Mapping
  5. You will see a list of databases on the right, find the log database you attached earlier and check the Map checkbox. 
  6. You will see the available DB roles in the bottom pane, check db_owner checkbox and click OK


WIN-911 Log Viewer User

  1. Connect to the SQL Server instance you attached your WIN-911 log database.
  2. Expand the Security folder and then the Logins folder
  3. Right-click the Logins folder and select New Login...
  4. The Login Properties dialog will appear. Use the Search button to locate the user you'd like to grant read permissions.
    1. If you want all users on the system to have read access, select the Authenticated Users group.
  5. Select the page on the left titled User Mapping
  6. You will see a list of databases on the right, find the log database you attached earlier and check the Map checkbox. 
  7. You will see the available DB roles in the bottom pane, check the db_datareader role checkbox and click OK


Edit Connection Strings


Since you have changed the location of the log database, you need to tell the WIN-911 Dispatcher Runtime and the WIN-911 Log Viewer where you moved it to. You do this by editing the database connection strings for both processes. 


WIN-911 Dispatcher

  1. Open WIN911.Dispatcher.Runtime.exe.config in Notepad, located here: C:\Program Files (x86)\WIN-911 Software\WIN-911 Enterprise\Dispatcher
  2. Find the <connectionStrings> element which contains the connection string to the log database.
  3. Locate data source=(localdb)\.\WIN911ALL; within the connection string.
  4. Change (localdb)\.\WIN911ALL; to the SQL instance you moved your log database to.
    1. E.g. data source=COMPUTER_NAME\WIN911;
  5. Save your changes and restart the WIN-911 Dispatcher Runtime service.


WIN-911 Log Viewer

  1. Open WIN911.Log.Viewer.exe.config in Notepad, located here: C:\Program Files (x86)\WIN-911 Software\WIN-911 Enterprise\Dispatcher
  2. Find the <connectionStrings> element which contains the connection string to the SQL database.
  3. Locate data source=(localdb)\.\WIN911ALL;
  4. Change (localdb)\.\WIN911ALL; to the SQL instance you moved your log database to.
    1. E.g. data source=COMPUTER_NAME\WIN911; 
  5. Save your changes and start the WIN-911 Log Viewer


Conclusion


If you followed the steps in this article, you have successfully moved your WIN-911 Log database from a LocalDB instance to SQL Server. If you experienced any issues with this article, please submit a support request or contact Product Support at 512-326-1011 x3 or toll free in the US and Canada at 1-800-331-8740 x3