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

m1

m2

m3

m4

m5

m6

m7

m8

m9

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

GO

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.

4-15-2014 9-19-13 PM

4-15-2014 9-19-41 PM

4-15-2014 9-20-16 PM

4-15-2014 9-20-44 PM

4-15-2014 9-21-34 PM

4-15-2014 9-22-08 PM

4-15-2014 9-23-22 PM

4-15-2014 9-24-23 PM

4-15-2014 9-25-16 PM

4-15-2014 9-25-31 PM

4-15-2014 9-26-19 PM

4-15-2014 9-27-05 PM

4-15-2014 9-27-45 PM

4-15-2014 9-28-17 PM 4-15-2014 9-29-00 PM

4-15-2014 9-29-50 PM 4-15-2014 9-30-05 PM

4-15-2014 9-30-38 PM

4-15-2014 9-44-55 PM

4-15-2014 9-52-23 PM

4-15-2014 9-18-29 PM

Below shows SQL Server Configuration Manager 2014.  Remember to enable TCP/IP and restart the SDE and SSA – SQL database and sql server agent engines.

 

4-15-2014 10-10-04 PM

 

4-15-2014 10-11-15 PM