SQLTeam.com | Weblogs | Forums

AOAG setup on two servers


#1

I am a system admin (OS-level - mostly unix but trying to do MS as we migrate to it), and do not have experience with MS SQL server 2014, but have been asked to install a database (mdb) and configure two SQL servers using AlwaysOn Availability Groups. My question is related to the order of the steps. Do I create the database admin user that will be administering the database on each server, attach the database I was given to each server, and then configure AOAG between the two servers' databases? Or do I only need to do this on the primary server? The two servers already have a server-level cluster in AD and I have 3 IP addresses assigned to each SQL server (1 for server access, 1 for AD cluster, and I have a third IP on each for the AOAG). I mostly understand my way around the management studio, but the instructions I have found so far involve writing and executing SQL code. Should I build the two servers independently at first with database running and then do the AOAG?


#2

The logins need to be created on all replicas, so in your case both servers. I suspect you've got logins that you need to create outside of just an admin user.

Once you've attached database on the primary replica, you can setup the AG via the wizard, which can handle creating the database on the secondary replica. I do prefer to use scripts, but I would definitely recommend using the wizard the first time.

You don't attach the database on the secondary replica. It has to be restored with at least one log backup and put into the right mode. The wizard will take care of this, or you can do this work. If you decide to try it out yourself, you would backup the database on the primary after you attached it. Run a log backup. Restore both to the secondary using the NORECOVERY option. Now you can use the wizard and specify that the database is already ready to be joined to the AG.


#3

Thank you Tara. That is exactly the information I was looking for!