SQL Server 2012 - Best Practice Analyzer

The following are required for using SQL Server 2012 Best Practices Analyzer:

• PowerShell V2.0   (Powershell 4.0 is out now)
• Microsoft Baseline Configuration Analyzer V2.0

http://www.microsoft.com/en-us/download/details.aspx?id=29302
http://www.microsoft.com/en-us/download/details.aspx?id=16475

Source: http://www.mssqltips.com/sqlservertip/2712/using-the-microsoft-sql-server-2012-best-practice-analyzer/

SQL Server 2012 - SSIS Execution Permissions

I ran into an issue where even the server administrator and sql server services accounts would not properly execute SSIS packages. I had to perform the following steps so the service account could execute a sql agent job and run a package.

Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties. On the Security tab, click Edit in the Launch and Activation Permissions area. Add users and assign appropriate permissions, and then click Ok. Repeat steps 4 - 5 for Access Permissions. Restart SQL Server Management Studio. Restart the Integration Services Service. (Source MSDN)

http://stackoverflow.com/questions/13941998/sql-server-2012-integration-services-failed-when-connecting-thru-ssms

SQL Server - Recovering TempDB Space

-- STEP 01 ALTER FILE SPECS =====================================
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE=100Mb);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=100Mb);
GO


-- STEP 02 SHRINK FILES ========================================
USE tempdb
GO
DBCC shrinkfile (tempdev, 100)
GO
DBCC shrinkfile (templog, 100)
GO


http://www.sqldbadiaries.com/2010/11/13/tempdb-please-allow-me-to-shrink-you/
http://www.dbadiaries.com/how-to-shrink-tempdb
http://support.microsoft.com/kb/307487
http://technet.microsoft.com/en-us/library/ms175527(v=SQL.105).aspx 

SQL Server Performance

http://technet.microsoft.com/en-us/magazine/2008.08.pulse.aspx?pr=blog

http://technet.microsoft.com/en-us/library/cc938593.aspx

http://sqlserverperformance.wordpress.com/2010/12/28/the-importance-of-windows-power-plans-for-server-performance/

For SQL Server Box:

1) Configure AV to ignore several key folders and files. Such own AV folder, SQL folders, SQL files, c:\pagefile.sys

2) Change power management option from balanced to performance.

3) Do not use SQL Boost.

4) Put SQL data files on separate drive.

5) Put SQL log files on separate drive, if possible.

6) Use simple database unless full logging is required.

SharePoint Log File Shrinking

Here is a real gem for recovering space taken up by log file growth. It is of course recommended that you back up any files first.

USE SharePoint_Config
GO

ALTER DATABASE SharePoint_Config SET RECOVERY SIMPLE
DBCC SHRINKFILE(N'SharePoint_Config_log', 50)

ALTER DATABASE 
SharePoint_Config SET RECOVERY FULL
GO