Archive for the ‘MS SQL’ Category

What is MSSQL NTFS User

Some times We need to give MS SQL User Permissions to a Certain folder .  The MS SQL User Usually is

NT Service\MSSQL$InstanceName

Example

NT Service\MSSQL$MSSQLSERVER

NT Service\MSSQL$SQLEXPRESS

 

Setup Replication or Disaster Recovery Of MS SQL Express Edition Databases

MS SQL Express Edition does not have the feature of Log-shipping like the Standard or Enterprise Edition . So if you want to setup Replication for MS SQL Express  Edition you would need to use SQL Script to backup up databases to the remote server and then another SQL Script to restore the database on the remote server .

Here is One way which I have tried and it is working successfully  .  The Environment has two servers at remote location from each other , both the servers have MS SQL 2008 Express Edition installed on the Servers .  On the Primary Server I have used the following script to backup all databases of Primary Server on the secondary server to a Folder configured as a Network Share .

DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
— DECLARE @fileDate VARCHAR(20) — used for file name
— specify database backup directory
SET @path = ‘\\Network_Share_folder\db_backup_folder\’

— specify filename format
— SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) — exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName WITH INIT
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Create a .bat file to Execute the SQL query and set it in task scheduler

sqlcmd -S INSTANCENAME  -i “C:\dbscripts\Backup_Databases.sql”

This Script will generate backups without time stamp and will overwrite the backup files every time it is executed .  Now the Question comes of restoring the databases on the secondary server .  You can use the below script to restore the databases on secondary Server

restore database DBNAME
from disk =’D:\DB_BACKUP\DBNAME.bak’
With REPLACE

restore database DBNAME1
from disk =’D:\DB_BACKUP\DBNAME1.bak’
With REPLACE

restore database DBNAME2
from disk =’D:\DB_BACKUP\DBNAME2.bak’
With REPLACE

Create a .bat file to Execute the SQL query and set it in task scheduler

So you are done with the Replication or Disaster Recovery  of MS SQL Databases with MS SQL Express Edition .

Happy Learning :)

MS SQL 2014 Failover Cluster Steps with Windows 2012 Operating System

Prerequisites:

a)      Minimum three servers required, one will be Active Directory and the other two acting as failover Cluster nodes.

b)     Two NICs are required on both the database nodes one for Cluster and other for heartbeat

c)      Activate OS license on the Active Directory Server before Active directory is installed as active directory does not allow activating license once it is installed.

d)     Apply windows updates on all the three servers. Try keeping the windows updates level same on both the nodes  as it can cause some issues while  validating the failover  cluster

e)      2 LUNS should be assigned on both the Nodes; the LUN with lower size is used as the Quorum Disk automatically by the failover Cluster.

f)       Format the LUNs and make it Online only on one Server.

g)      Install the failover over Cluster feature from add/remove roles and features on both the nodes

h)     Install donet framework 3.5 on both the nodes. Without Dotnet framework 3.5, SQL Installation gives error.

 INSTALLATION STEPS

1)     Install Active Directory on One Server. Set the Hostname.

2)     On the Active Directory Server , In Active Directory Users and Computers , Right Click Computers  , New computer, and Create New Computer. View Advanced , Features , Right Click Computers, Security Tab and give administrator create Computer Rights.

3)     Rename the NICs on both the database nodes as Public and Private

4)     On the two database nodes in Public LAN , For DNS Server , Add the IP of  Active Directory Server  , Enable NetBIOS  Over TCP/IP , In Private LAN Disable “Enable NetBIOS Over TCP/IP”

5)     On both the Nodes  in hosts file add  ” ACTIVE DIRECTORY HOSTNAME  IP ADD of AD ”

6)     Make both the servers member of domain controllers, reboot both the servers and Active Directory server.

7)     ON both the Database Servers Install Failover Cluster Service Role .

8)     Login to one DB Server with Administrator user as  a member of domain controller , goto  failover cluster manager => validate a configuration => Next => Browse => advanced => find now and select DB1 and DB2  => next and run the test with “Run all the tests ( recommended ) option “ .

9)     Once the test passes, Click create a cluster, Next , Access Point for Administering the Cluster , and set the Virtual hostname  and Virtual  IP of  OS Cluster and complete the cluster wizard . That’s it you have completed the OS cluster. Now next you need to install the MSSQL cluster.

10)  Run the MSSQL 2014 setup on DB1 node and select the option “ New SQL Server   Failover Cluster Installation” and complete the setup on one node

11)  Run the MSSQL 2014 setup on DB2 Node and select the option “Add node to a SQL Server Failover Cluster” and complete the setup on the second node. That’s it you have completed MSSQL 2014 failover Cluster setup.  Happy Learning  :)

(adsbygoogle = window.adsbygoogle || []).push({}); //--> Feedjit Flag Counter