In the field, I usually do not see dedicated networks for endpoint traffic so I wanted to take this a step further and monitor with some of the new extended event events for automatic seeding. Especially, when nothing else is utilizing the dedicated endpoint network. Normally, just looking at bytes sent and bytes received will be good enough for you to measure your throughput. The first thing I would look at for benchmarking throughput for network activity would be the bytes sent per second from the primary replica and bytes received per second on the secondary replicas.ĪG Seed VLDB Secondary Replica – Bytes Received per Second This makes a lot of sense as it caps out around what the storage system can deliver in this environment. I noticed 1.4 Gbps of consistent throughput during the seeding process. The 600 GB databases took about 66 minutes to seed across the network from a primary replica to the secondary replica. Where object_name like AS AS AS AS AS AS AS AS / AS AS AS AS AS AS AS AS AS ĬROSS APPLY EventXml.nodes('//event') as t1(c1) DECLARE VARCHAR(300) = 'S:\MSSQL13.MSSQLSERVER\MSSQL\Log\autoseed*' We will talk about the results in the results section below. The following T-SQL script is then used to read the results once your seeding process has completed. MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB, WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, CREATE EVENT SESSION ON SERVERĪDD EVENT sqlserver.hadr_automatic_seeding_state_transition,ĪDD EVENT sqlserver.hadr_automatic_seeding_timeout,ĪDD EVENT sqlserver.hadr_db_manager_seeding_request_msg,ĪDD EVENT sqlserver.hadr_physical_seeding_backup_state_change,ĪDD EVENT sqlserver.hadr_physical_seeding_failure,ĪDD EVENT sqlserver.hadr_physical_seeding_forwarder_state_change,ĪDD EVENT sqlserver.hadr_physical_seeding_forwarder_target_state_change,ĪDD EVENT sqlserver.hadr_physical_seeding_progress,ĪDD EVENT sqlserver.hadr_physical_seeding_restore_state_change,ĪDD EVENT sqlserver.hadr_physical_seeding_submit_callbackĪDD TARGET package0.event_file(SET filename=N'autoseed.xel',max_file_size=(20),max_rollover_files=(4)) We will talk about others in a future blog post. We will focus on the “hadr_physical_seeding_progress” event today. I also configured the following extended event session to monitor seeding activity on the primary and secondary replicas. Bytes Received/sec on Secondary replicas.
I configured the following performance monitor counters. Nothing else is active on the network or the replicas during the time of testing. This availability group has been configured with a separate 10Gbps network dedicated to endpoint traffic. Even if you decide to not use automatic seeding I recommend testing this feature as automatic seeding can be a great way to stress your endpoint network to validate its throughput. There is also a bonus feature of automatic seeding for DBA’s and Information Technology professionals. It will also join the database on the replicas for you.
#ANY LARGE DATABASE NEEDS A GOOD SEARCH DEFAULTS FULL#
This eliminates the need to manually take full and log backups from the primary replica to all the secondary replicas.
Automatic seeding utilizes a VDI Backup to take a copy only backup and send it over the endpoint network to seed the replicas and then join the databases with the replicas. It’s how the initial data synchronization occurs for Geo-Replication of Azure SQL Databases. Here is how I added a 60TB (Yes, TB not GB) database to an availability group that utilized multiple data centers.Īutomatic seeding is a feature that has been in Azure SQL Databases for a while. Personally, I have been a big fan of log shipping for the initial data synchronization of VLDB’s especially when you need more than two replicas. In the past, data initialization has been done with mirroring, log shipping, backup, and restores. When you add a database to an availability group, the replicas must synchronize the data between the availability groups to join the database on the replicas. The initial data synchronization easy button.
0 kommentar(er)
