When you create a new DB or for example when you install application that will create a new DB then you need to add it to the Availability Databases.
Add new database to Availability Databases
- Change the Recovery Model of the new DB to Full.
- Do a Full backup of the database.
- \\contmng0\Replicas\contdb1c0\AG\agscvmm0\VirtualManagerDB.bak
- Add Database Wizard
- Select your data synchronization preference.
- Connect existing replicas.
- Check the validation results.
- Check the results.
- The new database is synchronized now.
Verify the installation
- Using Failover Cluster Manager move the role to another node. Application (in my case System Center Virtual Machine Manager) that is using the database should not be affected.
3 responses to “Building Microsoft SQL Server AlwaysOn Availability Groups – Part 5 – Make another DB Highly-Available – Add it to Availability Databases”
Hey , first to say BIG thank you for your wonderful SQL AG tutorial. For the DB backups you use your management (contmng0) server is that correct ? That share is used only for DB backups, not for any synchronization between the nodes ? Can you also explain in more details when you created the Availability groups, what the "Readable Secondary" means ? And why in production environment is better to be "non-readable " ? Thank you again Mitko
Hi Mitko, thank you for the compliment ;-). I wrote that File Server for Full backup should be a cluster and now I have to admit that this is confusing. I wrote that because the backup server should be always highly available. First and second question You are right. contmng0 is the management server and the share is not used for synchronization. It is just a share that is accessible by all AG servers. More information on: http://msdn.microsoft.com/en-us/library/hh403415.aspx#Prerequisites And this is the paragraph you are looking for: You will need to specify a network share in order for the wizard to create and access backups. For the primary replica, the account used to start the Database Engine must have read and write file-system permissions on a network share. For secondary replicas, the account must have read permission on the network share. Third question Readable Secondary: No – that means that no direct connection is allowed to the database in the secondary replica. There is no general best practice for this configuration. This configuration is handy for example as disaster recovery (DR) solution. You should present your requirements to DB admins because they understand it in depth and they are the best for this decision. Btw. If you want to play with SQL AG then you should consider Microsoft Azure. You can for example in Trial mode deploy AlwaysOn AG image from gallery and Azure will create multiple VMs (including DC) with the best possible configuration.
Thank you , now everything is clear