Microsoft SQL Server is the database utility that RentalPoint uses to store information. SQL is an established database engine that has been utilized by RentalPoint since 2002. RentalPoint supports SQL Server software that is actively supported by Microsoft up to and including SQL Server 2019, both the free and the full versions. Note that there are limitations with the free version that you may want to consider.
When IT people are asked to size a server for an application like RentalPoint, the common misconception is that RentalPoint is like an accounting system or an order entry system or a database management program. So they tend to size an SQL server with little resources, given that most accounting or oder entry systems could probably run hundreds of user's with a small server.
This is incorrect because RentalPoint includes something we call "Predictive equipment scheduling".
Predictive equipment scheduling
Imagine if you could, an airline reservation system, there would be hundreds perhaps thousands of flights in the system, each plane would have up hundreds of seats to reserve. So for any computer reservation system it would have to find the flight and check how many seats were available in the class required, or perhaps check or display those seats. Now that sounds like some processing, but it is far less than a system like RentalPoint has to handle. For example, when an operator adds a package or a product that contains components/accessories in RentalPoint, the system must load all the transactions (other bookings) against each of the products, sort them into date and times sequence, add purchase orders, deduct assets that are out of service or have been transfered to another location, then calculate the minimum balance between the warehosue out and in dates and times AND do this for each of the components/accessories selected. Now that takes time and significant MS SQL resources.
RentalPoint also has to calculate the availablility on large lists of equipment when bookings are opened, the booking hardcopy is printed, the shortage list is opened and the checkout window is opened.
MS SQL works best if it can store the entire database in RAM at all times,
If it can not, for example if it was only allowed to store 10% of the database in RAM then tables would have to be read from the hard drive which is much slower than RAM, this would mean slow performance, installing faster drives will make little difference. If your server runs more than one database application that uses MS SQL, then the server needs enough RAM to store all the databases in use plus leave 1 GIG or more for the operating system (Windows server), to run.
So the resources that an MS SQL server must have to serve RentalPoint must be significant, as a rule of thumb follow this quick guide :-
| user's | CPUs | CPU Cores |
| 1 .. 5 | 1 | 1 |
| 6 .. 10 | 1 | 2 |
| 11 .. 20 | 1 | 4 |
| 21 -- 40 | 2 | 8 |
| 41 .. 60 | 4 | 12 |
| 61 .. 80 | 4 | 16 |
| 81 .. 120 | 8 | 32 Clustered servers x 2, 4 CPU's each, 4 cores each |
Minimum RAM needs to be 1 GIG plus the size of the database.
Generally RAM is cheap these days, so a small investment in RAM can yield significant performance from an SQL server.
The above table is only a guide and at the top end a guesstimate, database size and activity does impact this and more resources may be required if the database is large or activity is high for the number of user's.
MS SQL servers for RentalPoint should be dedicated to MS SQL for more than 5 user's, placing other applications on the MS SQL server such as Outlook, Office, Virus checking etc is not recommended.
Note that the RentalPoint system expects SQL to already be installed and available BEFORE the RentalPoint install process.
What is Microsoft SQL Server?
Microsoft SQL Server is the database utility that RentalPoint uses to store information. SQL is an established database engine that has been utilized by RentalPoint since 2002. RentalPoint supports SQL 2005 up to 2012, both the free and the full versions. Note that there are limitations with the free version that you may want to consider. More information on this is available on the Microsoft website at the following link.
http://www.microsoft.com/sqlserver/en/us/editions.aspx
Where do I install the SQL Server?
The SQL Server does not necessarily need to be on the same computer that you plan to install RentalPoint on. If the SQL Server instance is configured correctly for network access, RentalPoint will be able to access it remotely.
- If you are planning to use RentalPoint in a domain environment where many users will be running RentalPoint from their own PC but accessing the database in a central location, then install SQL on the server.
- If all users are logging into a dedicated Terminal Server to access RentalPoint then the SQL Server will be installed on the Terminal Server
- If you have a workgroup(or peer to peer) environment, you will choose ONE machine to install the SQL server and allow any other workgroup machines access to it.
- If you are running RentalPoint in a standalone environment on a laptop or PC then you will need to install SQL Server on your machine
What do I need to install?
There are two components to install when using Microsoft SQL Server.
- The main component is the actual database engine that will store all your information.
- The second component is the database manager. The database manager allows administrators to properly configure and maintain the database. This tool will also be needed for RentalPoint support staff to assist your company, should any issues arise.
How do I download Microsoft SQL Server?
The SQL Server 2012 Express Version download, with both components, is available for download on the Microsoft website at the following link, simply double click the downloaded file and follow the instructions to install:
https://www.microsoft.com/en-ca/download/details.aspx?id=29062
- For 64bit Operating System choose SQLEXPRWT_x64_ENU
- For 32bit Operating System choose SQLEXPRWT_x86_ENU
The ‘WT’ in the filename shows it is the version ‘with tools i.e. with management studio’, the above are the naming conventions used by Microsoft at the time this document was last revised
Privileges and Service Packs
Microsoft SQL Server 2012 offers enhanced security over previous versions. To eliminate potential installation issues turn on all privileges that are available. Ensure the user is logged in as Administrator on the machine the installation is taking place on in order to have sufficient access rights to make the changes.
When running Windows 7 the minimum service packs must be downloaded and installed for Microsoft SQL Server versions: Service Pack 3 for 2005 and Service Pack 1 for 2008. If you are unsure how to do this change your windows update to include other Microsoft updates, initiate a check for updates and let Microsoft do the work for you!
MS SQL is the name of the database management program that RentalPoint uses to store and retrieve data. RentalPoint communicates with MS SQL by using queries. Queries are complex sets of commands that tell MS SQL what data is required and what filters to apply when fetching the data. Some queries are extremely complex and are hundreds of lines of code, while others are very simple. When the desktop or terminal server CPU's are showing a high workload while waiting for a response from RentalPoint, it is because the desktop or terminal server is waiting for the MS SQL server to respond with the data requested.
It is important for the MS SQL server to be properly configured and to have sufficient resources available to it to provide peak performance to RentalPoint and therefore to your user's.
To configure MS SQL for peak performance with RentalPoint open SQL Server Management Studio. If this program does not display in your programs you have to download it from the Microsoft web site and install it.
Once the management studio is open, select the server name from the drop down list.
Right click your database and select Properties, a window similar to the widow below will display. Take note of the database size. The size of the database in this example is 23 mb, which is small by indstry standards. Usually a database for a small company will grow to 500 mb or larger after a year or two of operation. It is important to size a server with sufficient RAM to support the RentalPoint database now and for the next few years to come.
Select Options on the left hand side.
Change the recovery model to "Simple" and click "OK"
Right click on the SQL Server name at the top of the Database tree and select Properties. Select the Memory page.
MS SQL works best if it can store the entire database in RAM at all times.If it can not this may mean slow performance. Installing faster drives will make little difference. If your server has more than one database application that uses MS SQL, then the server needs enough RAM to store all the databases in use plus leave 1 GIG or more for the operating system to run.
See this technet article for more information: http://technet.microsoft.com/en-us/library/ms178067.aspx
Use AWE to allocate memory
leave this unchecked in most cases, see below for more details.
Specifies that SQL Server will take advantage of Address Windowing Extensions (AWE) in Microsoft Windows 2000 and Windows Server 2003 to support up to 64 GB of physical memory. AWE only applies to 32-bit operating systems. To use AWE you must configure Windows settings in addition to this SQL Server setting. To set this option, you must configure the lock pages in memory policy. For instructions about setting the policy, see How to: Enable the Lock Pages in Memory Option (Windows).
Minimum server memory (in MB)
Set this to the current size of your database, assuming of course that your server has sufficient RAM to do so, to find this take the installed RAM in the server and deduct 1 GIG, this will be for the operating system (windows) to run. As your database grows you may have to increase this limit. If you allocate too much memory for SQL to manage your database then Windows performance may be slow.
Maximum server memory (in MB)
Set this to the maximum size that you think the database could grow to in the next few months, or the maximum size that the Server can handle without impacting on the operating systems performance, whichever is smaller. As a general rule of thumb, take the server physical RAM and deduct 1 GIG.
If however the maximum amount of RAM that can be set here is less than the size of the database, well of course you need to add more RAM to the server, but in the mean time it is important to set a limit on the amount of RAM that MS SQL will allocate to the datbase, otherwise Windows server performance will start to regrade, especially once Windows starts using more and more virtual ram, the result can be what is commonly called in IT as 'thrashing'. This condition occurs when the operating system spend a lot a time loading and unloading code from virtual memory without doing much actual processing.
More Configuration Info :
Specifies the maximum amount of memory SQL Server can allocate when it starts and while it runs. This configuration option can be set to a specific value if you know there are multiple applications running at the same time as SQL Server and you want to guarantee that these applications have sufficient memory to run. If these other applications, such as Web or e-mail servers, request memory only as needed, then do not set the option, because SQL Server will release memory to them as needed. However, applications often use whatever memory is available when they start and do not request more if needed. If an application that behaves in this manner runs on the same computer at the same time as SQL Server, set the option to a value that guarantees that the memory required by the application is not allocated by SQL Server.
Index creation memory (in KB, 0 = dynamic memory) - leave this as zero
Specifies the amount of memory (in kilobytes) to use during index creation sorts. The default value of zero enables dynamic allocation and should work in most cases without additional adjustment; however, the user can enter a different value from 704 to 2147483647.
Minimum memory per query (in KB)
Specifies the amount of memory (in kilobytes) to allocate for the execution of a query. The user can set the value from 512 to 2147483647. The default value is 1024.
Now that you have installed and configured Microsoft SQL Server, continue to the next section to download the RentalPoint Installation Package and install the system.