The early version of the FAST-PASS Visitor Management System (now referred to as Classic) was designed to use the Firebird Database Engine. Fast forward a few years, and one of the early decisions made for the new FAST-PASS 7 web application would be the implementation of using Microsoft (“MS”) SQL Server as the database engine. During the implementation, that process meant learning a new technology, but it was not entirely too steep of a hill to climb. The effort was not as daunting because we had learned a tremendous amount years ago when we migrated the A-PASS system from Firebird to Microsoft SQL Server.
That means that we were able to learn several lessons during each step of the process, from installing and configuring Microsoft SQL Server to the deployment of the first project, and it certainly introduced new challenges. Of course, these challenges often grew as the technology included several components and features. Hence the first step, especially with a multi-component technology such as Microsoft SQL Server, was to identify the essential components to be deployed and that required a solid understanding of the functionality of each component as it pertains to the FAST-PASS 7.
Therefore, we were able to leverage the amount of time and effort for the new FAST-PASS 7 Visitor Management System. To that end, in this article, we will examine the components and features of MS SQL Server and determine how they fit into the FAST-PASS 7 Visitor Management installation. Like most relational database management systems (RDBMSs), MS SQL Server includes several Components. The MS SQL Server product itself, however, is often divided into two distinct categories: Business intelligence (BI) and the Database Engine.
Business Intelligence (BI) refers to data converted into information that can then be used to make more informed business decisions. For example, when a visitor arrives, the end-user will conduct a brief interview and ask questions like: where are you going? who are you here to see? do you have identification? Have you been here before? Finally, you would print a pass. The aggregate of these visitations will create data that with some analysis can reveal trends in visitation patterns. Generally speaking, patterns may confirm that a trend will remain constant or that a trend is changing. In this case, the interest would be in reviewing the visit patterns. In many ways, reports are management tools and their purpose are to assist decision-makers to grasp the basic elements and relationships found in raw data.
For this reason, FAST-PASS 7 includes a series of reports where you can select a subset of parameters or filters (i.e., by Activity, Date Range, Group, Location, and other parameters) to focus on an area of interest. For example, you can select parameters or filters to query the database and the system will generate an output in the form of reports to help you investigate activities such as “Entries” and “Exits” from a particular "Location" and return total counts of person records. Moreover, you can export the results to a comma-separated value ("CSV") file for archival purposes or to share with third-party analysis tools. For example, you can import the CSV file into Microsoft Excel to perform data manipulation (e.g., apply charts and data filters) for further analysis to retrieve valuable information. Finally, because the FAST-PASS 7 Visitor Management system uses MS SQL Server as the database engine, the reports retrieve the data with a very reasonable response.
Based on the time frame selected, the FAST-PASS 7 Visitor Management built-in Dynamic Charts will generate a graphic representation of the data previously collected where lows and peaks in visitation patterns can be easily reviewed. With such concise graphical data on hand, an organization can review and plan accordingly to ensure sufficient capacity is available for different scenarios as the table below illustrates.
|1||Amenities||Understanding visitor composition to improve and provide a comfortable environment with useful features consider food courts, restrooms, seating, vending machines, water fountains - among other amenities would provide a pleasant stay for visitors.|
|2||Capacity Planning||Virtually all organizations have physical limits that define how many people are allowed in a location at a given time. Due to safety and regulations, in the event of an emergency, these capacities should not exceed so that visitors can safely evacuate.|
|3||Fire Drills||Emergencies are chaotic and unpredictable with no order that may compromise public safety. For this reason, organizations should be responsible to comply with regulations that stipulate the requirement for emergency planning, to ensure contingent actions are in place and ready to respond. Having an understanding of visitor flow can help prepare strategies along with specific prevention and intervention techniques that will allow you to apply in the event of a human-made crisis or natural disaster.|
|4||Janitorial Services Scheduling||Whether janitorial services are performed by employees or outsourced to a contractor, it will help to understand visitor volume to better respond to demand. Depending on the volume of visitors and their specific destination, such as auditoriums, stairwells, restrooms, or waiting rooms, at some point they would require cleaning, replacement, replenishment, or sanitation. With the information provided by Dynamic Charts on hand and organization can better set a schedule such as Hourly, Weekly, Monthly, Semi-Annually, or Annually - based on the data provided.|
|5||Security Staffing||The analysis of allocating and providing security staff is a complex undertaking. Each organization poses a unique operational challenge for an organization to respond within budget and regulatory constraints. In preparing a security staffing analysis, the organization would be reviewing the interaction between the facilities, employees, operational and cycles, and visitors, and also importantly the goal or mission of the organization.|
|6||Workplace Violence||There are many forms of aggression that range from homicide, staff-on-staff, visitor violence against staff, against organizations, terrorist attacks, and others that would result in lost work time and damages. With information on hand, an organization can review the flow of visitors and prepare prevention policies to mitigate or manage workplace violence.|
The Database Engine is at the core of the MS SQL Server components. The Database Engine runs as a Windows service on a machine (i.e. PC or Server) and is often referred to as an instance of SQL Server. While you can run multiple instances of SQL Server on a given server, for the FAST-PASS 7 Visitor Management System, one instance is enough. When the FAST-PASS 7 Visitor Management System connects to SQL Server, the instance is the target of the connection. Thereafter, the FAST-PASS 7 Visitor Management System, will send Transact-SQL (T-SQL) statements to the instance. The instance will respond and send data back to the client (e.g., Registration or Report modules). Also importantly, within the connection is a security layer that controls and validates access to the data as specified during the installation and by the database administrators (DBAs).
Additionally, the Database Engine enables you to leverage the full capabilities of all the other components, such as accessing, storing, and securing the data. The storage component of the Database Engine determines how the data is stored on disk. The FAST-PASS 7 Visitor Management database design includes various aspects that dictate how indexes, store procedures, tables, and views are physically organized on the disk subsystem.
Within the Database Engine itself, the storage engine is the primary component. Surrounding it are several additional components that rely on the engine. There are several components, too many to cover, but we will highlight the ones of greater importance and visibility as they relate to the FAST-PASS 7 Visitor Management System and they include the following:
|1||A T-SQL programming interface (Microsoft's implementations of the SQL ANSI standard language)||SQL Server provides a rich programming language that allows you to write simple and complex queries against the underlying storage structures. Using T-SQL, you can write data manipulation queries that enable you to modify and access the data on demand. Within the FAST-PASS 7 Visitor Management database, several objects have been created such as views, stored procedures, triggers, and user-defined functions that act as a means of surfacing that data. The FAST-PASS 7 Visitor Management System has been written in C# .NET and sends T-SQL queries to the database engine. The Database Engine will then resolve the queries and respond with results back to the client (e.g., Registration or Reporting module).|
|2||Security subsystem||For most organizations, data is a valuable asset, and keeping that data security is a major concern and responsibility. Any vulnerability in an organization's security might end up triggering a series of events that could prove damaging to a business. Therefore, MS SQL Server consists of a robust security subsystem that allows you to control access via two modes of authentication, SQL and Windows. This is in addition to the built-in FAST-PASS 7 Visitor Management System password policy, permissions, and user authentication features to access the web application.|
|3||SQL Server Agent||Without any doubt, this is one of the favorite features of MS SQL Server over the Firebird Database Engine. The MS SQL Server Agent runs as a separate service on an instance of SQL Server. Each instance of SQL Server has an accompanying SQL Agent service. The main purpose of SQL Server Agent is to execute scheduled tasks, such as rebuilding indexes, backing up databases, and so on. It allows you to schedule the jobs to run at various intervals throughout the day or night to help maintain the FAST-PASS 7 Visitor Management System database in a healthy and responsive state. Moreover, to ensure that you are notified in the event of a job failure, SQL Server Agent allows you to configure operators and alerts. Where an operator is simply an individual and an email address. Once you configure an operator, you can send notifications or alerts to that person when a job succeeds, completes, or fails.|
|4||High availability and disaster recovery tools||With growing demands on server availability and uptime, your relational database management system must include several mechanisms that will ensure the consistency and availability of your data. such as “Entries” and “Exits” from a particular "Location" and return total counts for all contractors, visitors, vendors, and their corresponding person records and photos.|
|5||SQL Server Management tools||This is another favorite feature of MS SQL Server over the Firebird database engine. MS SQL Server includes a graphical user interface that enables you to develop, manage, maintain and monitor in a SQL Server environment. SQL Server Management Studio (SSMS) allows you to perform just about any action you can imagine against an instance of SQL Server. SSMS is an integrated environment where you can access the instance of SQL Server where the FAST-PASS 7 Visitor Management System database is running. SSMS consists of a broad set of tools with a rich set of interfaces and script editors that simplify the process of developing and configuring SQL Server instances. Most DBAs are familiar with this tool and it is an industry-standard at this point.|
Microsoft offers SQL Server with different editions and this section could go on for many pages and tables to cover the differences and similarities. However, we will focus on the immediate differences as they apply to the FAST-PASS 7 Visitor Management System. While there may be several factors to evaluate the appropriate MS SQL Edition (i.e., supported versions by the organization). In particular, when selecting the appropriate MS SQL Server Edition, you will need to think about how many visitors do you expect to process per day (e.g., 30, 200, 1,000, or 3,000) and how many workstations will connect to the database (e.g., 1, 10, 50, or more).
|#||Edition||Overview||FAST-PASS 7 Application|
|1||Enterprise||This is considered the premium edition of SQL Server This edition is all-inclusive, meaning that it contains all the features available in every edition.||For On-Premise Client\Server deployments|
|2||Standard||Ideal for a departmental application.||For On-Premise Client\Server deployments|
|3||Web||SQL Server Web edition is a low total-cost-of-ownership option for Web hosting (e.g., Azure).||For Hosted Client\Server deployments|
|4||Express||The Express version of SQL Server is a great entry-level product It is perfect for learning and building small data-driven applications. Ideal for Standalone deployments.||For Standalone deployments|
The table below provides an overview of the scale limits per MS SQL Server Edition. The table is a simplified version provided by Microsoft. Again, we are going to simply highlight the main differences. However, the hyperlink below will take you to the location where the extensive documentation is available for review. “Editions and supported features of SQL Server 2019 (15.x)”
|1||Maximum compute capacity used by a single instance - SQL Server Database Engine||Operating system maximum.||Limited to lesser of 4 sockets or 24 cores||Limited to lesser of 4 sockets or 16 cores||Limited to lesser of 1 socket or 4 cores|
|2||Maximum memory for buffer pool per instance of SQL Server Database Engine||Operating system maximum||128GB||64GB||1410 MB|
|3||Maximum relational database size||524PB||524PB||524PB||10GB|
FAST-PASS 7 Visitor Management System running on Microsoft SQL Server and managed with SQL Management Studio.
The FAST-PASS 7 Visitor Management system was written from the ground-up with modern technologies. Given that MS SQL Server offers a robust set of components and tools that enable the design of efficient, flexible, and highly available database topology for organizations. Each component either complements or supplements the capabilities and functionality of the others and the FAST-PASS 7 Visitor Management System is leveraging these components to offer an enterprise solution.
Erci Moisa, MBA