SQL Server/SSIS/SSRS environment setup

Introduction

Here is the best practice for setup SQL Server with reporting services, integration services etc. This is also combine some requirements according to some projects needs.

Solution

Infrastructure consideration

Network: ETL makes connections source/destination databases frequently; to avoid unnecessary overhead on F5 or firewall. highly recommend to put source/destination database and ETL application servers within the same network segment.

Tcp time out: ETL processes natural, highly recommend fast release database connections on OS level for source/destination database servers and ETL application servers. to minimize too many TIME_WAIT connections held by operating system until timeout. Follow steps by below link for Linux and Windows servers.

For Windows, Avoiding TCP/IP Port Exhaustion
For Linux, Coping with the TCP TIME-WAIT state on busy Linux servers

edit /etc/sysctl.conf and set below

net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_timestamps = 1

run sysctl -p

Source Database preparation

Creating Source DB accounts for ETL process and Report service access

For MySQL source database account creation
for example source database is histories databse like trango_hghms_<env>, davis_smh_<env>, etc. Create ssis/ssrs database service account with below.

GRANT SELECT ON `%_sandbox`.* TO ‘ssis’@’%’ IDENTIFIED BY ‘ssis.xxxx’; GRANT SELECT ON `%_sandbox`.* TO ‘ssrs’@’%’ IDENTIFIED BY ‘ssrs.xxxx’;

GRANT ALL ON `da_general_sandbox`.* TO ‘ssrs’@’%’;

Supporting version

For our development/test and qualify environment SQL Server version, the below version are what we are supporting; until Sep- 2016

OS Name: Microsoft Windows Server 2008 R2 Standard OS Version: 6.1.7601 Service Pack 1 Build 7601

SQL Server version sync to 12.0.4100.1 (SP1)

Microsoft SQL Server 2014–12.0.4100.1 (X64)
Apr 20 2015 17:29:27
Copyright © Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

For more details on SQL Server version, please refer to

Microsoft SQL Server Version List SQL Server 2014 build versions

Other software requirement

Install .Net Framwork4.0 and Windows Management Framework 3.0 for the purpose of running Powershell 3.0 maintenance scripts.

WMF 3 Release Notes.docx

Installation

Pre installation

By now, we only consider stand alone installation for standard edition. hardware and software requirement just need to follow official ( Hardware and Software Requirements for Installing SQL Server 2014).

Hard disk layout:

C:\ operating system, page files, SQL Server Binary (local drive) D:\ SQL server data, transaction log, temp DB (local drive)
R:\ database backup file storage (local or network drive)

During installation, follow the below setting.

with service account svc_idc_db Required Features:

Database Engine Services Analysis Services Reporting Services — Native Client Tools Connectivity Integration Services

Client Tools Backwards Compatibilty Client Tools SDK
Management Tools — Basic Management Tools — Complete

Instance root directory: D:\

Shared feature directory: C:\Program Files\Microsoft SQL Server\

Shared feature directory(x86): C:\Program Files (x86)\Microsoft SQL Server\

Select Default instance
Default instance installation path show below

SQL Server directory: D:\MSSQL12.MSSQLSERVER

Analysis Services directory: D:\MSAS12.MSSQLSERVER

Reporting Services directory: D:\MSRS12.MSSQLSERVER

Collation for Database Engine: Latin1_General_CS_AS

Collation for Analysis Services: Latin1_General_CS_AS

Authentication Mode: Mixed Mode
Data Directories :

Data root Directory: D:\

System database directory D:\MSSQL12.MSSQLSERVER\MSSQL\Data

User database directory: D:\MSSQL12.MSSQLSERVER\MSSQL\Data

User database log directory: D:\MSSQL12.MSSQLSERVER\MSSQL\Data

Temp DB directory: D:\MSSQL12.MSSQLSERVER\MSSQL\Data

Temp DB log directory: D:\MSSQL12.MSSQLSERVER\MSSQL\Data

Backup directory: D:\MSSQL12.MSSQLSERVER\MSSQL\Backup

Backup directory can be reassign to network drive after.

(Analysis Services Configuration) Data root Directory: D:\

Data directory

D:\MSAS12.MSSQLSERVER\OLAP\Data

Log file directory

Temp directory

Backup directory

D:\MSAS12.MSSQLSERVER\OLAP\Log

D:\MSAS12.MSSQLSERVER\OLAP\Temp

D:\MSAS12.MSSQLSERVER\OLAP\Backup

Backup directory can be reassign to network drive after.

Opening SQL Server Management Studio
There is some tricks here, while you experience SQL Server Management Studio extremely slow, you need to follow the

below step to fix this problem.

Open Internet Explorer
Go to Tools -> Internet option Open the “Advanced” tab
In Security section,

Uncheck “Check for publisher’s certificate revocation”
Uncheck “Check for server certificate revocation (requires restart)” Uncheck “Check for signatures on downloaded programs”

Post installation

Restrict Database engine maximum server memory to 2GB. Because the server is running multiple application in a single box.

by right click instance, select property
in Server Properties page,
select Memory page
in Server memory options, change Maximum server memory(in MB): 2048

Enable backup compress globally

by right click instance, select property
in Server Properties page,
select Database Settings page
check Compress backup in Backup and restore section

Configure Model system database

What is a Model System Database?

Model is a system database which is used as a template while creating newer user databases in SQL Server.

All the default settings of the Model database will be adopted by the newly created user databases. Hence, it is very important to make necessary customization to Model System Database on each instance of SQL Server.

In this tip, we will take a look at few of the very importance customizations which a DBA must do for a Model System Database so that all the newly created databases on the SQL Server Instance have better configuration that the default database settings.

For non-production env

USE [master];
GO
ALTER DATABASE [model] MODIFY FILE (NAME = N'modeldev', SIZE = 50 MB,
FILEGROWTH = 50 MB);
GO
ALTER DATABASE [model] MODIFY FILE (NAME = N'modellog', SIZE = 1 MB,
FILEGROWTH = 1 MB);
GO

For production env

USE [master];
GO
ALTER DATABASE [model] MODIFY FILE (NAME = N'modeldev', SIZE = 100 MB,
FILEGROWTH = 100 MB);
GO
ALTER DATABASE [model] MODIFY FILE (NAME = N'modellog', SIZE = 10 MB,
FILEGROWTH = 10 MB);
GO

Setup SQL Server maintenance jobs

xp_cmdshell Server Configuration Option enable (details please refer to xp_cmdshell Server Configuration Option.) Create database DBA
Create table [DBA].[dbo].[DatabaseBackup] (DatabaseBackup_DDL.sql)
Create backup store procedure [DBA].[dbo].[s_BackupAllDatabases] (s_BackupAllDatabases_v11.sql)

Create Database Full Backup job (Database Full Backup job.sql) ** Before run, need to adjust @owner_login_name, backup path in @command
Create Database Transaction Log Backup job (Database Transaction Log Backup job.sql) ** Before run, need to
adjust @owner_login_name, backup path in @command

Create Database backup file housekeeping job (Database backup file housekeeping job.sql) ** Before run, need to adjust @owner_login_name, backup path in @command

The below is directory structure of file server: SQLServer_Backup

|
| — — — DB_Server_Name

|
| — — — DB_Name 1 | — — — DB_Name 2

The Naming Standard of Transaction log as below:

[DB Name]_[LOG]_[YYYY-MM-DD_hh24miss]_[log_sequence_number].log

(Optional) Backup DB to the network drive

locate the drive (Drive is S, the mount point is \\mo-mgt-snapproxy-app01.gb.laxigames.local\ssrs-backups )

Enable xp_cmdshell as its disabled

EXEC sp_configure 'advanced', 1
RECONFIGURE WITH override
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH override

GO

Map the drive via T-SQL so that SQL can see it

EXEC xp_cmdshell 'net use S: \\mo-mgt-snapproxy-app01.gb.
laxigames.local\ssrs-backups'

Test the drive is successful found by SSMS, you will find the information of drive S:

xp_cmdshell 'dir S:'

Enable OLE Automation Procedures

sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Configure Database Mail

expand the SQL Server instance via SQL Server Management studio
under Management, right click Database Mail to configure
create email profile “Email for SQL jobs” and configure SMTP server accordingly

Setup SSIS catalog maintenance jobs

Create SSISDB catalog

Open SQL Server Management Studio.
Connect to the SQL Server Database Engine.
In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
Click Enable CLR Integration.
Click Enable automatic execution of Integration Services stored procedure at SQL Server startup
Enter a password for SSISDB (ssisdb.d07h36357), and then click Ok.

Important

The catalog protects data using encryption. A key is need for this encryption. Enter a password to protect the encryption key,l and save the password in a secure location.

You can manage the encryption key by creating a backup. If you migrate or move the integration Services catalog to another SQL Server instance, you can restore the key to regain access to encrypted content.

Backup, Restore, and Move the SSIS Catalog

Change retention policy for “SSIS Server Maintenance Job”

This query shows the current SSIS catalog properties

SELECT [property_name],[property_value]
FROM [SSISDB].[internal].[catalog_properties];

The below command change the

MAX_PROJECT_VERSIONS to 3
RETENTION_WINDOW to 7 days
Server-wide Default Logging Level to “None” (0: None, 1: Basic, 2: Performance, 3: Verbose)

USE SSISDB;
EXEC catalog.configure_catalog MAX_PROJECT_VERSIONS, 3
EXEC catalog.configure_catalog RETENTION_WINDOW, 7;
EXEC catalog.configure_catalog SERVER_LOGGING_LEVEL, 0;

Change “SSIS Server Maintenance Job” schedule

In Daily frequency section, change Occurs every to 15 minutes; Starting at: 12:00 AM; Ending at: 11:59: 59 PM

Change “syspolicy_purge_history” schedule

In Daily frequency section, change Occurs once at: 10:02:00 AM

SQL Server Agent History retention configure

right click SQL Server Agent select property, click History

under Current job history log size(in row): check Limit size of job history log

Set Maximum job history log size (in rows): 10000 Set Maximum job history rows per job: 1000

Configure SQL Server Agent Alerts

expand SQL Server Agent, right click Alerts and create new alert
In General page, Specify Name as “Fail” and type as “SQL Server event alerts” In Options page, include alert error text in: check Email

Configure SQL Server Agent Operations

expand SQL Server Agent, right click Operations and create new operation In General page, Specify Name as “SQL job failure”
In Notifications page, view notification sent to this user by: select Alert
and check Email for Fail alert that in Alert list.

Configure SQL Server Agent Mail to Use Database Mail

To configure SQL Server Agent to use Database Mail
In Object Explorer, expand a SQL Server instance. Right-click SQL Server Agent, and then click Properties. Click Alert System.

Select Enable Mail Profile.
In the Mail system list, select Database Mail.
In the Mail profile list, select a mail profile for Database Mail. Restart SQL Server Agent.

Create SQL Server databases (ETL destination databases)

The below are land-base needed databases

ssis_dmms_<env> ssis_ldmms_<env>

Creating MS SQL Server account for service access and Job Agent process

we need to create 2 SQL server login ssis (for SSIS ETL processes use) and ssrs (for Reporting server access use)

password for ssis (ssis.xxxx)

password for ssrs (ssrs.xxxx)

Via Microsoft SQL Server Management Studio, In SQL Server instance, security → Logins, right click and create new login, create SQL server service account (SQL Server authentication) and follow these

Unchecked Enforce password policy Unchecked Enforce password expiration Required User Mapping:

master database, check db_datareader
msdb
database, check db_datareader
SSISDB
database, check db_owner, ssis_admin
(project-wide database) ssis_dmms_<env>, ssis_ldmms_<env>, check db_owner

SQL Server services configure

Ensure startup type, log on As setting are configured properly for below services (Run SQL Server Configuration Manager)

Important

Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.

For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for Power Pivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.
To change Reporting Services options, use the Reporting Services Configuration Tool.

For more information, please go to: Configure Windows Service Accounts and Permissions

ODBC setup For SSIS ETL tasks

Automatic Automatic Automatic Disabled Automatic Automatic

(Delayed Start)

gamesourcecloud\svc_idc_db gamesourcecloud\svc_idc_db gamesourcecloud\svc_idc_db Local Service gamesourcecloud\svc_idc_db gamesourcecloud\svc_idc_db

A 64-bit version of the Microsoft Windows operating
Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):

The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

The Odbcad32.exe file displays the following types of data source names (DSNs):

system includes the following versions of the Microsoft Open

System DSNs User DSNs

Details refer to ODBC Administrator tool displays both the 32-bit and the 64-bit user DSNs in a 64-bit version of Windows

SSAS 32 bits Windows (run odbcad32.exe under C:\Windows\SysWOW64) SSRS 64bits Windows (run odbcad32.exe under C:\Windows\System32)

SSISDB catalog index tuning

Index #1 — ncidxOperation_Id on internal.event_messages

CREATE NONCLUSTERED INDEX [ncidxOperation_Id] ON [internal].
[event_messages]
([operation_id] ASC )

Index #2 — ncidxExecution_id on internal.executable_statistics

CREATE NONCLUSTERED INDEX [ncidxExecution_id] ON [internal].
[executable_statistics]
([execution_id] ASC)

Index #3 — ncidxExecution_idSequence_id on internal.execution_component_phases

CREATE NONCLUSTERED INDEX [ncidxExecution_idSequence_id] ON [internal].
[execution_component_phases]
([execution_id] ASC,
[sequence_id] ASC)

Index #4 — ncidxOperation_id on internal.operation_messages

CREATE NONCLUSTERED INDEX [ncidxOperation_id] ON [internal].
[operation_messages]
([operation_id] ASC)

Index #5 — ncidxEvent_message_id on internal.event_message_context

CREATE NONCLUSTERED INDEX [ncidxEvent_message_id] ON [internal].
[event_message_context]
([event_message_id] ASC)
CREATE NONCLUSTERED INDEX [ncidxEvent_operation_id] ON [internal].
[event_message_context]
([operation_id] ASC)

#Index6 — idx_internal_operations_end_time_created_time

/****** To Avoid issue
Failed to execute IS server package because of error 0x80131904.
Description: The operation failed because the execution timed out.
Source: .Net SqlClient Data Provider ******/
USE [SSISDB]
GO
/****** Object: Index [internal_operations_end_time_created_time]
Script Date: 11/6/2019 16:09:02 ******/
CREATE NONCLUSTERED INDEX
[idx_internal_operations_end_time_created_time] ON [internal].
[operations]
(
[end_time] ASC,
[created_time] ASC,
[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
GO

#index7 — idx_internal_operation_created_time

/****** To Avoid issue
Failed to execute IS server package because of error 0x80131904.
Description: The operation failed because the execution timed out.
Source: .Net SqlClient Data Provider ******/
USE [SSISDB]
GO
/****** Object: Index [internal_operation_created_time] Script
Date: 11/6/2019 16:09:28 ******/
CREATE NONCLUSTERED INDEX [idx_internal_operation_created_time] ON
[internal].[operations]
(
[created_time] ASC,[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
GO

Apply the below cleanup_server_retention_window_updated.sql

Related Information

Microsoft SQL Server Version List
SQL Server 2014 build versions
xp_cmdshell Server Configuration Option
ODBC Administrator tool displays both the 32-bit and the 64-bit user DSNs in a 64-bit version of Windows

SSIS 2012 — CATALOG INDEXING RECOMMENDATIONS

SSIS Performance Tuning–Monitoring & Data Collection
SSIS Performance Tuning — Methodology and general approach

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store