April 2014 – SQLMint

Database Snapshot

Sample T-SQL Code for creating a database snapshot.

use AdventureWorks2012
go

sp_helpfile — used to get the logical database name needed for below

CREATE DATABASE AWSnapshot
ON (
NAME = ‘AdventureWorks2012_Data’,
FILENAME=’d:tlogAWSnapshot.ss’
)
AS SNAPSHOT OF AdventureWorks2012;

–using it
USE AWSnapshot;
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = 53;

–reverting from snapshot
RESTORE DATABASE AdventureWorks2012
FROM DATABASE_SNAPSHOT=’AWSnapshot’;

Database Mirroring

Prefered long term solution for this suggested by Microsoft is using AlwaysOn High Availablilty.

T-SQL Code for mirroring [note the port numbers have to be different.

/****** Object: Endpoint [mirroring] on Server1 Script Date: 4/27/2014 3:32:37 AM ******/
CREATE ENDPOINT [mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7575, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

/****** Object: Endpoint [mirroring] on Server2 Script Date: 4/27/2014 3:31:17 AM ******/
CREATE ENDPOINT [mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7595, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

/****** Object: Endpoint [mirroring] on Witness instance Script Date: 4/27/2014 3:30:20 AM ******/
CREATE ENDPOINT [mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7585, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

On the Mirror Server, restore a backup with NO RECOVERY

USE [master]
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N’D:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLBackupAdventureWorks2012.bak’
WITH FILE = 5,
MOVE N’AdventureWorks2012_Data’
TO N’C:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLDATAAdventureWorks2012_Data.mdf’,
MOVE N’AdventureWorks2012_Log’
TO N’D:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLDataAdventureWorks2012_log.ldf’,
NORECOVERY,
NOUNLOAD,
STATS = 5

Microsoft Big Data

Excellent training videos on Microsoft Big Data overview, implementation and infrastructure. Click here.

Below is part 1.

Installing Microsoft SQL Server 2014

Below shows a step by step install of Microsoft SQL Server 2014 which was releases on April 1st 2014. in pictures with some notes.

Notes:

For a server install, Microsoft recommends using separate domain accounts for each sql service. Below setup is the same for a server environment install, but works best for a developer workstation. Also, for a stand alone sql server, you will want to place the mdf, ldf, backup files in separate sas,san, ssd, raid configured san setup uptimally for the individual files you are storing.