April 2, 2017 at 8:39 am
Hi Guys,
Seeking advise.
1. Once AG is setup with automatic seeding, whenever i create a database in Primary, i need to perform the below in order to initialize/sync it over to the secondary.ALTER AVAILABILITY GROUP <AG GROUP> ADD DATABASE <DBNAME>;
Do i first need to do a full backup first as prereq? As understand that if using manual initialize in earlier version,
it will first check that the DB has been at least fill backup once before we can proceed to next page.
2. This new feature is good as it ease the procedure to initialize the DB. For DBs that can be automatically
created without DBA's knowledge at anytime (eg, sharepoint site by end-users), how do u guys usually handle this? To
send an alert whenever a new DB is created for DBA to perform initialization?
thanks for sharing in advanced!
* will be trying out direct seeding soon. just trying to understand for now.
April 3, 2017 at 4:51 pm
1. Databases in AG must be in full recovery model. Newly created databases are in pseudo-simple until a full backup is taken. So, yes you need to take a full backup + log backup before you can add the database into AG via automatic seeding. This prerequisites is documented @ (https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group)
2. Automatic seeding eases the process of adding databases into AG, but I personally don't think this should be automated. It can be easily automated via means of SQL Agent jobs, but I wouldn't. Newly databases added into production server should be accounted i.e. owner, maintenance, backup, monitoring, etc. Having databases added without DBA knowledge can quickly go out of control especially in terms of administration.
FYI, You're also required to grant permission to create database on all secondaries
ALTER AVAILABILITY GROUP [agname] GRANT CREATE ANY DATABASE;
GO
Simon Liew
Microsoft Certified Master: SQL Server 2008
April 4, 2017 at 1:31 am
I agree that automating it could be a bad idea, because there's also the issue of direct seeding potentially blocking your backup jobs, as described here .
From the link:
If your full or differential jobs hit a database while Direct Seeding is doing the initial sync, they’ll be blocked until it finishes. This can really mess with RPO and RTO for your other databases, especially if it takes a while to sync, or you’re syncing a bunch of databases with Direct Seeding, and the backup job keeps getting stuck behind each database synchronization.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply