After the installation of SQL Server, one of the major decisions to make is which authentication to be enabled for the connections to be served from SQL Server (Windows authentication or SQL authentication). Windows authentication is best in terms of security as login/passwords can be captured by network sniffers and tend to be less secure. Some prefer to go with mixed mode to avoid the pitfalls of either and make it convenient for the application developers.

However broadly categorized reasons for either usage are mentioned below.

SQL Authentication

connections coming from external domains

Need web applications to access like Entity frameworks, ASP.NET application etc.,

Windows Authentication

connections mostly from users belonging to a domain controller attached by Active Directory

App server and DB server are connected to the same domain

SQL Server permissions are granulated at server and database levels. This is done by the implementation of server and database roles.

In addition to the various data modelling techniques that need to be employed for creation of new datawarehousing solution, a lot of thought needs to be put on underlying storage model needed.

DW requirements mostly are different from OLTP/POS systems as most of the data can be ETL’ed from source systems.

Microsoft has a tool called as “Fast Track Core Calculator” that can be used as a reference before choosing the SAN vendor as it suggests number of cores needed, number of arrays, disk size & throughput (MB/sec).

Please download the same:

Fast Track Core Calculator v1.2

MS Reference Guide

In SSIS 2012, after a catalog is created, there are only few properties which can be modified from the GUI as shown below.
Some properties are greyed out.

Please note that Encryption algorithm name is greyed out.

Here are the steps to be followed to modify the property:

Make the SSISDB to be in single user mode

Then change the encryption type using TSQL command (using GUI wouldnt work as the database in single user mode and get this message)

Make the SSISDB to be in multi user mode

[cc lang=”sql”]
— single user
ALTER DATABASE SSISDB set single_user with rollback immediate

— change the property
USE SSISDB
EXEC catalog.configure_catalog @property_name=’Encryption_Algorithm’, @property_value=’TRIPLE_DES_3KEY’;

— multi user
ALTER DATABASE SSISDB SET MULTI_USER
[/cc]

Then verify that the catalog properties got modified for the SSISDB catalog as shown:

As we are aware that multiple SSIS packages can be deployed with project deployment model in SSIS 2012.
Also previously the packages were deployed to MSDB or filesystem. These options are present in 2012 too except that it has additional option to
deploy to integration services catalog database to have better visibility on logging.

After the creating of SSISDB catalog from SSMS, logging options needs be tweaked to avoid unnecessary load on the resources. Here are the options available and choose one based on the requirements.

Verbose : logs all the steps in the packages and it will be too noisy pretty soon.

Basic : logs everything except onprogress and oncustom events

Performance : logs only onerror

None