Pozdrav, i ja sam se namucio ali odustajanje nije bilo opcija :). Naterao sam replikaciju da konacno radi pa evo da skratim muke svima koji imaju tu srecu il nesrecu da treba da postave replikaciju na osnovu oskudnog MSDN upustva. Slede beleske koje sam pisao da samog sebe podsetim kad zatreba:
-Na DB1 napraviti entry u C:\Windows\system32\drivers\etc\hosts fajlu za DB2 i obratno proveriti koristeci "ping DB1"
-Na DB1 napraviti alias za DB2 i obratno:
pokrenuti SQL Server Configuration Manager -> SQL Native Clien Configuration -> Aliases -> New Alias...
upisati: Alias Name (DB2), Port No (8143), Protocol (TCP/IP), Server (DB2) (i suprotno za DB2)
-Na oba DB servera napraviti windows naloge DistributionAgent i SnapshotAgent
-Na SQL serveru na svakom DB serveru dodati ova dva windows naloga, sa istim siframa na obe masine i sa sledecim privilegijama:
server roles: public, sysadmin
user mapping: MyDatabase baza, role: db_owner, public
-Na SQL serveru na svakom DB serveru za sql nalog MyDatabaseUser postaviti:
server roles: public, sysadmin
-Na oba DB servera napraviti folder G:\ReplicationSnapshot i u sharing dijalogu(win server 2008) postaviti:
SnapshotAgent: Co-owner
DistributionAgent: Reader
-Oba DB servera postaviti za distributore
SQL Server Management Studio -> Replication -> Configure Distribution... i odabrati sledece opcije:
(sve opcije koje nisu navedene ostaviti na default vrednosti)
DB1 will act as its own distributor (odn. DB2)
Snapshot folder: \\DB1\ReplicationSnapshot (odn. \\DB2\ReplicationSnapshot za DB2)
Distibution database name: distribution
Folder for the distribution database file: G:\Databases
Folder for the distribution database log file: G:\Databases
-Napraviti bekap baze MyDatabase na DB1 G:\Backup i nazvati ga MyDatabase_DB1_yyyyMMdd.bak
(sluzi za ne-daj-boze, ako postoji bekap na drugom mestu onda preskociti)
-Napraviti publikaciju na DB1:
SQL Server Management Studio -> Replication -> Local Publications -> New Publication... i odabrati sledece opcije:
(sve opcije koje nisu navedene ostaviti na default vrednosti)
Publication database: MyDatabase
Publication type: Transactional publication
Objects to publish: sve tabele
Snapshot agent: Run under the following windows account: SnapshotAgent
Publication name: MyDatabase
-Setovati upravo napravljenu publikaciju da podrzava peer-to-peer rad:
SQL Server Management Studio -> Replication -> Local Publications -> MyDatabase -> Properties -> Subscription options -> Allow peer-to-peer subscriptions -> True
-Napraviti beckup MyDatabase baze na DB1 G:\Backup, nazvati ga MyDatabase_DB1_with_publication_yyyyMMdd.bak i preneti ga na DB2 na G:\Backup
-Napraviti restore MyDatabase baze na DB2 iz predhodno prenesenog backup-a G:\Backup\MyDatabase_DB1_with_publication_yyyyMMdd.bak
-Na svim tabelama u MyDatabase bazi na DB2 promeniti identity specifikaciju da glasi:
increment: 2
seed: 2
-NA DB1 SQL serveru dodati DB2 u peer-to-peer topologiju:
SQL Server Management Studio -> Replication -> Local Publications -> MyDatabase -> Configure peer-to-peer topology...
(sve opcije koje nisu navedene ostaviti na default vrednosti)
Peers -> Add SQL Server...
Server Name: DB2
Authentication: Windows authentication
Login: MyDatabaseUser
Peer Database: MyDatabase
u Log Reader Agents Security za DB2 staviti:
Run under the following windows account: DB1\DistributionAgent (odn. DB2\DistributionAgent za DB1)
Connect to the Publisher: By impersonating the proccess account
u Distribution Agent security za DB2 staviti:
Run under the following windows account: DB2\DistributionAgent (odn. DB1\DistributionAgent za DB1)
Connect to the Distributor: By impersonating the proccess account
Connect to the Subscriber: By impersonating the proccess account
ako se pojavi upozorenje "Log Reader Agent already exists" ignorisati ga
-Potvrditi da u history log-u ni za jednog agenta (ima ih 3 koji pocinju sa MyDatabase) nema gresaka
SQL Server Management Studio -> SQL Server Agent -> Jobs -> DB1-MyDatabase-1 -> View History
-Na DB2 dodati snapshot agent-a
SQL Server Management Studio -> Replication -> Local Publications -> MyDatabase -> Properties -> Agent Security -> Snapshot Agent -> Create Agent...
Live long and prosper