Transactional replication
Domail Controller
Publisher as well as Distributor (Internal Server)Subscriber (Primary Server)
What is Sql Server Replication ?
SQL Server replication is a technology for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency and integrity of the data. In most cases, replication is a process of reproducing the data at the desired targets
Step 1.
Create Users (Replication user accounts)
Active Directory Users and computers
[create a user for SQL kashif (replacing administrator with kashif)
create group with administrative previllage SQLAdmin
Add kashif to SQLAdmin
User1: ALK_snapshot
User2: ALK_logreader
User3: ALK_distributor
User4: ALK_merge
Step 2.
Add exception in Firewall (Publisher and Subscriber)
Port: tcp/ip 1433 port
Step 3.
Open Sql manager and add user kashif
Database->Security->Logins-> New Logins
Search ----> Server Roles==> Sysadmin
Step 4.
Login with Kashif (in Publisher)
Step 5.
Open SQL Manager with the primary server
Step 6.
Open subscriber database(with subscriber server name) also in publisher
Step 7.
Right click Replication-> Configure Distribution
->Next-> Check Primary/local server act as own -> Next (manually go services and apply: SQL Server Agent Start Automatically)
->Next (note down path, snapshot folder we need to share in future)
->Next ->Next(Default distribution location and name) ->Next ->Next -Next (Configure Disatribution)
->Next (Summary) Finish
[create a database with records for checking]
Step 8.
Add Users in SQL Manager (ALK_snapshot,ALK_logreader,ALK_distributor,ALK_merge)
Open Sql manager and add user kashif
Database->Security->Logins-> New Logins
Search ----> User Mapping==> select distribution,dbname, db_owner,public
Step 9.
Share Folder
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repdata
Sharing->Advance Sharing->Share this Folder->PErmissions->Add all 4 users (ALK_snapshot,ALK_logreader,ALK_distributor,ALK_merge)
Allow Full Control to all users
Add All 4 Users into security also and allow full rights
Step 10.
goto SQL MAnager
Right click Replication->Local Publications->New Publications
Next->Local(Primaryserver)->Next, select transactional publication ->Next
Select all Articles(if tables does not contain primary key will not be selected) ->Next ->Next
Select 'Create Snapshot keep this snapshot to intialize subscription' ->Next
Uncheck 'use this security settinfgs to......'
"Snapshot Agent"'Select Setings'-> run under the follwing windows account ---LocalServerNmae\ALK_snapshot (and enter same pasword) ->Ok
"Log Reader Agent"'Select Settings' -> run under the follwing windows account ---LocalServerNmae\ALK_logreader (and enter same pasword) ->Ok
->Next create the publication ->Next ----give publication name(Alkabeer) ->Finish
Step 11.
goto properties new publication created ->Publication Access List -> Add ->ALK_distributor ->Ok
Step 12.
goto properties new publication created ->New Subscription ->Next Alkabeer->Next ->Next
'Subscriber and Subscription databases' 'Add Subscriber' (Alkabeer primary/secondary server) "connect"
"<New database>" give name (alkabeer2) [check primary full] ->Next
[Connection to distributor or connection to subscriber] give name 'Alkabeer\ALK_distributor' (give same password) ->Ok -Next ->Next (select run continously)
->Next (create subscription checkbox)->Next ->Finish
Step 13.
connect subscriber from same SQLManager or from subscriber machine itself
Database->Security->Logins-> New Logins
Search ----(ALK_distribution)> User Mapping==> select distribution,dbname, db_owner,public ->Ok (refresher entire databases)
