SQL Server Installation Checklist

This is my version of SQL Server installation check-list.

Pre-SQL Installation

 

  1. Service Account
  2. Procure all AD Service accounts. Preferably one for each SQL Server service.
  3. Register an SPN. DBAs often do not have permission to do this. Have this done by a domain admin now so it is ready to go after the installation:
  4. Add the readServicePrincipalName and writeServicePrincipalName permissions to the SQL Server service account in AD.
  5. Run the following:
    setspn -S MSSQLSvc/ServerName SQLServiceAccountName
    setspn -S MSSQLSvc/ServerName:1433 SQLServiceAccountName
    setspn -S MSSQLSvc/ServerName.root.DomainName:1433 SQLServiceAccountName
  6. Server Configuration
  7. Install the latest applicable Windows Updates. Ensure there are no pending reboots before continuing. Run Disk Defragmenter afterwards.
  8. Add the SQL Server service account to the following Local Security Policy.
  9. Replace a process level token
  10. Lock Pages in Memory (LPIM).  May not be necessary in Windows 2008 or newer. Do it anyway.

iii.    Perform Volume Maintenance Task

  1. Set the queue depth to an appropriate level. Preferably 128 or higher.
  2. Set any virus scan software to not scan any MDF, NDF, LDF, BAK or TRN files.
  3. Set all NICs, physical or VM, to full duplex.
  4. Set BIOS or VM power management to OS managed, then set to full power.
  5. Format data drives with an allocation unit size of 64k, and 8192 bytes for log drives.
    Verify using the fsutil utility: fsutil fsinfo ntfsinfo [drive letter]:
  6. Set the pagefile to 150% of the total RAM. If this is too large, then set it to 100% + 1 MB.
  7. Create local “SQL Server Admins” group, add applicable users.
  8. Add applicable users to local Administrators and RDP groups.
  9. Disk Configuration
  10. Drives and Directories – Create the following directory structure for a single SQL instance. Also consider directory structure requirements for other services, such Analysis Services.
  11. OS – C:
  12. SQL  Installation – D:

iii. Data drive(s) – E:\SQLData, add more as needed.

  1. Log drive – L:\SQLLogs
  2. Backup drive – N:\Backups
  3. TempDb drive – T:\TempDb
  4. Disk format configuration
    1. Disable Indexing on disk volumes
    2. Disable generation of 8.3 names (command: FSUTIL BEHAVIOR SET DISABLE8DOT3 1)
  • Disable last file access time tracking (command: FSUTIL BEHAVIOR SET DISABLELASTACCESS 1)
  1. Keep some space empty (let us say 15% for reference) on drive is possible
  2. Defragement the volume (Only on Filestream Data storage volume)
  3. Service Account Permissions
  4. Remove “Everyone” from non-C drives.
  5. Grant the following permission to the SQL Server and SQL Agent service accounts
  6. SQL Server
  7. D: – Full control
  8. E: – List folder contents
  9. L: – List folder contents
  10. T: – List folder contents
  11. E:\SQLData – Full control
  12. L:\SQLLogs – Full control
  13. T:\TempDb – Full control
  14. SQL Server Agent
  15. D: – Full control
  16. N: – List folder contents
  17. N:\SQLBackups – Full control

SQL Installation

  1. Features – Install the features needed for the instance. Do not install something “just in case.”
  2. Shared feature directory: D:\Program Files\Microsoft SQL Server\
  3. Shared feather directory (x86): D:\Program Files (x86)\Microsoft SQL Server\
  4. Instance root: D:\Program Files\Microsoft SQL Server\
  5. Service Accounts: add applicable AD service accounts with Automatic Startup
  6. SQL Server Bowser: Disabled if installing only one instance.
  7. Authentication Mode: Mixed, or as appropriate.
  8. Add “SQL Server Admins” local group to SQL Server Administrators
  9. Data Directories
  10. Root: E:\SQLData
  11. User database: E:\SQLData\
  12. User database logs: L:\SQLLogs
  13. Tempdb data and logs: T:\TempDb
  14. Backup: N:\Backups

Post-SQL Installation

  1. Install any SQL Server or Visual Studio service packs and updates.
  2. Check that Lock Pages in Memory is being used. Restart SQL service if not done after updates.
  3. Confirm the SPN was registered:
  4. Restart SQL service if not done after updates.
  5. The query below should return “KERBEROS” if registration was successful. Run from a remote connection:
    SELECT auth_scheme
    FROM sys.dm_exec_connections
    WHERE session_id = @@spid
  6. Added applicable users to sysadmin server role.
  7. Set Model database settings. Adjust the sizes as appropriate. Here is a good start:
  8. SIMPLE recovery
  9. Data file: 2048 MB Initial Size, 1024 MB autogrowth, unlimted size
  10. Log file: 1024 MB Initial Size, 512 MB autogrowth, unlimited size
  11. Move .mdf and .ldf if necessary.
  12. Set master and msdb database settings. Adjust the sizes as appropriate. Here is a good start:
  13. SIMPLE recovery
  14. Data file: 1024 MB autogrowth, unlimted size
  15. Log file: 512 MB autogrowth
  16. Set tempdb database settings. Use Glenn Berry’s suggested settingsfor a starting point.
  17. SIMPLE recovery
  18. Add additional data files as appropriate:
    Logical name = tempdev2, File name = tempdb2.ndf.
  19. All tempdb data files: 4096 MB Initial Size, 1024 MB autogrowth, unlimted size
  20. Log file: 1024 MB Initial Size, 512 MB autogrowth, unlimited size
  21. Set trace flag -T1118. This helps elevate contention in tempdb. There is some debate on this, but it looks like it does not hurt to have it on. Paul Randal’s blog goes into detail.
  22. Enable the DAC. (sp_configure ‘remote admin connections’, 1)
  23. Set Optimize for Ad hoc Workloads to True
  24. Set Min and MAX Server Memory to an appropriate level. Use Glenn Berry’s suggested settingsfor a starting point.
  25. Set Cost Threshold for parallelism as appropriate.
  26. Set Max Degree Parallelism as appropriate.
  27. Setup database mail.
  28. Create Administrator Operators.
  29. Enable Alert System in SQL Agent using the profile just created.
  30. Enable fail-safe operator for notification types.
  31. Configure SQL Error Log retention for 31 log files, or whatever is appropriate.
  32. Create job to cycle error logs: sp_cycle_agent_errorlog, sp_cycle_errorlog.
  33. Configure alerts for severity 16 through 25 as well as specific alerts for 823, 824 and 825 errors.
  34. Confirm SQL Server service account is NOT in the local Administrators group.
  35. Install Brent Ozar’s sp_blitz. Address issues.
  36. Install and MS Baseline Config Analyzer and SQL Server 2012 BPA on your workstation, run. Address issues.
  37. Install Ola Hallengren’s Maintenance Solution
  38. Setup backups
  39. Setup integrity checks
  40. Setup index maintenance
  41. Setup history maintenance

 

Advertisements
Posted in Uncategorized | Tagged , , | Leave a comment

List of new changes in SQL2014 SP1

Microsoft has release SQL Server 2014 Service Pack 1 on April 15 and recalled very shortly after going live on the Microsoft Download Center (only) to address a recall issue.  After resolving that issue the new release is now available –KB3058865.

Here are the couple of changes that you will experience after patching.

01. Access violation occurs on the I/O Completion routine when you enable AutoClose in SQL Server 2014

02. Error when you add a custom connection manager in SSIS 2014 Designer

03. Rollback causes database to go to suspect mode in SQL Server 2012 or SQL Server 2014

04. Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012 or 2014

05. SSAS 2012 logging feature improvement in SQL Server 2012 Service Pack 2

06. New DMF sys.dm_fts_index_keywords_position_by_document in SQL Server 2012 SP2 and SQL Server 2014 SP1

07. New log files are not created when you restart the Reporting Services Windows Service in SSRS on January 1

08. “Database ‘model’ cannot be opened” error when you restart SQL Server after tail-log backup for model database

09. Event ID 22 or error 0xC1000016 will be logged when you use the Discover command in SSAS 2012

10. File Share subscription doesn’t populate the description column in SSRS 2012 or SSRS 2014

11. The “Name” drop-down list is empty when a report is specified in SSRS 2012 or SSRS 2014

12. Can’t connect to the server when you run a cube creation MDX query in SSAS 2012 or SSAS 2014

13. SQL Server crashes when the log file of tempdb database is full in SQL Server 2012 or SQL Server 2014

14. Image is not displayed correctly in the webpage when you use DrillThrough action on an SSRS 2012 or 2014 report

15. Text truncated when you use HPB renderer to view a report in SSRS 2012 or in SSRS 2014

16. Rollback recovery on a snapshot fails when you run DBCC CHECKDB and then SQL Server shuts down unexpectedly

17. Incorrect data returned when you use DATE data type as a qualifier in a query in SQL Server 2014

18. Non-yielding error occurs when you execute DML statements in SQL Server 2014

19. Error 17066 or 17310 during SQL Server startup

20. Non-yielding scheduler issue when you write pages to buffer pool extension file in SQL Server 2014

21. OS error 665 when you execute DBCC CHECKDB command for database that contains columnstore index in SQL Server 2014

22. Duplicate sequence value is generated when you run sp_sequence_get_range in parallel with NEXT VALUE FOR function

23. DBCC CHECKDB and DBCC CHECKTABLE take longer to run when SQL CLR UDTs are involved in SQL Server 2012 or SQL Server 2014

24. SOS_CACHESTORE spinlock contention on system table rowset cache causes high CPU usage in SQL Server 2012 or 2014

25. AlwaysOn availability groups are reported as NOT SYNCHRONIZING

26. Error 8646 when you run DML statements on a table with clustered columnstore index in SQL Server 2014

27. Issues when you enable the buffer pool extension feature in SQL Server 2014

28. Query performance issues when new cardinality estimator is enabled in SQL Server 2014

29. Consuming reports on a remote farm does not work in SharePoint Integrated for a published SSRS 2014 Service application

Posted in Uncategorized | Tagged , , , | Leave a comment

Nostalgia 01 (Fastest way to copy a database)

I can still remember, when I was a beginner, I used to take backup of my databases in most unconventional way, probably the fastest way to copy an image of a database. This is a big confession; still I like to share this to novice DBAs. Sometimes it can be a lifesaver. So, this is how I used to do it.

Step 01: Shutdown the Database

Offline

Step 02: DETACH DATABASE

Detach

Step 03: Copy the Database

Step 04: Paste the Database

Step 05: Rename the Database

Step 06: Attach Database

Attach

 

Get the total script here.

 

USE [MASTER]
GO
ALTER DATABASE TSQL2012
SET OFFLINE WITH ROLLBACK IMMEDIATE

USE [MASTER]
GO
EXEC master.DBO.SP_DETACH_DB  @DBNAME = ‘TSQL2012’
GO

USE [MASTER]
GO
CREATE DATABASE [TSQL2012] ON
(FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.NIRVANA\MSSQL\DATA\TSQL2012_COPY.MDF’),
(FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.NIRVANA\MSSQL\DATA\TSQL2012_LOG_COPY.LDF’)
FOR ATTACH
GO

 

Posted in Uncategorized | Tagged | Leave a comment

Types of Table Expressions in TSQL

Table expressions are named queries. We write an inner query that returns a relational result set, name it, and query it from an outer query.

T-SQL supports four forms of table expressions

  • Derived Tables
  • Common Table Expressions (CTEs)
  • Views
  • Inline Table-Valued Function

Upper tows are visible only to the statement that defines them and the last twos are database objects; therefore, these are reusable. Just like any other database objects these can be access controlled. Table Expressions are part of relational database, so it follows all rules of set theory, means that all columns returned by the inner query must have unique names. Also, the inner query is not allowed to have an ORDER BY clause as set has no order. But there is an exception of the last rule: If we use TOP or OFFSET-FETCH option in the inner query, the ORDER BY serves a meaning that is not related to presentation ordering; rather, it’s part of the filter’s specification. So if the inner query uses the TOP or OFFSET-FETCH option, it’s allowed to have an ORDER BY clause as well. But then the outer query has no presentation ordering guarantees if it doesn’t have its own ORDER BY clause.

Derived Tables

A derived table is a form of table expression which is very close to a subquery—only a subquery that returns an entire table result. Derived tables are inner query in parentheses in the FROM clause of the outer query.

Difference between Derived tables and Subqueries:

– Derived Tables are used in the FROM clause

– Subqueries are used in the WHERE clause, but can also be used to select from one table and insert into another table).

Image

Image

Common Table Expression

CTE is a named table expression that is visible only to the statement that defines it. A common table expression (CTE) is similar to derived table. A query against a CTE involves three main parts:

The inner query

  • The name you assign to the query and its columns
  • The outer query

Unlike derive table the arrangement is quite different. With CTEs, you first name the CTE, then specify the inner query, and then the outer query—a much more modular approach.

WITH <CTE_name>
AS
(
<inner_query>
)
<outer_query>;

Image

Image

Views and Inline Table-Valued Functions

Both views and Inline Table-Valued Functions are database objects. Both has existence after the query execution. Views are commonly used for data security restriction while Table-Valued Functions are users for returning tables. But the main differences between these two are,

  • Table-Valued Function one can be parametrized but not indexed
  • Views can be indexed but not parametrized

View Example:

Image

Image

Table-Valued Functions Example:

Image

Image

 

Posted in Uncategorized | Tagged , | Leave a comment

Optional parameters in a T-SQL stored procedure

Sometimes it’s really important to use optional parameter in Stored Procedure. For a beginner, it’s definitely a helpful if he/she practices optional parameters in Stored Procedure. For illustration the scenario here is an example.
USE TSQL2012

CREATE PROCEDURE GET_EMP_SP (
@FirstName    VARCHAR (20) = NIULL,
@LastName    VARCHAR (20) = NIULL,
@Title        VARCHAR (20) = NIULL
) AS

set nocount on

BEGIN
Select * from [HR].[Employees] WHERE
(FirstName = ISNULL(@FirstName, FirstName) OR COALESCE(@FirstName, FirstName, ”) = ”)
OR (LastName = ISNULL(@LastName, LastName) OR COALESCE(@LastName, LastName, ”) = ”)
OR (Title = ISNULL(@Title, Title) OR COALESCE(@Title, Title, ”) = ”)

END
set nocount off

GO

EXEC GET_EMP_SP ‘Sara’

Posted in Uncategorized | Tagged , | Leave a comment

Difference Between Stored Procedures and User Define Functions

SPs and UDFs are two most important database objects used every day by thousands of developers round the world. More than that SQL Server itself uses these two database objects very frequently. There are some fundamental differences between these two. Here are some major differences between these two:

 

No Stored Procedures Functions
1 Can be used to read and modify data. Can only read data, cannot modify the database.
2 To run an SP Execute or Exec is used, cannot be used with SELECT statement. Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
3 Cannot JOIN a SP in a SELECT statement. Can JOIN a UDF in a SELECT statement.
4 Can use Table Variables as well as Temporary Tables inside an SP. Cannot use a Temporary Table, only Table Variables can be used.
5 Can create and use Dynamic SQL. Cannot use a Dynamic SQL inside a UDF.
6 Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP. Cannot use transactions inside a UDF.
7 Can use used with XML FOR clause. Cannot be used with XML FOR clause.
8 Can use a UDF inside a SP in SELECT statement. Cannot execute an SP inside a UDF.
9 Cannot be used to create constraints while creating a table. Can be used to create Constraints while creating a table.
10 Can execute all kinds of functions, be it deterministic or nondeterministic. Cannot execute some nondeterministic builtin functions, like GETDATE().
Posted in Uncategorized | Tagged | Leave a comment

What’s the Difference between sendRedirect() and Forward tag?

Basically sendRedirect() and <jsp:forward…> does the same task. Both of them redirect the page to a particular address. Still there is a small difference.

Forward tag is used when you like to keep the visitor in the same application. But when you like to take him to another page which is not included in your application you should use sendRedirect().

The funda is when we are using the sendRedirect() we are sending the control to the redirected page. Where as using <jsp:forward…> we are just browsing another page keeping the control.

Posted in JSP | Leave a comment