{"id":2329,"date":"2020-05-27T10:56:41","date_gmt":"2020-05-27T10:56:41","guid":{"rendered":"https:\/\/www.tiagoneves.net\/blog\/?p=2329"},"modified":"2020-05-27T10:56:44","modified_gmt":"2020-05-27T10:56:44","slug":"alwayson-como-configurar-leitura-backup-e-checkdb-na-replica-secundaria","status":"publish","type":"post","link":"https:\/\/www.tiagoneves.net\/blog\/alwayson-como-configurar-leitura-backup-e-checkdb-na-replica-secundaria\/","title":{"rendered":"AlwaysOn &#8211; Como configurar leitura, backup e checkdb na r\u00e9plica secund\u00e1ria"},"content":{"rendered":"\n<p>Hey guys tudo certo?<\/p>\n\n\n\n<p>No post de hoje vou compartilhar um pouco mais sobre o <strong>AlwaysOn<\/strong>.<\/p>\n\n\n\n<p>Nos dois \u00faltimos posts mostrei como realizar a configura\u00e7\u00e3o do AlwaysOn no SQL Server e como podemos utilizar o Snapshot para fazer leitura na r\u00e9plica.<\/p>\n\n\n\n<figure class=\"wp-block-embed-wordpress wp-block-embed is-type-wp-embed is-provider-tiago-neves-dba-sql-server\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"mn0AsTg4Vw\"><a href=\"https:\/\/www.tiagoneves.net\/blog\/leitura-na-replica-do-alwayson-com-sql-standard-isso-e-possivel-parte-i\/\">Leitura na R\u00e9plica do AlwaysOn com SQL Standard? Isso \u00e9 poss\u00edvel? &#8211; Parte I<\/a><\/blockquote><iframe loading=\"lazy\" title=\"&#8220;Leitura na R\u00e9plica do AlwaysOn com SQL Standard? Isso \u00e9 poss\u00edvel? &#8211; Parte I&#8221; &#8212; Tiago Neves - DBA SQL Server\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" src=\"https:\/\/www.tiagoneves.net\/blog\/leitura-na-replica-do-alwayson-com-sql-standard-isso-e-possivel-parte-i\/embed\/#?secret=mn0AsTg4Vw\" data-secret=\"mn0AsTg4Vw\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<figure class=\"wp-block-embed-wordpress wp-block-embed is-type-wp-embed is-provider-tiago-neves-dba-sql-server\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"qDJPmrqYHu\"><a href=\"https:\/\/www.tiagoneves.net\/blog\/leitura-na-replica-do-alwayson-com-sql-standard-isso-e-possivel-parte-ii\/\">Leitura na R\u00e9plica do AlwaysOn com SQL Standard? Isso \u00e9 poss\u00edvel? \u2013 Parte II<\/a><\/blockquote><iframe loading=\"lazy\" title=\"&#8220;Leitura na R\u00e9plica do AlwaysOn com SQL Standard? Isso \u00e9 poss\u00edvel? \u2013 Parte II&#8221; &#8212; Tiago Neves - DBA SQL Server\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" src=\"https:\/\/www.tiagoneves.net\/blog\/leitura-na-replica-do-alwayson-com-sql-standard-isso-e-possivel-parte-ii\/embed\/#?secret=qDJPmrqYHu\" data-secret=\"qDJPmrqYHu\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>S\u00f3 que no post anterior <a href=\"https:\/\/www.tiagoneves.net\/blog\/leitura-na-replica-do-alwayson-com-sql-standard-isso-e-possivel-parte-ii\/\">\u201cLeitura na R\u00e9plica do AlwaysOn com SQL Standard? Isso \u00e9 poss\u00edvel? \u2013 Parte II\u201d<\/a>, a configura\u00e7\u00e3o foi realizada em um <strong>SQL Server Standard<\/strong>. Agora vamos fazer em um <strong>SQL Server Developer<\/strong>, que tem as mesmas configura\u00e7\u00f5es do <strong>SQL Server Enterprise<\/strong>, ou seja, <strong>n\u00e3o temos limita\u00e7\u00f5es das features<\/strong>. Ent\u00e3o, agora podemos criar um grupo de disponibilidade com mais de uma base, realizar leitura na r\u00e9plica, executar o checkdb e backups.<\/p>\n\n\n\n<p><strong>IMPORTANTE &#8211; LICENCIAMENTO!!!<\/strong><\/p>\n\n\n\n<p>Lembrando que dependendo do seu contrato voc\u00ea necessita <strong>licenciar os 2 n\u00f3s<\/strong> para poder utilizar essas funcionalidades na r\u00e9plica. <strong>CUIDADO!!!<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Configurando o AlwaysOn no SQL Server Enterprise<\/strong><\/h2>\n\n\n\n<p>A parte de configura\u00e7\u00e3o do <strong>Cluster<\/strong> \u00e9 a mesma, nada muda, ent\u00e3o voc\u00ea pode ver como fazer a base da configura\u00e7\u00e3o no post <a href=\"https:\/\/www.tiagoneves.net\/blog\/leitura-na-replica-do-alwayson-com-sql-standard-isso-e-possivel-parte-i\/\">\u201cLeitura na R\u00e9plica do AlwaysOn com SQL Standard? Isso \u00e9 poss\u00edvel? \u2013 Parte I\u201d<\/a>. O que muda \u00e9 somente a configura\u00e7\u00e3o do <strong>AlwaysOn<\/strong>.<\/p>\n\n\n\n<p>Embora n\u00e3o seja obrigat\u00f3rio, eu gosto de fazer o <strong>restore das bases<\/strong> antes na <strong>r\u00e9plica<\/strong> e dar somente o <strong>join<\/strong> no AlwaysOn, mas isso fica a gosto do fregu\u00eas.<\/p>\n\n\n\n<p>Normalmente, eu fa\u00e7o da seguinte forma:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Bases Pequenas<\/strong>: Backups Full + Log. Nesse caso, o Backup Full \u00e9 r\u00e1pido.<\/li><li><strong>Bases Grandes<\/strong>: Backups Full + Diferencial + Log. Nesse caso, s\u00e3o bases maiores. Com isso, podemos deixar o Backup Full sendo restaurado com mais anteced\u00eancia e no momento da configura\u00e7\u00e3o restauramos o Diferencial + Log e conseguimos agilizar o processo.<\/li><\/ul>\n\n\n\n<p>Entretanto, isso fica a gosto do fregu\u00eas. =)<\/p>\n\n\n\n<p><strong><em>Vamos aos passos!<\/em><\/strong><\/p>\n\n\n\n<p>Antes de come\u00e7ar, vamos <strong>identificar os servidores<\/strong>:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>SQL22 <\/strong>ser\u00e1 o <strong>servidor prim\u00e1rio;<\/strong><\/li><li><strong>SQL23 <\/strong>ser\u00e1 o <strong>servidor secund\u00e1rio<\/strong>.<\/li><\/ul>\n\n\n\n<ol class=\"wp-block-list\"><li>O primeiro passo \u00e9 realizar um <strong>backup full <\/strong>dos bancos no servidor prim\u00e1rio para realizar o restore no servidor secund\u00e1rio.<\/li><\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>backup database AdventureWorks\nto disk = '\\\\sql22\\BKP\\AdventureWorks_dados.bak'\nwith format,stats=1\n\nbackup database Stackoverflow\nto disk = '\\\\sql22\\BKP\\Stackoverflow_dados.bak'\nwith format,stats=1\n<\/code><\/pre>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1363\" height=\"630\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn001.png?fit=678%2C313&amp;ssl=1\" alt=\"\" class=\"wp-image-2330\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn001.png?w=1363&amp;ssl=1 1363w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn001.png?resize=300%2C139&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn001.png?resize=1024%2C473&amp;ssl=1 1024w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn001.png?resize=768%2C355&amp;ssl=1 768w\" sizes=\"auto, (max-width: 678px) 100vw, 678px\" \/><\/figure><\/div>\n\n\n\n<p>2) Ap\u00f3s o backup ser realizado, vamos executar o restore das bases no servidor secund\u00e1rio.<\/p>\n\n\n\n<p><strong>IMPORTANTE \u2013<\/strong> Devemos utilizar a op\u00e7\u00e3o <strong>NORECOVERY<\/strong> para conseguir restaurar os outros backups posteriormente.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>restore database AdventureWorks\nfrom disk = '\\\\sql22\\BKP\\FULL\\AdventureWorks_dados.bak'\nwith move 'AdventureWorks2017' to 'C:\\MSSQLSERVER\\DATA\\AdventureWorks2017.mdf',\nmove 'AdventureWorks2017_log' to 'C:\\MSSQLSERVER\\LOG\\AdventureWorks2017_log.ldf',\nNORECOVERY\n\n\nrestore database Stackoverflow\nfrom disk = '\\\\sql22\\BKP\\Stackoverflow_dados.bak'\nwith move 'SUPERUSER' to 'C:\\MSSQLSERVER\\Data\\Stackoverflow.mdf',\nmove 'SUPERUSER_log' to 'C:\\MSSQLSERVER\\Log\\Stackoverflow_log.ldf',\nNORECOVERY\n<\/code><\/pre>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1360\" height=\"630\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn002.png?fit=678%2C314&amp;ssl=1\" alt=\"\" class=\"wp-image-2331\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn002.png?w=1360&amp;ssl=1 1360w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn002.png?resize=300%2C139&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn002.png?resize=1024%2C474&amp;ssl=1 1024w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn002.png?resize=768%2C356&amp;ssl=1 768w\" sizes=\"auto, (max-width: 678px) 100vw, 678px\" \/><\/figure><\/div>\n\n\n\n<p>3) Agora vamos fazer a configura\u00e7\u00e3o do AlwaysOn. Se voc\u00ea tiver muitas transa\u00e7\u00f5es no momento que for realizar a configura\u00e7\u00e3o, pode ser que voc\u00ea necessite fazer um <strong>backup de log<\/strong> no servidor prim\u00e1rio e fazer o restore no servidor secund\u00e1rio.<\/p>\n\n\n\n<p>Para realizar a configura\u00e7\u00e3o do AlwaysOn, no servidor prim\u00e1rio vamos na guia <strong>\u201cAlways On Availability\u201d \u00e0 \u201cNew Availability Group\u201d<\/strong><\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"416\" height=\"367\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn003.png?resize=416%2C367&#038;ssl=1\" alt=\"\" class=\"wp-image-2332\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn003.png?w=416&amp;ssl=1 416w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn003.png?resize=300%2C265&amp;ssl=1 300w\" sizes=\"auto, (max-width: 416px) 100vw, 416px\" \/><\/figure><\/div>\n\n\n\n<p>4) Vamos ter que dar um nome ao grupo de disponibilidade, no caso vou utilizar o <strong>\u201cAG2017\u201d.<\/strong> Logo ap\u00f3s vamos informar quais databases ir\u00e3o fazer parte do grupo de disponibilidade e informar qual servidor ser\u00e1 configurado a <strong>r\u00e9plica<\/strong>, no caso vou utilizar o servidor secund\u00e1rio <strong>\u201cSQL23\u201d<\/strong>.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1218\" height=\"629\" src=\"https:\/\/i1.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn004.png?fit=678%2C350&amp;ssl=1\" alt=\"\" class=\"wp-image-2333\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn004.png?w=1218&amp;ssl=1 1218w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn004.png?resize=300%2C155&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn004.png?resize=1024%2C529&amp;ssl=1 1024w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn004.png?resize=768%2C397&amp;ssl=1 768w\" sizes=\"auto, (max-width: 678px) 100vw, 678px\" \/><\/figure><\/div>\n\n\n\n<p>5) Agora vamos informar se vamos realizar <strong>leitura na r\u00e9plica<\/strong>. Para fazer isso, na op\u00e7\u00e3o \u201c<strong>Readable Secondary<\/strong>\u201d devemos setar o valor \u201c<strong>Read-intent only<\/strong>\u201d.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"333\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image.png?resize=624%2C333&#038;ssl=1\" alt=\"\" class=\"wp-image-2335\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image.png?w=624&amp;ssl=1 624w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image.png?resize=300%2C160&amp;ssl=1 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<p>Feito isso, o nosso <strong>Grupo de Disponibilidade<\/strong> est\u00e1 configurado! <strong>WOW!!!<\/strong><\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"357\" height=\"471\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-1.png?resize=357%2C471&#038;ssl=1\" alt=\"\" class=\"wp-image-2336\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-1.png?w=357&amp;ssl=1 357w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-1.png?resize=227%2C300&amp;ssl=1 227w\" sizes=\"auto, (max-width: 357px) 100vw, 357px\" \/><\/figure><\/div>\n\n\n\n<p>Para realizar a leitura na r\u00e9plica, precisamos adicionar o par\u00e2metro \u201c<strong>ApplicationIntent=ReadOnly<\/strong>\u201d no <strong>\u201cSQL Server Management Studio\u201d<\/strong> e na <strong>string de conex\u00e3o<\/strong> das aplica\u00e7\u00f5es.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"477\" height=\"523\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-002.png?resize=477%2C523&#038;ssl=1\" alt=\"\" class=\"wp-image-2337\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-002.png?w=477&amp;ssl=1 477w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-002.png?resize=274%2C300&amp;ssl=1 274w\" sizes=\"auto, (max-width: 477px) 100vw, 477px\" \/><\/figure><\/div>\n\n\n\n<p>Ap\u00f3s isso, \u00e9 poss\u00edvel fazer a leitura na r\u00e9plica secund\u00e1ria!!!<\/p>\n\n\n\n<p>No teste abaixo, vou executar uma consulta na r\u00e9plica secund\u00e1ria. Fiz uns <strong>\u201ccross apply\u201d<\/strong> para fazer uma consulta um pouco mais demorada. Dessa forma, vou conseguir tirar um print com a <strong>sp_whoisactive<\/strong> para fazer a demonstra\u00e7\u00e3o.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select *\nfrom AdventureWorks.HumanResources.Employee \ncross apply  AdventureWorks.HumanResources.Department\ncross apply  sys.objects<\/code><\/pre>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1918\" height=\"909\" src=\"https:\/\/i2.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-003.png?fit=678%2C321&amp;ssl=1\" alt=\"\" class=\"wp-image-2338\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-003.png?w=1918&amp;ssl=1 1918w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-003.png?resize=300%2C142&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-003.png?resize=1024%2C485&amp;ssl=1 1024w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-003.png?resize=768%2C364&amp;ssl=1 768w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-003.png?resize=1536%2C728&amp;ssl=1 1536w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AG-003.png?w=1356&amp;ssl=1 1356w\" sizes=\"auto, (max-width: 678px) 100vw, 678px\" \/><\/figure><\/div>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"296\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-2.png?resize=624%2C296&#038;ssl=1\" alt=\"\" class=\"wp-image-2339\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-2.png?w=624&amp;ssl=1 624w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-2.png?resize=300%2C142&amp;ssl=1 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure><\/div>\n\n\n\n<p><strong>\u201cOk Tiago, mas tem algo a mais que podemos fazer na r\u00e9plica secund\u00e1ria?\u201d<\/strong><\/p>\n\n\n\n<p>Tamb\u00e9m podemos utilizar a r\u00e9plica secund\u00e1ria para realizar outras atividades, por exemplo: <strong>CHECKDB<\/strong> e <strong>BACKUP<\/strong>.<\/p>\n\n\n\n<p>Para configurar o SQL Server para fazer o <strong>BACKUP<\/strong> na r\u00e9plica secund\u00e1ria, voc\u00ea tem que alterar a configura\u00e7\u00e3o do <strong>AlwaysOn<\/strong>.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"434\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-4.png?resize=624%2C434&#038;ssl=1\" alt=\"\" class=\"wp-image-2341\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-4.png?w=624&amp;ssl=1 624w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-4.png?resize=300%2C209&amp;ssl=1 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure><\/div>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Prefer Secondary \u2013 <\/strong>O Backup sempre que poss\u00edvel ser\u00e1 realizado na r\u00e9plica secund\u00e1ria, mas se ela n\u00e3o estiver dispon\u00edvel o backup ser\u00e1 realizado na r\u00e9plica primaria.<\/li><li><strong>Secondary Only <\/strong>\u2013 O backup ser\u00e1 sempre realizado a r\u00e9plica secund\u00e1ria.<\/li><li><strong>Primary <\/strong>\u2013 O backup ser\u00e1 sempre realizado na r\u00e9plica prim\u00e1ria.<\/li><li><strong>Any Replica <\/strong>\u2013 O backup ser\u00e1 realizado em qualquer uma das r\u00e9plicas.<\/li><\/ul>\n\n\n\n<p><strong>\u201cTiago posso fazer todos os meus backups na r\u00e9plica secund\u00e1ria?\u201d<\/strong><\/p>\n\n\n\n<p>A resposta \u00e9 <strong>N\u00c3O!<\/strong> Voc\u00ea n\u00e3o consegue fazer um <strong>backup diferencial<\/strong> na r\u00e9plica, pelo menos at\u00e9 a data de hoje (20\/05\/2020). Vamos ver mais sobre isso daqui a pouco.<\/p>\n\n\n\n<p><strong>\u201cTipos de backups suportados na r\u00e9plica secund\u00e1ria\u201d<\/strong><\/p>\n\n\n\n<p>Na r\u00e9plica secund\u00e1ria podemos fazer basicamente somente o backup de log, pois n\u00e3o \u00e9 suportado fazer o backup full na r\u00e9plica secund\u00e1ria, ou melhor, voc\u00ea at\u00e9 pode fazer, mas seria o backup full com a op\u00e7\u00e3o <strong>COPY_ONLY<\/strong>.<\/p>\n\n\n\n<p>N\u00e3o \u00e9 poss\u00edvel fazer <strong>backup diferencial<\/strong> na r\u00e9plica secund\u00e1ria.<\/p>\n\n\n\n<p>\u00c9 poss\u00edvel fazer somente o <strong>backup de log<\/strong> na r\u00e9plica secund\u00e1ria.<\/p>\n\n\n\n<p>Ent\u00e3o podemos criar nossa <strong>pol\u00edtica de backup<\/strong> utilizando tanto a <strong>r\u00e9plica prim\u00e1ria<\/strong> para fazer os backups <strong>full e diferencial<\/strong> e utilizar a <strong>r\u00e9plica secund\u00e1ria<\/strong> para fazer os <strong>backups de log<\/strong>.<\/p>\n\n\n\n<p><strong>Pontos de aten\u00e7\u00e3o:<\/strong><\/p>\n\n\n\n<p>Novamente fica como ponto de aten\u00e7\u00e3o a quest\u00e3o de <strong>LICENCIAMENTO<\/strong>.<\/p>\n\n\n\n<p>Se voc\u00ea utiliza o <strong>Plano de Manuten\u00e7\u00e3o<\/strong> para fazer os backups do seu banco de dados, se atente para algumas pegadinhas:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Se voc\u00ea configurou que a prefer\u00eancia de backup \u00e9 somente na r\u00e9plica secund\u00e1ria, o SQL Server ao executar o job de Backup FULL n\u00e3o vai efetuar o backup, a n\u00e3o ser que voc\u00ea marque a op\u00e7\u00e3o no plano de manuten\u00e7\u00e3o para ignorar a prefer\u00eancia.<\/li><\/ul>\n\n\n\n<p><strong><em>Veja no exemplo abaixo:<\/em><\/strong><\/p>\n\n\n\n<p>Como j\u00e1 foi dito no in\u00edcio do post, a minha r\u00e9plica primaria \u00e9 o servidor <strong>SQL22<\/strong>.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"353\" height=\"256\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-5.png?resize=353%2C256&#038;ssl=1\" alt=\"\" class=\"wp-image-2342\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-5.png?w=353&amp;ssl=1 353w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-5.png?resize=300%2C218&amp;ssl=1 300w\" sizes=\"auto, (max-width: 353px) 100vw, 353px\" \/><\/figure><\/div>\n\n\n\n<p>E configurei o meu AG para fazer o <strong>backup somente na r\u00e9plica secund\u00e1ria<\/strong>.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"594\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-6.png?resize=624%2C594&#038;ssl=1\" alt=\"\" class=\"wp-image-2343\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-6.png?w=624&amp;ssl=1 624w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-6.png?resize=300%2C286&amp;ssl=1 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure><\/div>\n\n\n\n<p>Ao criar um Plano de Manuten\u00e7\u00e3o para fazer um Backup FULL, \u00e9 necess\u00e1rio se atentar em marcar a seguinte op\u00e7\u00e3o:<\/p>\n\n\n\n<p>\u201c<strong>For availability databases, ignore replica priority for backup and backup on primary settings<\/strong>\u201d,<\/p>\n\n\n\n<p>Se voc\u00ea <strong>N\u00c3O<\/strong> habilitar essa op\u00e7\u00e3o, o JOB ser\u00e1 executado com sucesso, por\u00e9m o backup <strong>N\u00c3O<\/strong> ser\u00e1 realizado.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"58\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-7.png?resize=624%2C58&#038;ssl=1\" alt=\"\" class=\"wp-image-2344\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-7.png?w=624&amp;ssl=1 624w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-7.png?resize=300%2C28&amp;ssl=1 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure><\/div>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"56\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-8.png?resize=624%2C56&#038;ssl=1\" alt=\"\" class=\"wp-image-2345\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-8.png?w=624&amp;ssl=1 624w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-8.png?resize=300%2C27&amp;ssl=1 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure><\/div>\n\n\n\n<p>Uma vez marcada a op\u00e7\u00e3o, o SQL Server ir\u00e1 ignorar a prioridade e efetuar\u00e1 o backup full na r\u00e9plica prim\u00e1ria.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"363\" height=\"300\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-9.png?resize=363%2C300&#038;ssl=1\" alt=\"\" class=\"wp-image-2346\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-9.png?w=363&amp;ssl=1 363w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-9.png?resize=300%2C248&amp;ssl=1 300w\" sizes=\"auto, (max-width: 363px) 100vw, 363px\" \/><\/figure><\/div>\n\n\n\n<p>Este problema ocorre <strong>somente<\/strong> quando utilizamos <strong>plano de manuten\u00e7\u00e3o<\/strong>. Se voc\u00ea faz backup utilizando scripts, voc\u00ea n\u00e3o ter\u00e1 esse problema. <strong>=)<\/strong><\/p>\n\n\n\n<p>Para o <strong>backup de log<\/strong>, se voc\u00ea for realiz\u00e1-lo na r\u00e9plica secund\u00e1ria voc\u00ea vai ter que configurar um job para executar na r\u00e9plica secund\u00e1ria (no caso do post o SQL23).<\/p>\n\n\n\n<p><strong>Para mais informa\u00e7\u00f5es sobre backup no AlwaysOn:<\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/availability-groups\/windows\/configure-backup-on-availability-replicas-sql-server?view=sql-server-2017\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/availability-groups\/windows\/configure-backup-on-availability-replicas-sql-server?view=sql-server-2017<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/availability-groups\/windows\/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups?view=sql-server-2017\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/availability-groups\/windows\/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups?view=sql-server-2017<\/a><\/p>\n\n\n\n<p><strong>CHECKDB na r\u00e9plica secund\u00e1ria<\/strong><\/p>\n\n\n\n<p>Como foi dito anteriormente, podemos executar a rotina de <strong>CHECKDB<\/strong> na r\u00e9plica secund\u00e1ria sem nenhum problema. Al\u00e9m disso, ela nem precisa estar com a op\u00e7\u00e3o do <strong>readonly habilitada<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>dbcc checkdb('AdventureWorks')<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"323\" src=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-10.png?resize=624%2C323&#038;ssl=1\" alt=\"\" class=\"wp-image-2347\" srcset=\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-10.png?w=624&amp;ssl=1 624w, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/image-10.png?resize=300%2C155&amp;ssl=1 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<p>Bom pessoal \u00e9 isso, com esse post finalizamos a s\u00e9rie de como fazer a <strong>Leitura na R\u00e9plica do AlwaysOn<\/strong>.<\/p>\n\n\n\n<p>Recomendo tamb\u00e9m que fa\u00e7am o <strong>CURSO GRATUITO<\/strong> <strong>\u201cM\u00f3dulo 01: HADR &#8211; In\u00edcio da Jornada\u201d <\/strong>da Power Tuning com instrutores feras demais nesse assunto!<\/p>\n\n\n\n<p><a href=\"https:\/\/cursos.powertuning.com.br\/course?courseid=mdulo-01-hadr-incio-da-jornada\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/cursos.powertuning.com.br\/course?courseid=mdulo-01-hadr-incio-da-jornada<\/a><\/p>\n\n\n\n<p>Espero que tenham gostado e aprendido algo tamb\u00e9m! <strong>=)<\/strong><\/p>\n\n\n\n<p>Um abra\u00e7o,<\/p>\n\n\n\n<p>Tiago Neves<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hey guys tudo certo? No post de hoje vou compartilhar um pouco mais sobre o AlwaysOn. Nos dois \u00faltimos posts mostrei como realizar a configura\u00e7\u00e3o do AlwaysOn no SQL Server e como podemos utilizar o Snapshot para fazer leitura na r\u00e9plica. S\u00f3 que no post anterior \u201cLeitura na R\u00e9plica do AlwaysOn com SQL Standard? Isso [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2349,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rop_custom_images_group":[],"rop_custom_messages_group":[],"rop_publish_now":"initial","rop_publish_now_accounts":{"twitter_91251433_91251433":""},"rop_publish_now_history":[],"rop_publish_now_status":"pending","_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"No post de hoje, concluo a s\u00e9rie de como fazer leitura na r\u00e9plica secund\u00e1ria e dessa vez sem fazer gambiarra ou, sem fazer uma solu\u00e7\u00e3o de cotorno =D, al\u00e9m de como configurar algumas rotinas como backup e checkdb na r\u00e9plica secund\u00e1ria.","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_wpscppro_dont_share_socialmedia":false,"_wpscppro_custom_social_share_image":0,"_facebook_share_type":"","_twitter_share_type":"","_linkedin_share_type":"","_pinterest_share_type":"","_linkedin_share_type_page":"","_instagram_share_type":"","_medium_share_type":"","_threads_share_type":"","_google_business_share_type":"","_selected_social_profile":[],"_wpsp_enable_custom_social_template":false,"_wpsp_social_scheduling":{"enabled":false,"datetime":null,"platforms":[],"status":"template_only","dateOption":"today","timeOption":"now","customDays":"","customHours":"","customDate":"","customTime":"","schedulingType":"absolute"},"_wpsp_active_default_template":true},"categories":[2,310,44,220,26,20],"tags":[95,214,174,29,311,35,166],"class_list":["post-2329","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-casos-do-dia-a-dia","category-high-availability","category-microsoft","category-sql-server-2017","category-sqlserver-2016","category-sqlserver-geral","tag-alwayson","tag-backup","tag-checkdb","tag-high-availability","tag-read-only","tag-sql-server","tag-sql-server-brasil"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2020\/05\/AlwaysOn-SQL-Server.png?fit=353%2C95&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6eIyh-Bz","jetpack-related-posts":[{"id":2414,"url":"https:\/\/www.tiagoneves.net\/blog\/sql-server-2016-esta-chegando-ao-fim-do-suporte-o-que-isso-significa-na-pratica\/","url_meta":{"origin":2329,"position":0},"title":"SQL Server 2016 est\u00e1 chegando ao fim do suporte: o que isso significa na pr\u00e1tica?","author":"tiagoneves","date":"27 de abril de 2026","format":false,"excerpt":"O SQL Server 2016 ter\u00e1 seu suporte estendido encerrado em 14 de julho de 2026, o que resultar\u00e1 na falta de atualiza\u00e7\u00f5es de seguran\u00e7a e suporte. Continuar usando essa vers\u00e3o traz riscos como vulnerabilidades e n\u00e3o conformidade regulat\u00f3ria. Planejar a migra\u00e7\u00e3o para vers\u00f5es mais recentes ou cloud deve ser prioridade\u2026","rel":"","context":"Em &quot;Seguran\u00e7a&quot;","block_context":{"text":"Seguran\u00e7a","link":"https:\/\/www.tiagoneves.net\/blog\/category\/seguranca\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/04\/sql_server_2016_eos_v3.png?fit=772%2C702&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/04\/sql_server_2016_eos_v3.png?fit=772%2C702&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/04\/sql_server_2016_eos_v3.png?fit=772%2C702&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/04\/sql_server_2016_eos_v3.png?fit=772%2C702&ssl=1&resize=700%2C400 2x"},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts\/2329","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/comments?post=2329"}],"version-history":[{"count":2,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts\/2329\/revisions"}],"predecessor-version":[{"id":2350,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts\/2329\/revisions\/2350"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/media\/2349"}],"wp:attachment":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/media?parent=2329"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/categories?post=2329"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/tags?post=2329"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}