3

I'd like to programmatically (T-SQL scripts) create a new database, and then add it to an existing Always On Availability Group (AOAG).

So far, I'm ok with creating the database, creating a full back-up (which is a pre-requisite for adding a database to AOAG) and to add it to the existing AOAG.

However, I don't know how to initialize the initial replication to the secondary database servers.

Here's my script so far: 

CREATE DATABASE Test;
GO

USE Test;
GO

BACKUP DATABASE Test
TO DISK = 'D:\Sync\Test.Bak'
   WITH FORMAT,
      MEDIANAME = 'D_Sync',
      NAME = 'Initial Full Backup for restore in AOAG group';
GO

USE Master
GO

ALTER AVAILABILITY GROUP AOAG_APP ADD DATABASE Test; 
GO

If I was using MS SQL Server Management Studio, I would use the wizard and in the Select Data Synchronization options, I would select Full, and then the path to the back up files.

How can I achieve the same with a SQL script? It doesn't seem the ALTER AVAILABILITY GROUP <group> ADD DATABASE <database> has the options to specify Full and the path to backup files.

Any help would be much appreciated!

Alex Sanséau
  • 8,250
  • 5
  • 20
  • 25
  • You may also want to post this question over on http://dba.stackexchange.com/ – G B Mar 10 '15 at 09:48
  • I don't know much about but this link might have what you're looking for https://msdn.microsoft.com/en-us/library/ff877941.aspx – Stephan Mar 10 '15 at 14:09
  • Learn this the easy way: do it through the wizard and then hit the "script" button on the top of the window instead of the "OK" button to apply the change. – Ben Thul Mar 10 '15 at 14:38
  • Thanks guys for your recommendation. I'll think about dba.stackexchange.com next time. @Ben, that's what I did in the end and it made me realize it can't be done the way I wanted it (i.e. executed as part of our deployment scripts (Octopus Deploy + RoundhousE)) - the script must be run in SQLCMD MODE, so it can connect to the different servers. With additional complexity that the script needs to know about the AOAG topology, which might change over time. – Alex Sanséau Mar 11 '15 at 09:18

1 Answers1

4

You can try with the code below.

--On primary node

create database test
alter database test set recovery FULL
backup database test to disk='Z:\Backups\Test Folder\test.bak'
backup log test to disk='Z:\Backups\Test Folder\test.trn'
alter availability group availablitygroup_name
add database [test];

--On secondary node

Restore database test from disk='\\node1\Backups\Test Folder\test.bak' with norecovery
restore log test from disk='\\node1\Backups\Test Folder\test.trn' with norecovery
alter database test set HADR availability group= availablitygroup_name

--On primary node

use test
go
create table abc(name varchar(15))
Insert into abc values('amarnath')
select * from abc

--On secondary node

use test
go
select * from abc
peter_the_oak
  • 3,529
  • 3
  • 23
  • 37
Amarnath Khandimalla
  • 56
  • 1
  • 1
    I've marked it as the accepted answer, as this seems to be the only way of doing it at the time of writing. However, I was looking for a solution where one doesn't need to login to different servers (and therefore need to know about the AOAG topology - we have AOAG with 2 replicas, other with 4). I wish there was a solution more like Dropbox, where you can add a new database and it will be replicated on other replicas, like Dropbox would copy new files to all devices, without you having to know about the other devices. @Microsoft, if you're listing, this is a feature request for you. – Alex Sanséau Mar 11 '15 at 09:07