One Identity recommends installing the full SQL Server version for databases above 6876.4 MB or 10,000 users. For databases below 6,000 MB, you can install SQL Server Express, and can later upgrade to the full version of SQL Server in case of a database growth. For more information on how database sizes relate to the number of users in the database environment, see the following table.
NOTE: SQL Server Express databases have a size capacity limit of 10,000 MB.
Also, when selecting the SQL Server version, consider that certain SQL Server installations may not be able to take advantage of the available processing power and memory storage.
Table 3: Maximum database size, compute capacity, and maximum memory of different SQL Server installations
|
Maximum database size |
524 PB |
524 PB |
524 PB |
10 GB |
|
Compute Capacity |
OS Max |
4 sockets or 24 cores |
4 sockets or 16 cores |
1 socket or 4 cores |
|
Maximum Memory |
OS Max |
128 GB |
64 GB |
1 GB |
Table 4: Recommended SQL Server versions for various Safeguard Privilege Manager for Windows environment sizes
|
1 |
4.7 MB |
SQL Server Express |
|
10 |
10.5 MB |
SQL Server Express |
|
100 |
73.0 MB |
SQL Server Express |
|
1,000 |
692.6 MB |
SQL Server Express |
|
2,000 |
1,378.6 MB |
SQL Server Express |
|
5,000 |
3,439.9 MB |
SQL Server Express |
|
10,000 |
6,876.4 MB |
SQL Server Express |
|
15,000 |
10,313.0 MB |
SQL Server |
|
20,000 |
13,749.5 MB |
SQL Server |
|
50,000 |
34,368.8 MB |
SQL Server |
|
100,000 |
68,734.2 MB |
SQL Server |
Safeguard Privilege Manager for Windows uses the default auto-growth configuration settings that comes installed on SQL Server. This setting sets the initial database size of SQL Server to 3 MB, then grows it by 1 MB every time the data limit is exceeded. The log file starts at 2 MB and is set to grow by 10% increments until the disk is full.
Even though the default auto-growth configuration settings work for Safeguard Privilege Manager for Windows, it may not be the most appropriate configuration for all environments (especially for customers exceeding 10,000 users).
Every time the database grows it takes a performance hit. In SQL Server storage terms, 1024 KB is 128 pages. These pages are stored in 8 KB blocks. For Safeguard Privilege Manager for Windows, which is going to potentially load millions of records, growing the data file of a database every 128 pages may result in a large performance hit, especially since SQL Server I/O requests are a major bottleneck.
Additionally, since auto-growth allocates chunks of data at a time it is easier for the database to become fragmented. With that in mind it is recommended to update the auto-growth settings.
The table below displays the recommended settings based on the size of the network environment. These values are not set in stone but are based on database growth rates of your specific environment. The rule of thumb is to set this value to one eight of the estimated database size. Ideally you should use auto-grow as a fail/safe parameter, and use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.
Table 5: Auto-growth recommendation for various Safeguard Privilege Manager for Windows environment sizes
|
1 |
4.7 MB |
1 MB |
|
10 |
10.5 MB |
1.3 MB |
|
100 |
73.0 MB |
9.1 MB |
|
1,000 |
692.6 MB |
86.5 MB |
|
2,000 |
1,378.6 MB |
172.3 MB |
|
5,000 |
3,439.9 MB |
429.9 MB |
|
10,000 |
6,876.4 MB |
859.5 MB |
|
15,000 |
10,313.0 MB |
1289.1 MB |
|
20,000 |
13,749.5 MB |
1718.7 MB |
|
50,000 |
34,368.8 MB |
4296.1 MB |
|
100,000 |
68,734.2 MB |
8591.8 MB |
NOTE: Steps may be slightly different for other supported versions of SQL Server.
To change the auto-growth settings:
-
Start SQL Server Management Studio.
-
Highlight, then right click the PAReporting database and navigate to Properties.
-
In the left Panel, select the Files from the Database Properties dialog. The Properties window will be used to change Auto-growth.
-
Identify the PAReporting name under Logical Name and change the Auto-growth based on table 5 above. In this example, we have set the auto-growth to 430 MB and a max size of unlimited and left the log file to grow my 10% to a limit of 20971252 MB. 430 is roughly one eighth of the 3,439.9 MB database size for the 5000-user environment.
When Safeguard Privilege Manager for Windows is installed, it uses the default file and log sizes specified by SQL Server. The default file size is 3 MB for the database and 2 MB for the database logs.
NOTE: Consider that once these files exceed the initial file size settings, further file size increases may result in data fragmentation in the disk, and corresponding performance issues.
To prevent such performance issues, scale the log file sizes accordingly. For example, if the database is expected to grow to a specific size in a month, One Identity recommends that you take the expected value, double it, then use the doubled value for the initial size.
For example, if the database is expected to grow to 2,000 MB in a month, set the initial database size to 4,000 MB. This will reduce the number of auto-growths and reduce fragmentation, as a larger allocation means that more database-related information can be accessed from the same disk location. The following table breaks down the recommended initial size for the database based on a range of 1 to 100,000 users for the different database size estimations.
Table 6: Initial size recommendation for different Safeguard Privilege Manager for Windows environment sizes
|
1 |
4.7 MB |
9.4 MB |
|
10 |
10.5 MB |
21.0 MB |
|
100 |
73.0 MB |
146.0 MB |
|
1,000 |
692.6 MB |
1385.2 MB |
|
2,000 |
1,378.6 MB |
2,757.2 MB |
|
5,000 |
3,439.9 MB |
6,879.8 MB |
|
10,000 |
6,876.4 MB |
13,752.8 MB |
|
15,000 |
10,313.0 MB |
21,626.0 MB |
|
20,000 |
13,749.5 MB |
27,498.0 MB |
|
50,000 |
34,368.8 MB |
68,736.0 MB |
|
100,000 |
68,734.2 MB |
137,468.4 MB |
