I wanted to keep two good references handy in case needed.

First resources is SQL Authority with Pinal Dave.

http://blog.sqlauthority.com/2007/05/15/sql-server-dbcc-commands-list-documented-and-undocumented/

The second resources is MSDN library

http://msdn.microsoft.com/en-us/library/ms188796

I’m very impressed with an article that I found on SQL Server Pro.

http://www.sql-server-pro.com/sql-server-configuration.html

The article discussed how to installing and configuring Microsoft SQL Server  after completed  the software installation.

In addition, the article discussed and explained detail on each SQL Server Configuration components.

• Affinity Mask
• Affinity IO Mask
• Cost Threshold for Parallelism
• Fill Factor
• Index Create Memory
• Locks
• Max Degree Of Parallelism
• Max Worker Threads
• Min and Max Server Memory
• Min Memory Per Query
• Piority Boost
• Recover Invertal
• Disk Configuration
• Startup Options

To view SQL Server Configuration:

Method 1:

EXEC SP_CONFIGURE ‘Show Advanced Options’, 1

GO

RECONFIGURE

GO

EXEC SP_CONFIGURE

GO

Method 2:

SELECT * FROM  SYS.CONFIGURATIONS

GO

Over the years, I learned that Microsoft SQL Server product has TCP/IP port configured to 1433 by default.    I strongly advised to change the SQL Server TCP/IP port to unique port number.  In addtion, It’s Microsoft Best Practice advised SQL Server database’s TCP/IP port to be changed.    Enclosed are the procedures how to change Microsoft SQL Server 2008 R2 database port.

1. Go to SQL Server Configuration Manager –> Select Protocols for SQL01
2. Right Click the TCP/IP –> Then go to Properties
3. Click on IP Addresses –> Scroll down to TCP Port
4. Then changed the TCP Port to unique number.

Microsoft SQL Server database backup in full recovery model consisted of:

• Full Database Backup
• Differential Database Backup
• Log Backup.

How to create a database

CREATE DATABASE TestDB ON PRIMARY
 (NAME = N’TestDB_Data’, FILENAME = N’M:\SQL_Data\TestDB.mdf’,
  SIZE = 8MB, MAXSIZE = UNLIMITED, FILEGROWTH = 16MB),
 FILEGROUP FG1
 (NAME = N’TestDB_Data2′, FILENAME = N’M:\SQL_Data\TestDB.ndf’,
  SIZE = 8MB, MAXSIZE = UNLIMITED, FILEGROWTH = 16MB),
 FILEGROUP Documents CONTAINS FILESTREAM DEFAULT
 (NAME = N’Documents’, FILENAME = N’M:\SQL_Data\TestDBDocuments’)

LOG ON
 (NAME =N’TestDB_Log’, FILENAME = N’L:\SQL_Log\TestDB.ldf’,
 SIZE = 8MB, MAXSIZE = 2048GB, FILEGROWTH = 16MB)
GO
ALTER DATABASE TestDB
MODIFY FILEGROUP FG1
DEFAULT
GO

Recently I was assigned to move the Dynamics SQL Server production database into a development environment.  In addition, I setup , install, and configure the Microsoft DYNAMICS.

• Installed, Setup, Configure Window Server 2008 R2
• Jointed the Window Server 2008 R2 to Domain Controller
• Installed, Setup, and Configured Microsoft Dynamics 2009
• Installed, Setup, and Configured Microsoft SQL Server 2008 R2
• Copy the DYNAMICS production backup
• Then, restored the database:

 — 03/22/2012 RESTORE AX2009-LIVE and CONVERT INTO AX2009-DEV
 — RESTORING DYNAMIC AX2009-LIVE DATABASE DATED 3/21/2012 12:00 AM
 RESTORE DATABASE [AX2009-DEV]
 FROM DISK = ‘C:\Temp4All\AX2009-Live Backup\AX2009-Live_backup_2012_03_21_010001_8246391.bak’
 WITH MOVE ‘AX2009-Dev’ TO ‘F:\SQL_Data\AX2009-Dev.mdf’,
 MOVE ‘AX2009-Dev_Log’ TO ‘G:\SQL_Log\AX2009-Dev.ldf’,
RECOVERY
GO
—- RESTORING DYNAMIC AX2009-LIVE LOG DATED 3/21/2012 4:00 AM
RESTORE DATABASE [AX2009-DEV]
 FROM DISK = ‘C:\Temp4All\AX2009-Live Backup\AX2009-Live_backup_2012_03_21_050001_5114638.trn’
 WITH MOVE ‘AX2009-Dev’ TO ‘F:\SQL_Data\AX2009-Dev.mdf’,
 MOVE ‘AX2009-Dev_Log’ TO ‘G:\SQL_Log\AX2009-Dev.ldf’,
NORECOVERY
GO

—- RESTORING DYNAMIC AX2009-LIVE LOG DATED 3/21/2012 5:00 AM
RESTORE DATABASE [AX2009-DEV]
 FROM DISK = ‘C:\Temp4All\AX2009-Live Backup\AX2009-Live_backup_2012_03_21_060001_7013047.trn’
 WITH MOVE ‘AX2009-Dev’ TO ‘F:\SQL_Data\AX2009-Dev.mdf’,
 MOVE ‘AX2009-Dev_Log’ TO ‘G:\SQL_Log\AX2009-Dev.ldf’,
NORECOVERY
GO

—- RESTORING DYNAMIC AX2009-LIVE LOG DATED 3/21/2012 6:00 AM
RESTORE DATABASE [AX2009-DEV]
 FROM DISK = ‘C:\Temp4All\AX2009-Live Backup\AX2009-Live_backup_2012_03_21_070001_5084996.trn’
 WITH MOVE ‘AX2009-Dev’ TO ‘F:\SQL_Data\AX2009-Dev.mdf’,
 MOVE ‘AX2009-Dev_Log’ TO ‘G:\SQL_Log\AX2009-Dev.ldf’,
NORECOVERY
GO

—- RESTORING DYNAMIC AX2009-LIVE LOG DATED 3/21/2012 7:00 AM
RESTORE DATABASE [AX2009-DEV]
 FROM DISK = ‘C:\Temp4All\AX2009-Live Backup\AX2009-Live_backup_2012_03_21_080001_9615089.trn’
 WITH MOVE ‘AX2009-Dev’ TO ‘F:\SQL_Data\AX2009-Dev.mdf’,
 MOVE ‘AX2009-Dev_Log’ TO ‘G:\SQL_Log\AX2009-Dev.ldf’,
RECOVERY
GO

SQL Server  – Common Table Expression (CTE)

• Common Table Expression (CTE) was implemented in SQL Server 2005
• CTE are not design to be a replacement of the Temp Table
• CTE declare the name at the beginning of code  with keyword “WITH EmpSort_CTE AS”
• CTE exampleWITH EmpSort AS
  

 (SELECT FirstName, LastName, EmpID, LocationID,
 ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS Position
 FROM Employee)
SELECT * FROM EmpSort
GO