Preciso fazer backup de 10 a 20 bancos de dados do SQL Server 2008 R2 com tamanhos entre 10 e 50 GB, enquanto eles estão online e usados ​​simultaneamente por um único aplicativo corporativo. Também preciso restaurá-los para um estado que seja amplamente sincronizado em todos os bancos de dados (posso pagar alguns segundos de dessincronização entre os bancos de dados). O objetivo é capturar dados de produção para ambientes QA/DEV.

Eu gostaria fortemente de não exigir que os bancos de dados sejam executados em recuperação total e criar um método de backup dedicado à captura de dados para ambientes de controle de qualidade e que permaneça independente de um processo principal de backup que não esteja sob meu controle.

Para meus clientes, levará de 1 a 2 horas para capturar 20 backups completos de aproximadamente 30 GB cada. Isso torna inaceitável fazer backups completos sequencialmente, pois os bancos de dados ficariam muito dessincronizados ao serem executados na recuperação simples.

Estou procurando uma ideia melhor do que estas:

IDEIA 1: Snapshot no nível SAN de discos VM. xcopy MDFs/LDFs do instantâneo.

Depois que os arquivos copiados são anexados a uma instância de servidor diferente, seu processo de recuperação deve produzir bancos de dados consistentes que são instantâneos praticamente simultaneamente.
Pesquisar no Google me convenceu de que essa é uma má ideia, pelo menos porque posso obter desync vs. master/msdb/etc.

IDEIA 2: Orquestre um backup complexo e uma restauração sincronizada em todos os bancos de dados

Isso exige que bancos de dados exigentes sejam executados em recuperação total, o que eu não quero. Inicie backups paralelos para todos os bancos de dados bem antes do prazo (T0). Quando T0 for atingido, faça backup de todos os logs (deve levar no máximo alguns minutos). Pegue a miríade resultante de backups e tente restaurá-los e rolar os logs para frente/para trás para obter um estado um tanto consistente nos bancos de dados, em relação a T0.
Isso requer muito planejamento e scripts para que seja usado de forma confiável, então eu faria de tudo para evitá-lo.

Estou perdendo alguma outra solução?

PS1: Eu adoraria poder usar snapshots db . A ideia era iniciar um instantâneo em cada banco de dados (que deve terminar em segundos) e, em seguida, fazer backup completo de cada um sequencialmente nos minutos/horas seguintes. Em seguida, restaure todos eles em um servidor diferente e reverta cada um para o instantâneo. AFAIK este cenário não é possível porque os instantâneos não podem ser copiados junto com o banco de dados. Eles só podem ser revertidos no local, no servidor onde foram criados. Além disso, eles exigem a Enterprise Edition, que não tenho para todos os clientes.

PS2: Se você souber de uma solução de terceiros capaz de produzir backups sincronizados entre bancos de dados, mencione-a.

Eu tenho uma tabela legada com um nome contendo caracteres não imprimíveis ( CHAR(31), para ser específico).

O caractere não imprimível está ao lado de um sublinhado e descobri que o atalho CTRL+SHIFT+_cria o CHAR(31)caractere (que significa "EUA" - separador de unidades). O desenvolvedor anterior pode ter acertado essa combinação por engano e criado a tabela com esse personagem estranho.

Quando emitimos um SELECTcomando na tabela, ele retorna resultados. Mas quando tentamos emitir qualquer DDL contra ele ( DROP, sp_rename, etc), o personagem causa um problema.

Exemplos:

DROP TABLE Table_Name;

Levanta:

Msg 15225 - Nenhum item com o nome de 'Table_Name' pôde ser encontrado no banco de dados atual 'MyDB', dado que @itemtype foi inserido como '(nulo)'.

EXEC sp_rename N'Table_Name', N'NewTableName'; 

Levanta:

Msg 102 - Sintaxe incorreta perto de '_Name'.

Já dupliquei a tabela com o nome correto, e corrigi nos objetos dependentes. A etapa restante é apenas removê-lo do banco de dados.

Só um insight: quando copiamos+colamos do SQL Server para o Notepad++, ele mostra o caractere oculto ("US") no meio do nome da tabela, ao lado do sublinhado:

Tenho que refatorar e documentar uma série de foo.sqlconsultas que serão compartilhadas por uma equipe de suporte técnico do banco de dados (para configurações do cliente e coisas assim). Existem tipos de tickets que vêm regularmente onde cada cliente tem seus próprios servidores e bancos de dados, mas fora isso o esquema é o mesmo em todos os lugares.

Os procedimentos armazenados não são uma opção no momento. Estou debatendo se devo usar dinâmico ou SQLCMD, não usei muito porque sou um pouco novo no SQL Server.

Scripts SQLCMD Eu sinto definitivamente "parece" mais limpo para mim e mais fácil de ler e fazer pequenas alterações nas consultas conforme necessário, mas também força o usuário a habilitar o modo SQLCMD. Dinâmico é mais difícil, pois o realce da sintaxe é perdido devido à consulta sendo escrita usando manipulação de string.

Eles estão sendo editados e executados usando o Management Studio 2012, SQL versão 2008R2. Quais são alguns dos prós/contras de qualquer método ou algumas das "práticas recomendadas" do SQL Server em um método ou outro? Um deles é "mais seguro" do que o outro?

Exemplo dinâmico:

declare @ServerName varchar(50) = 'REDACTED';
declare @DatabaseName varchar(50) = 'REDACTED';
declare @OrderIdsSeparatedByCommas varchar(max) = '597336, 595764, 594594';

declare @sql_OrderCheckQuery varchar(max) = ('
use {@DatabaseName};
select 
    -- stuff
from 
    {@ServerName}.{@DatabaseName}.[dbo].[client_orders]
        as "Order"
    inner join {@ServerName}.{@DatabaseName}.[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ({@OrderIdsSeparatedByCommas});
');
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@ServerName}',   quotename(@ServerName)   );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@DatabaseName}', quotename(@DatabaseName) );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@OrderIdsSeparatedByCommas}', @OrderIdsSeparatedByCommas );
print   (@sql_OrderCheckQuery); -- For debugging purposes.
execute (@sql_OrderCheckQuery);

Exemplo SQLCMD:

:setvar ServerName "[REDACTED]";
:setvar DatabaseName "[REDACTED]";
:setvar OrderIdsSeparatedByCommas "597336, 595764, 594594"

use $(DatabaseName)
select 
    --stuff
from 
    $(ServerName).$(DatabaseName).[dbo].[client_orders]
        as "Order"
    inner join $(ServerName).$(DatabaseName).[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ($(OrderIdsSeparatedByCommas));

Eu tenho um aplicativo que usa uma tabela PostgreSQL. A tabela é muito grande (bilhões de linhas) e possui uma coluna que é um número inteiro.

O integerpode ter até 6 dígitos, ou seja, 0-999.999 , sem negativos.

Pensei em mudar para ser numeric(6,0).

Isso seria uma boa ideia? Levaria numeric(6,0)menos bytes? E quanto ao desempenho (esta tabela está sendo muito consultada)?

Dada a tabela:

e índices:

• "items_pkey" CHAVE PRIMÁRIA, btree (id)
• "items_object_id_idx" btree (object_id)

Quando eu executo:

SELECT *
FROM items
WHERE object_id = 123
LIMIT 1;

Ele retorna 0 linhas muito rapidamente. Porém, quando executo essa query com ORDER BY, ela trava por muito tempo:

SELECT *
FROM items
WHERE object_id = 123
ORDER BY id DESC  -- I added the ORDER BY
LIMIT 1;

O que explica essa discrepância?

Planos de consulta

Consulta rápida (sem ORDER BY)

                                                                          QUERY PLAN                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.34 rows=1 width=63) (actual time=0.014..0.014 rows=0 loops=1)
   ->  Index Scan using items_object_id_operation_idx on items  (cost=0.56..2579.16 rows=929 width=63) (actual time=0.013..0.013 rows=0 loops=1)
         Index Cond: (object_id = 123::bigint)
 Total runtime: 0.029 ms

Consulta lenta (com o ORDER BY)

                                                                            QUERY PLAN                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..1269.14 rows=1 width=63) (actual time=873796.061..873796.061 rows=0 loops=1)
   ->  Index Scan Backward using items_pkey on items  (cost=0.44..1164670.11 rows=918 width=63) (actual time=873796.059..873796.059 rows=0 loops=1)
         Filter: (object_id = 123::bigint)
         Rows Removed by Filter: 27942522
 Total runtime: 873796.113 ms

Considere uma pricestabela com estas colunas:

id         integer primary key
product_id integer -- foreign key
start_date date not null
end_date   date not null
quantity   integer
price      numeric

Gostaria que o banco de dados impusesse a regra de que um produto só pode ter um preço em uma quantidade específica em um intervalo de datas (via where <date> BETWEEN start_date AND end_date).

Esse tipo de restrição baseada em intervalo é factível?

Minha função new_customeré chamada várias vezes por segundo (mas apenas uma vez por sessão) por um aplicativo da web. A primeira coisa que ele faz é bloquear a customertabela (para fazer uma 'inserção se não existir' - uma variante simples de um upsert).

Meu entendimento dos documentos é que outras chamadas new_customerdevem simplesmente ficar na fila até que todas as chamadas anteriores sejam concluídas:

LOCK TABLE obtém um bloqueio em nível de tabela, aguardando, se necessário, a liberação de quaisquer bloqueios conflitantes.

Por que às vezes é um impasse em vez disso?

definição:

create function new_customer(secret bytea) returns integer language sql 
                security definer set search_path = postgres,pg_temp as $$
  lock customer in exclusive mode;
  --
  with w as ( insert into customer(customer_secret,customer_read_secret)
              select secret,decode(md5(encode(secret, 'hex')),'hex') 
              where not exists(select * from customer where customer_secret=secret)
              returning customer_id )
  insert into collection(customer_id) select customer_id from w;
  --
  select customer_id from customer where customer_secret=secret;
$$;

erro do registro:

2015-07-28 08:02:58 BST DETALHE: Processo 12380 aguarda ExclusiveLock na relação 16438 da base de dados 12141; bloqueado pelo processo 12379.
        Processo 12379 aguarda ExclusiveLock na relação 16438 do banco de dados 12141; bloqueado pelo processo 12380.
        Processo 12380: selecione new_customer(decode($1::text, 'hex'))
        Processo 12379: selecione new_customer(decode($1::text, 'hex'))
2015-07-28 08:02:58 BST DICA: Consulte o log do servidor para obter detalhes da consulta.
2015-07-28 08:02:58 BST CONTEXT: Função SQL "new_customer" instrução 1
28/07/2015 08:02:58 DECLARAÇÃO BST: selecione new_customer(decode($1::text, 'hex'))

relação:

postgres=# select relname from pg_class where oid=16438;
┌──────────┐
│ relname  │
├──────────┤
│ customer │
└──────────┘

editar:

Consegui obter um caso de teste reproduzível simples. Para mim, isso parece um bug devido a algum tipo de condição de corrida.

esquema:

create table test( id serial primary key, val text );

create function f_test(v text) returns integer language sql security definer set search_path = postgres,pg_temp as $$
  lock test in exclusive mode;
  insert into test(val) select v where not exists(select * from test where val=v);
  select id from test where val=v;
$$;

script bash executado simultaneamente em duas sessões bash:

for i in {1..1000}; do psql postgres postgres -c "select f_test('blah')"; done

log de erros (geralmente um punhado de impasses nas 1000 chamadas):

2015-07-28 16:46:19 BST ERROR:  deadlock detected
2015-07-28 16:46:19 BST DETAIL:  Process 9394 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9393.
        Process 9393 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9394.
        Process 9394: select f_test('blah')
        Process 9393: select f_test('blah')
2015-07-28 16:46:19 BST HINT:  See server log for query details.
2015-07-28 16:46:19 BST CONTEXT:  SQL function "f_test" statement 1
2015-07-28 16:46:19 BST STATEMENT:  select f_test('blah')

edição 2:

@ypercube sugeriu uma variante com o lado de lock tablefora da função:

for i in {1..1000}; do psql postgres postgres -c "begin; lock test in exclusive mode; select f_test('blah'); end"; done

curiosamente, isso elimina os impasses.

Sou muito novo no assunto de bancos de dados, então isso pode parecer ignorante, mas estou curioso para saber por que uma chave deve ser explícita em uma tabela. Isso é principalmente para informar ao usuário que o valor da coluna fornecido é (esperançosamente) exclusivo em cada linha? A singularidade ainda deve estar lá, mesmo que não seja mencionada.

Estou tendo um problema de desempenho com uma consulta que não consigo entender.

Tirei a consulta de uma definição de cursor.

Esta consulta leva segundos para ser executada

SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND ((A.CALCTIMEHOURS<>0)
AND (A.JOBTYPE<>3)))
AND EXISTS (SELECT 'X'
FROM PRODROUTE B
WHERE ((B.DATAAREAID=N'IW')
AND (((((B.PRODID=A.PRODID)
AND ((B.PROPERTYID=N'PR1526157') OR (B.PRODID=N'PR1526157')))
AND (B.OPRNUM=A.OPRNUM))
AND (B.OPRPRIORITY=A.OPRPRIORITY))
AND (B.OPRID=N'GRIJZEN')))
AND NOT EXISTS (SELECT 'X'
FROM ADUSHOPFLOORROUTE C
WHERE ((C.DATAAREAID=N'IW')
AND ((((((C.WRKCTRID=A.WRKCTRID)
AND (C.PRODID=B.PRODID))
AND (C.OPRID=B.OPRID))
AND (C.JOBTYPE=A.JOBTYPE))
AND (C.FROMDATE>{TS '1900-01-01 00:00:00.000'}))
AND ((C.TODATE={TS '1900-01-01 00:00:00.000'}))))))
GROUP BY A.JOBTYPE
ORDER BY A.JOBTYPE

O plano de execução real se parece com isso.

Percebendo que a configuração de todo o servidor foi definida como MaxDOP 1, tentei brincar com as configurações de maxdop.

Adicionar OPTION (MAXDOP 0)à consulta ou alterar as configurações do servidor resulta em um desempenho muito melhor e neste plano de consulta.

No entanto, o aplicativo em questão (Dynamics AX) não executa consultas como esta, ele usa cursores.

O código real capturado é este.

declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

resultando neste plano de execução (e, infelizmente, os mesmos tempos de execução de vários segundos).

Eu tentei várias coisas, como descartar planos em cache, adicionar opções na consulta dentro da definição do cursor, ... Mas nenhum deles parece me dar um plano paralelo.

Também pesquisei bastante no google procurando por limitações de paralelismo de cursores, mas não consigo encontrar nenhuma limitação.

Estou perdendo algo óbvio aqui?

A compilação real do SQL é SQL Server 2008 (SP1) - 10.0.2573.0 (X64)que eu percebo não ser suportada, mas não posso atualizar esta instância como considero adequado. Eu precisaria transferir o banco de dados para outro servidor e isso significaria obter um backup descompactado bastante grande em uma WAN lenta.

O sinalizador de rastreamento 4199 não faz diferença, nem OPTION (RECOMPILE).

As propriedades do cursor são:

API | Fast_Forward | Read Only | Global (0)

Gostaria de saber se existe alguma outra maneira de verificar nossas consultas lentas sem registrar uma consulta lenta. Suponha que eu tenha um servidor altamente ocupado que não possa registrar muito para economizar memória e E/S. Então, existe alguma outra maneira disponível para verificar se eu tenho uma consulta lenta? Eu sei, podemos fazer o perfil da consulta, mas ainda não sabemos exatamente o que fazer para identificar qual consulta é a que ocupa mais tempo e memória.

Acabei de iniciar a administração do mysql e não tenho certeza de como lidar com isso. Qualquer orientação será muito apreciada.