Prerequisites and limitations

System Prerequisites

The system requirements for BigFix 10 Insights are as follows:

Database Permissions

There are three types of users:

1. Report Users (PowerBI/Tableau): These users require READ ONLY permission, granting SELECT access to all tables in Insights.

   Note: In multi-datasource scenarios, the Read Only database user may not access all table data due to the Row-Level Security (RLS) policy. Two methods to bypass the RLS security policy and generate reports across all datasources are using the dbo user or creating a user group called 'sec_rls_excluded' and adding the user into it.

2. Insights Creators (INSIGHTS_CREATOR): Insights creators need the following permissions:

   • CREATE DATABASE
   • ALTER ANY LOGIN
   • ALTER ANY LINKED SERVER

3. Insights Administrators (INSIGHTS_ADMIN): As administrators who use Insights and run regular ETLs, these users require the following permissions:

   • CREATE DATABASE
   • ALTER ANY LOGIN
   • ALTER ANY LINKED SERVER
   • CREATE SCHEMA
   • SELECT/INSERT into dbo
   • SELECT/INSERT into webui
   • ALTER ANY SECURITY POLICY

Credential prerequisites

The credential prerequisites for the Insights ETL components follow:

Network prerequisites

As with all data loading applications, minimize and optimize network latency and bandwidth impacts. Align resources within close network proximity, where or when possible. If possible, co-locate replicas with the Insights Data Lake.

Application requirements

SQL Server Listening Port

By default, SQL Server listens on TCP port number 1433, but for named instances the TCP port is dynamically configured. There are several options available to get the listening port for a SQL Server named instance.

Compute and memory requirements

The following section is excerpted from the BigFix Capacity Planning guide. You can refer the complete publication: BigFix Performance & Capacity Planning Resources

For best capacity-planning results, base the Insights deployment on a database replica. You have many options for managing the base capacity, such as using database backups, MS SQL replication support, and MS SQL availability groups.

• For an MS SQL replica, additional MS SQL resources are required. See the following table for considerations and note the additional pressure on log space that is required for the replica.
• It is possible to bypass the replica for low-scale and test deployments. However, use care with this approach by using premonitoring and postmonitoring, in particular for the DBMS, to ensure system and database health.
• For input/output operations per second (IOPS), the general DBMS server standard of 5,000 IOPS with less than 1msec latency provides the best results. However, the I/O profile tends to be more read intensive and highly subject to reporting content and workloads. As a result, it might be possible to have good performance with a 2,500-IOPS based storage device. However, this possibility can only be assessed through careful monitoring.

For the Insights server, the capacity planning requirements are for the base BigFix offering.

• The requirements for the base operating system, the MS SQL secondary instance (if applicable), and the customer provided reporting technology (for example, Tableau), and associated data sources must be included.
• For best results, the deployment size is represented in the BigFix Enterprise computers table data cardinality for the initial offering.

The CPU and memory recommendations are based on the Extract-Transform-Load (ETL) agent that ingests the BigFix root server content to build the Insights Data Lake. These requirements are generally low because of the economy of the ETL process.

Workload management and Insights DBMS sizing

Ingestion objects

Global content and linked items

In this release, global object mapping is limited to external content sites, fixlets (and tasks) and analysis. Custom content linking is not within the scope of this release.

In the Insights database, you can import data from one or more BigFix Enterprise Database data sources. This release supports data ingestion from up to 10 data sources, and the total number of endpoints of the ingested data sources must not exceed 250,000.

The concept of global objects amounts to the singular representation of the same object from multiple BigFix Enterprise Database data sources. For example, the BigFix Enterprise Server support site is the same on one data source, many data sources, and every data source.

Sizing concepts

The scalability and size requirements of Insights is directly related to the concepts of data sets with an additional dimension of “rate of change”.

These size of data sets varies for each environment as every environment is unique. For example, the rate of change of an environment with a considerable amount of property-result data impacts the database differently than the one that has a sizeable amount of fixlet data (property results vary in data return, but fixlet data is binary). The varying width of data is substantial between these two data types and impacts the size also. Sizing considerations comes always depend on numerous factors. Because of the dynamic nature of data sources and the number of objects ingested and linked to drives the initial full ETL data set, and the rate of change drives the subsequent sets of changes. Consequently, the approach to sizing focuses primarily on the size of the first full data set. The sets of changes are accommodated as a rate of change (percentage) that is applied as an assumption to the full data set. That assumption drives the projected rate of database growth over subsequent ETL changes. In concept, consider the following categories:

Some general trends correlate the initial data source size to the resulting Insights database size (after the first ETL). This resulting size can vary. However, the following query provides a rough sizing estimate of the database containers that Insights requires. Run the attached query on the ingesting BigFix Enterprise Database. The returned number is a conservative estimate of what the initial dataset might be after a 20% increase. Ensure that you run the query on all ingesting BigFix Enterprise environments to project the accumulated size of the first ETL.

-- SCRIPT USED to Guestimate the destination size database +- 20% For Full run

Select 1.2* (sum(TotalspaceMB)) as 'Initial Projected DB Container space required (MB)' from
(SELECT
t.NAME AS TableName,
--s.Name AS SchemaName, p.rows,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))
AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))
AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id WHERE
--t.NAME NOT LIKE 'dt%'
t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
and (
t.name like 'ACTIONRESULTS' OR t.name like 'ACTIONS'
OR t.name like 'ACTIONSTATESTRINGS'
OR t.name like 'COMPUTERS'
OR t.name like 'COMPUTER_GROUPS'
OR t.name like 'COMPUTER_SITES' OR t.name like 'CUSTOM_ANALYSES'
OR t.name like 'CUSTOM_ANALYSIS_PROPERTIES'
OR t.name like 'CUSTOM_FIXLETS'
OR t.name like 'CUSTOM_FIXLET_FIELDS' OR t.name like 'DBINFO'
OR t.name like 'EXTERNAL_ANALYSES'
OR t.name like 'EXTERNAL_ANALYSIS_PROPERTIES'
OR t.name like 'EXTERNAL_FIXLETS'
OR t.name like 'EXTERNAL_FIXLET_FIELDS' OR t.name like 'FIXLETRESULTS'
OR t.name like 'GROUPS'
OR t.name like 'LONGQUESTIONRESULTS'
OR t.name like 'PROPERTIES'
OR t.name like 'QUESTIONRESULTS' OR t.name like 'SITENAMEMAP'
OR t.name like 'SITES'
) GROUP BY
t.Name, s.Name, p.Rows
)
as T1
-- SCRIPT USED to Guestimate the destination size T-Log Container +- 20% For Full run
Select 2.5*(sum(TotalspaceMB)) as 'Initial Projected T-Log Container space required (MB)' from
(SELECT t.NAME AS TableName,
--s.Name AS SchemaName, p.rows,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))
AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))
AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM sys.tables t INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id WHERE
--t.NAME NOT LIKE 'dt%'
t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
and (
t.name like 'ACTIONRESULTS' OR t.name like 'ACTIONS'
OR t.name like 'ACTIONSTATESTRINGS'
OR t.name like 'COMPUTERS'
OR t.name like 'COMPUTER_GROUPS' OR t.name like 'COMPUTER_SITES'
OR t.name like 'CUSTOM_ANALYSES'
OR t.name like 'CUSTOM_ANALYSIS_PROPERTIES'
OR t.name like 'CUSTOM_FIXLETS'
OR t.name like 'CUSTOM_FIXLET_FIELDS' OR t.name like 'DBINFO'
OR t.name like 'EXTERNAL_ANALYSES'
OR t.name like 'EXTERNAL_ANALYSIS_PROPERTIES'
OR t.name like 'EXTERNAL_FIXLETS'
OR t.name like 'EXTERNAL_FIXLET_FIELDS' OR t.name like 'FIXLETRESULTS'
OR t.name like 'GROUPS'
OR t.name like 'LONGQUESTIONRESULTS'
OR t.name like 'PROPERTIES'
OR t.name like 'QUESTIONRESULTS' OR t.name like 'SITENAMEMAP'
OR t.name like 'SITES'
) GROUP BY
t.Name, s.Name, p.Rows
)
as T1

-- SCRIPT USED to Guestimate the TempDB database +- 20% For Full run
Select .5*(sum(TotalspaceMB)) as 'Initial Projected TempDB space required (MB)' from
(SELECT t.NAME AS TableName,
--s.Name AS SchemaName,
p.rows,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))
AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))
AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id WHERE
--t.NAME NOT LIKE 'dt%'
t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
and (
t.name like 'ACTIONRESULTS' OR t.name like 'ACTIONS'
OR t.name like 'ACTIONSTATESTRINGS'
OR t.name like 'COMPUTERS'
OR t.name like 'COMPUTER_GROUPS' OR t.name like 'COMPUTER_SITES' OR t.name like 'CUSTOM_ANALYSES'
OR t.name like 'CUSTOM_ANALYSIS_PROPERTIES'
OR t.name like 'CUSTOM_FIXLETS'
OR t.name like 'CUSTOM_FIXLET_FIELDS' OR t.name like 'DBINFO'
OR t.name like 'EXTERNAL_ANALYSES'
OR t.name like 'EXTERNAL_ANALYSIS_PROPERTIES'
OR t.name like 'EXTERNAL_FIXLETS'
OR t.name like 'EXTERNAL_FIXLET_FIELDS' OR t.name like 'FIXLETRESULTS'
OR t.name like 'GROUPS'
OR t.name like 'LONGQUESTIONRESULTS'
OR t.name like 'PROPERTIES'
OR t.name like 'QUESTIONRESULTS' OR t.name like 'SITENAMEMAP'
OR t.name like 'SITES'
) GROUP BY
t.Name, s.Name, p.Rows
)
as T1

Scale Implications, Best practices and Optimizations

System versioned tables

The data warehouse that BigFix Insights creates will leverage SQL Server's system versioned tables in order to keep a historical account of incoming and changing data.

The default retention policy for all temporal tables created by Insights is 36 MONTHS. At present, the only way to modify this is manual. However, it is possible to apply the WebUI setting _WebUIAppEnv_RETENTION_PERIOD to some other supported value (e.g. 100 DAYS). If you apply this setting prior to generating your Insights data lake, then all temporal tables will take this new value instead of the default. If you apply the setting after-the-fact then this will only affect those tables generated thereafter.

[dbo].[device_dimensions_historical]

SQL transactions and application behavioral patterns

All transactions use snapshot isolation during importation. From that point, SQL uses a prepared batch statement, and this statement is fed into an upsert. The upsert performs two functions: First is it reads the applicable destination row to see whether an update is required and the second is it inserts or updates the row as required. During importation, the tempdb is heavily used. Data is dumped into the tempdb and then read and processed for updation or insertion.

The end of the importation process, where global objects are computed and stored (warehousing), includes a large read impact (approximately 90%) and small write impact (approximately 10%).

Normal Business Intelligence report running activities generates a large read impact and nearly no write operations.

Considering the previous information, the suggested balance for the Insights DBMS should be optimized around 75 to 25 percent with a slight preference for read based off application usage. For this reason the following specifications are defined.

BigFix Insights SQL configuration guidelines

ETL rhythm and scheduling

Managing the replica

BigFix Insights relies on a replica of the BigFix Enterprise Data Source that is co-located with the Insights database. The WebUI Component of Insights is located on a designated WebUI Server, which is likely one of the ingested BigFix Enterprise Servers. The BigFix Insights database can also be co-located on the same WebUI Server. No technical controls prevent you from the putting the database on the WebUI Server. The WebUI Server maintains communication process to the BigFix Insights Database, while all ingested information and audit details are stored on the Insights database. The Insights database might consume more than one BigFix Enterprise Database, and if co-located on the WebUI Server, it might expand the information boundary to other BigFix Enterprise Data Sources on the Insights Database.

BigFix Server - Periodic Database backup (BFEnterprise)

USE [msdb]
GO
/****** Object:	Job [BigFix Database Backups] 13:58:51 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT SELECT @ReturnCode = 0
/****** Object:	JobCategory [Database Maintenance]	Script Date: 11/16/2012 13:58:51 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance'
AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',
@type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =	msdb.dbo.sp_add_job @job_name=N'BigFix Database Backups',
@enabled=1,
@notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0,
@description=N'No description available.', @category_name=N'Database Maintenance',
@owner_login_name=N'BIGFIXVM\Administrator', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:	Step [Backup BESReporting]	Script Date: 11/16/2012 13:58:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup BESReporting',
@step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=3, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-----------------------------------------------------------------
-- Backup database to file
declare @backupFileName varchar(100), @backupDirectory varchar(100), @databaseDataFilename varchar(100), @databaseLogFilename varchar(100), @databaseDataFile varchar(100), @databaseLogFile varchar(100), @databaseName varchar(100), @execSql varchar(1000)

-- Set the name of the database to backup
set @databaseName = ''BESReporting''
-- Set the path fo the backup directory on the sql server pc set @backupDirectory =
''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\''
-- such as ''c:\temp\''
-- Create the backup file name based on the backup directory, the database name and today''s date
set @backupFileName = @backupDirectory + @databaseName + ''-'' + replace(convert(varchar, getdate(), 110), ''-'', ''.'') + ''.bak''

-- Get the data file and its path
select @databaseDataFile = rtrim([Name]), @databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files inner join
master.dbo.sysfilegroups as groups on
files.groupID = groups.groupID where DBID = (
select dbid
from master.dbo.sysdatabases where [Name] = @databaseName
)

-- Get the log file and its path
select @databaseLogFile = rtrim([Name]), @databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName
)
and groupID = 0 set @execSql = ''
backup database ['' + @databaseName + ''] to disk = '''''' + @backupFileName + '''''' with
noformat, noinit, name = '''''' + @databaseName + '' backup'''', skip,
compression''
exec(@execSql)', @database_name=N'master', @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:	Step [Backup BFEnterprise]	Script Date: 11/16/2012 13:58:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup BFEnterprise',
@step_id=2, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-----------------------------------------------------------------
-- Backup database to file
declare @backupFileName varchar(100), @backupDirectory varchar(100), @databaseDataFilename varchar(100), @databaseLogFilename varchar(100), @databaseDataFile varchar(100), @databaseLogFile varchar(100), @databaseName varchar(100), @execSql varchar(1000)

-- Set the name of the database to backup set @databaseName = ''BFEnterprise''
-- Set the path fo the backup directory on the sql server pc set @backupDirectory =
''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\''
-- such as ''c:\temp\''
-- Create the backup file name based on the backup directory, the database name and today''s date
set @backupFileName=@backupDirectory + @databaseName+''-'' + replace(convert(varchar, getdate(), 110), ''-'', ''.'')+''.bak''

-- Get the data file and its path
select @databaseDataFile = rtrim([Name]), @databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files inner join
master.dbo.sysfilegroups as groups on
files.groupID = groups.groupID where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName
)


-- Get the log file and its path
select @databaseLogFile = rtrim([Name]), @databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files where DBID = (
select dbid
from master.dbo.sysdatabases where [Name] = @databaseName
)
and
groupID = 0

set @execSql = ''
backup database ['' + @databaseName + ''] to disk = '''''' + @backupFileName + '''''' with
noformat, noinit,
name = '''''' + @databaseName + '' backup'''', skip,
compression''

exec(@execSql)', @database_name=N'master', @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id
= 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Backup Schedule',
@enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0,
@freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20121113, @active_end_date=99991231, @active_start_time=20000, @active_end_time=235959,
@schedule_uid=N'16b08a53-4276-4cf4-8d40-d33ff7794db5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name
= N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION
GOTO EndSave QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Primary BigFix Server – Periodic Scheduled Task: Robocopy Database and BES Server backups to Standby Server

forfiles -p "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER
\MSSQL\Backup" -m *.* -d -7 -c "cmd /C echo @path" >> C:\maintenance\deleted.txt
forfiles -p "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER
\MSSQL\Backup"
-m *.* -d -7 -c "cmd /c del /q @path"
robocopy "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL
\Backup"
"\\Replica\c$\dbbackups" /s /z /np /w:1 /r:1 /mir /log:C:\Maintenance
\robocopylog.txt /TEE /maxage:3

BigFix replica Server – Periodic Database restore (BFEnterprise)

USE [msdb]
GO
/****** Object:	Job [Restore BigFix Databases] Script Date: 11/16/2012 14:07:56 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT SELECT @ReturnCode = 0
/****** Object:	JobCategory [Database Maintenance] Script Date: 
11/16/2012 14:08:32 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =	msdb.dbo.sp_add_job @job_name=N'Restore BigFix Databases',
@enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0,
@description=N'No description available.', @category_name=N'Database Maintenance', @owner_login_name=N'DSSSUA\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:	Step [Restore BESReporting]	Script Date: 11/16/2012 14:10:28 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore BESReporting',
@step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=3, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-----------------------------------------------------------------
-- Restore database from file
use master go
declare @backupFileName varchar(100), @restoreDirectory varchar(100), @databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
@databaseDataFile varchar(100), @databaseLogFile varchar(100), @databaseName varchar(100), @execSql nvarchar(1000)

-- Set the name of the database to restore set @databaseName = ''BESReporting''
-- Set the path to the directory containing the database backup set @restoreDirectory = ''C:\DBBackups\'' -- such as ''c:\temp\''

-- Create the backup file name based on the restore directory, the database name and today''s date
set @backupFileName=@restoreDirectory+@databaseName + ''-'' + replace(convert(varchar, getdate(), 110),''-'',''.'')+ ''.bak''

-- Get the data file and its path
select @databaseDataFile = rtrim([Name]), @databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files inner join
master.dbo.sysfilegroups as groups on
files.groupID = groups.groupID where DBID = (
select dbid
from master.dbo.sysdatabases where [Name] = @databaseName
)

-- Get the log file and its path
select @databaseLogFile = rtrim([Name]), @databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files where DBID = (
select dbid
from master.dbo.sysdatabases where [Name] = @databaseName
)
and
groupID = 0

-- Create the sql to kill any active database connections set @execSql = ''''
select @execSql = @execSql + ''kill '' + convert(char(10), spid) + '' '' from master.dbo.sysprocesses
where db_name(dbid) = @databaseName and
DBID <> 0
and
spid <> @@spid exec (@execSql)

set @execSql = ''
restore database ['' + @databaseName + ''] from disk = '''''' + @backupFileName + '''''' with
file = 1,
move '''''' + @databaseDataFile + '''''' to '' + '''''''' + @databaseDataFilename + '''''',
move '''''' + @databaseLogFile + '''''' to '' + '''''''' + @databaseLogFilename + '''''',
replace''

exec sp_executesql @execSql', @database_name=N'master', @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:	Step [Restore BFEnterprise]	Script Date: 11/16/2012 14:10:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore BFEnterprise',
@step_id=2, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-----------------------------------------------------------------
-- Restore database from file

use master go
declare @backupFileName varchar(100), @restoreDirectory varchar(100), @databaseDataFilename varchar(100), @databaseLogFilename varchar(100), @databaseDataFile varchar(100), @databaseLogFile varchar(100), @databaseName varchar(100), @execSql nvarchar(1000)
-- Set the name of the database to restore set @databaseName = ''BFEnterprise''
-- Set the path to the directory containing the database backup set @restoreDirectory = ''C:\DBBackups\'' -- such as ''c:\temp\''
-- Create the backup file name based on the restore directory, the database name and today''s date
set @backupFileName = @restoreDirectory + @databaseName + ''-'' + replace(convert(varchar, getdate(), 110), ''-'', ''.'') + ''.bak''

-- Get the data file and its path
select @databaseDataFile = rtrim([Name]), @databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files inner join
master.dbo.sysfilegroups as groups on
files.groupID = groups.groupID where DBID = (
select dbid
from master.dbo.sysdatabases where [Name] = @databaseName
)

-- Get the log file and its path
select @databaseLogFile = rtrim([Name]), @databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files where DBID = (
select dbid
from master.dbo.sysdatabases where [Name] = @databaseName
)
and
groupID = 0

-- Create the sql to kill any active database connections
set @execSql = ''''
select @execSql = @execSql + ''kill '' + convert(char(10), spid) + '' '' from master.dbo.sysprocesses
where db_name(dbid) = @databaseName and
DBID <> 0
and
spid <> @@spid exec (@execSql)

set @execSql = ''
restore database ['' + @databaseName + ''] from disk = '''''' + @backupFileName + '''''' with
file = 1,
move '''''' + @databaseDataFile + '''''' to '' + '''''''' + @databaseDataFilename + '''''',
move '''''' + @databaseLogFile + '''''' to '' + '''''''' + @databaseLogFilename + '''''',
replace''

exec sp_executesql @execSql', @database_name=N'master', @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id
= 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId,
@name=N'Restore Schedule', @enabled=1,
@freq_type=4,
@freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20121113, @active_end_date=99991231, @active_start_time=50000, @active_end_time=235959,
@schedule_uid=N'1fd12e1c-1a21-49d3-b57a-e8195c980b29'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name
= N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION
GOTO EndSave QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Appending SQL and SAN

SAN considerations

You have to consider several factors when you install and configure an SQL Server to a SAN based environment. You must be familiar with both SQL and SAN.

Make sure that you familiarize yourself with the fundamentals of SQL Servers, when you install a SAN. Whether or not a SAN is being used, it is important to have a good knowledge of the hardware that SQL files are being stored on. The configuration values must not conflict with the fundamentals.

SAN and RAID configurations

SAN hardware supports zoning, which an admin can use to dedicate full physical disks to a LUN so that the I/O is isolated from the SQL Server. SQL Server performance depends on read/write access to physical disks. So, basic RAID fundamentals are important when you deploy servers on a SAN. SQL is an I/O intensive application, and the highest priority for performance should be given to the SQL Servers disk configurations. Microsoft recommends to separate SQL log files from data files at a physical disk level. In a SAN environment, this means separating the database log files to LUNs, which have dedicated physical disks, from those that are not shared with other LUNs, including those that are used for other database logs, data, or index files. A write response time of 2 - 3 ms and a read response time no greater than 20ms is the goal for this configuration according to Microsoft best practices. Isolate physical disks that servers or applications share, because different servers that run different applications with different I/O characteristics might cause problems.

Further considerations

On large database servers that use SANS, isolate the TempDB, Logs, and database file subsystems from the physical disk space requirements. Because large database engines usually  use TempDBs and transaction logs, additional consideration must given to separate an SQL TempDB or transaction log files to its own RAID subsystem, preferably RAID 10. The Microsoft SQL best practice typically recommends RAID 1+0 for MDF and Index Files, and Microsoft does not recommended RAID5 because of increased read/write times that are associated with it. Based on Microsoft recommendations use a 250Kb stripe size. Never set stripe sizes lower than 64Kb because that setting is the size of a single SQL extent.

Storage requirements

It is an advantage to have distinct storage subsystems and controllers for specific components. For best results, set up discrete storage subsystems for each of the bases, the database logs, and the database containers. The database logs are typically characterized by the need for very fast sequential I/O. The database containers can have much more diverse access patterns, but are highly insulated from storage impact because of the database buffer pools.

Known issues and limitations

• Datasource authentication is currently limited to SQL authentication.
• Linked objects is limited to external sites, Fixlets, tasks, and analysis.
  Note: Custom sites are not supported.
• BigFix Enterprise Server operators, roles, and unmanaged assets are not ingested by Insights.
• Insights supports ingesting data from eight or fewer BigFix Enterprise databases, depending on performance qualification.

• The performance of the Tableau reports varies when they display data for a larger set of devices.
• You can use Tableau sample reports are only on an BigFix Insights database that has completed an ETL. The reports run as though an ETL is scheduled daily.

More information