Personal Notes of Edwin Eu

Archive for the ‘Microsoft SQL Server’ Category

Server Configuration

In Microsoft SQL Server on 06/29/2012 at 2:26 PM

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

SQL Server – How to change SQL Server 2008 R2 database port

In Microsoft SQL Server on 06/15/2012 at 3:16 PM

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.

Database backup

In Microsoft SQL Server on 06/12/2012 at 3:08 PM

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

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

SQL Server – Create a database

In Microsoft SQL Server on 06/12/2012 at 9:00 AM

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

SQL Server – Restore a database

In Microsoft SQL Server on 06/12/2012 at 8:55 AM

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)

In Microsoft SQL Server on 06/12/2012 at 8:42 AM

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

How to setup Database Mail in Microsoft SQL Server 2008 R2.

In Microsoft SQL Server on 02/24/2012 at 11:33 AM

 

How to setup Database Mail in Microsoft SQL Server 2008 R2.

  1. Enable and configure Email
  2. Create Profile and Account
  3. Configure Email.

USE master
GO

EXEC sp_configure ‘show advanced’, 1
GO
RECONFIGURE
GO

EXEC sp_configure ‘Database Mail XPs’, 1
GO

RECONFIGURE WITH OVERRIDE
GO

1. Open SQL Server Management Studio –>Expand Management Node —> Right click Database Mail —> Select Configure Database Mail.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click Add

 

 

 

 

 

 

Click Next

 

 

 

 

 

 

Click Next

 

 

 

 

 

 

Click Next

 

 

 

 

 

 

Click Finish to complete the setup

How to find SQL Server Recovery Model

In Microsoft SQL Server on 02/21/2012 at 4:33 PM

T-SQL script find SQL Server Recovery Model

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model]
FROM sys.databases
GO

 

Enabling CLR Integration

In Microsoft SQL Server on 06/08/2011 at 8:36 AM

The common language runtime (CLR) integration feature is off by default. The CRL must be enabled in orde to use objects that are implemented using CLR integration.

To enable CLR integration

Sp_configure ‘show advanced options’, 1;
GO

RECONFIGURE;
GO

Sp_configure ‘clr enabled’, 1;
GO

RECONFIGURE
GO

 

SharePoint 2010 Central Administration

In Microsoft SQL Server, SharePoint 2010 on 05/11/2011 at 4:02 PM

SharePoint 2010 Central Administration

1.   Central Administration

2.   Application Management

3.   System Settings

4.   Monitoring

5.   Backup and Restore

6.   Security

7.   Upgrade and Migration

8.   General Application Settings

9.   Configuration Wizards