gotqn's questions

Preciso usar STRING_SPLIT com ordenação habilitada:

Atualizamos todos os nossos servidores para o SQL Server 2022 e alteramos o nível de compatibilidade para 160, mas o código a seguir não está funcionando:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

O erro é:

Msg 8748, Nível 16, Estado 1, Linha 1 O argumento enable_ordinal para STRING_SPLIT suporta apenas valores constantes (não variáveis ​​ou colunas).

O ambiente de produção é:

Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64) 
    Nov 14 2023 18:33:19 
    Copyright (C) 2022 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

A versão do SSMS é:

Testamos o código na CU mais recente:

A única coisa que encontrei foi esta resposta apontando que:

O problema é que o SSMS tem IntelliSense/dicas de ferramentas codificadas sem lógica condicional com base na versão, e o código está à frente do mecanismo. Atualmente, a funcionalidade está disponível apenas no Banco de Dados SQL do Azure, Instância Gerenciada e Synapse.

Ainda assim, não tenho certeza de onde está o problema: na documentação, no mecanismo, no SSMS ou em algo que não estou fazendo direito.

Observação: o problema é o mesmo no Azure Data Studio.

Sempre pensei que a função CONCAT fosse na verdade um wrapper sobre o + (Concatenação de Strings) com algumas verificações adicionais para facilitar nossa vida.

Não encontrei nenhum detalhe interno sobre como as funções são implementadas. Quanto ao desempenho, parece que há sobrecarga para chamar CONCATquando os dados estão concatenando em um loop (o que parece normal, pois há identificadores adicionais para NULLs).

Há poucos dias, um desenvolvedor modificou algum código de concatenação de strings (de +para CONCAT)porque não gostou da sintaxe e me disse que ficou mais rápido.

Para verificar o caso, usei o seguinte código:

DECLARE @V1 NVARCHAR(MAX)
       ,@V2 NVARCHAR(MAX)
       ,@V3 NVARCHAR(MAX);

DECLARE @R NVARCHAR(MAX);

SELECT  @V1 = REPLICATE(CAST('V1' AS NVARCHAR(MAX)), 50000000)
       ,@V2 = REPLICATE(CAST('V2' AS NVARCHAR(MAX)), 50000000)
       ,@V3 = REPLICATE(CAST('V3' AS NVARCHAR(MAX)), 50000000);

onde esta é a variante um:

SELECT @R = CAST('' AS NVARCHAR(MAX)) + '{some small text}' + ISNULL(@V1, '{}') + ISNULL(@V2, '{}') + ISNULL(@V3, '{}'); 
SELECT LEN(@R); -- 1200000017

e esta é a variante dois:

SELECT @R = CONCAT('{some small text}',ISNULL(@V1, '{}'), ISNULL(@V2, '{}'), ISNULL(@V3, '{}'))
SELECT LEN(@R); -- 1200000017

Para strings menores, não há diferenças. Em algum momento, a CONCATvariante se torna mais rápida:

Eu me pergunto se alguém pode compartilhar alguns detalhes internos ou explicar o comportamento, pois parece que pode haver uma regra de que é melhor concatenar strings grandes usando CONCAT.

Versão:

Microsoft SQL Server 2022 (RTM-CU8) (KB5029666) - 16.0.4075.1 (X64) 23 de agosto de 2023 14:04:50 Copyright (C) 2022 Microsoft Corporation Standard Edition (64 bits) no Windows Server 2019 Standard 10.0 (Build 17763 : ) (Hipervisor)

O script exato é semelhante ao seguinte:

DECLARE @V1 NVARCHAR(MAX)
       ,@V2 NVARCHAR(MAX)
       ,@V3 NVARCHAR(MAX);

DECLARE @R NVARCHAR(MAX);

SELECT  @V1 = REPLICATE(CAST('V1' AS NVARCHAR(MAX)), 50000000)
       ,@V2 = REPLICATE(CAST('V2' AS NVARCHAR(MAX)), 50000000)
       ,@V3 = REPLICATE(CAST('V3' AS NVARCHAR(MAX)), 50000000);



--SELECT @R = CAST('' AS NVARCHAR(MAX)) + '{some small text}' + ISNULL(@V1, '{}') + ISNULL(@V2, '{}') + ISNULL(@V3, '{}'); -- 00:00:45 -- 00:01:22 -- 00:01:20
--SELECT LEN(@R); -- 300000017

SELECT @R = CONCAT('{some small text}',ISNULL(@V1, '{}'), ISNULL(@V2, '{}'), ISNULL(@V3, '{}')) -- 00:00:11 -- 00:00:16 -- 00:00:10
SELECT LEN(@R); -- 300000017

onde estou alterando o último argumento das funções REPLICATE para gerar strings maiores para a concatenação. Então, estou executando cada variante três vezes.

Em uma das instâncias do SQL Server não consigo executar nenhuma consulta. Achei que fosse devido a alguma carga pesada, então tento executar sp_execeute whoisactivee obter:

]

Ontem à noite, o DBA realizou a atualização para o SQL Server 2022 CU5 e me disse que há um problema que foi resolvido com o CU7. Então, a atualização para CU7 foi realizada, mas nada mudou.

O DBA me enviou isto:

e depois disso:

mas não tenho certeza do que está acontecendo. O DBA interrompeu o armazenamento de consultas e todas as sessões de eventos estendidas, mas nada mudou. Agora, o DBA acha que o problema é causado por isso , mas deve ser corrigido no CU4 e causa problemas durante a atualização, não depois dela.

O DBA abriu ticket para MS, mas gostaria de saber se alguém sabe o que pode estar causando isso?

Vou anexar os dados de rastreamento. Um pouco mais de informação - temos uma instância do SQL Server 2022 CU5 e outra executando as réplicas com CU7.

O outro DBA acredita que o problema está nas réplicas sempre ativas, então elas são interrompidas - as réplicas travavam constantemente.

Temos um requisito comercial para poder solicitar um ponto de extremidade HTTP quando determinados eventos ocorrem no banco de dados (um banco de dados do SQL Server). Temos um catálogo de pontos finais que controlamos. Usamos SQL Server 2019 e 2022.

Atualmente, usamos o Service Broker External Activator , que funciona bem, mas essa tecnologia é antiga e, pelo que entendemos, não é mais suportada pela Microsoft. Além disso, a configuração/implementação é bastante complexa e a administração a marcou como "negócios inviáveis".

Quando trabalharmos no Azure, usaremos o novo sp_invoke_external_rest_endpoint para chamar uma função do Azure que fará a solicitação. Isso funciona bem e vamos usá-lo quando estiver no Azure.

Infelizmente, este procedimento NÃO está disponível na versão local padrão do SQL Server 2022. É por isso que planejamos usar EXECUTE sp_execute_external_script @language = N'Python'para solicitar pontos de extremidade HTTP.

Já usamos o SQL Server Machine Learning Services em produção, então os temos disponíveis no SQL Server 2019 e 2022. Testamos essa implementação e parece estar funcionando bem, embora um pouco lento.

Queremos saber: Existe uma grande desvantagem em usar o Python para chamar endpoints HTTP que estão faltando em nossa avaliação?

Aqui está um exemplo do código que usamos para prova de conceito:

CREATE PROCEDURE [dbo].[usp_APICalls_HTTPRequest]
    @URL NVARCHAR(100),
    @Method VARCHAR(50),
    @HeadersJSON NVARCHAR(MAX),
    @BodyJSON NVARCHAR(MAX) = NULL,
    @BodyParams NVARCHAR(MAX) = NULL,
    @StatusCode VARCHAR(50) = NULL OUTPUT,
    @ResponseBody NVARCHAR(MAX) = NULL OUTPUT
AS
    EXECUTE sp_execute_external_script @language = N'Python',
    @script = N'
import datetime
import json
import requests
 
print("")
print("Script started {:%Y-%m-%d %H:%M:%S}".format(datetime.datetime.now()))
print("")
 
objHeaders = json.loads(HeadersJSON)

if BodyJSON:
    payload = json.dumps(json.loads(BodyJSON))
else:
    payload = BodyParams
 
objReqResponse = requests.request(Method, URL, headers=objHeaders, data=payload)
 
objResponse = {
    "StatusCode": [objReqResponse.status_code],
    "Body": [objReqResponse.text]
}
 
StatusCode = str(objReqResponse.status_code)
ResponseBody = objReqResponse.text
 
print("")
print("Script complete {:%Y-%m-%d %H:%M:%S}".format(datetime.datetime.now()))
print("")
 
    ',
        @params = N'@URL NVARCHAR(100),
                    @Method VARCHAR(50),
                    @HeadersJSON NVARCHAR(MAX),
                    @BodyJSON NVARCHAR(MAX),
                    @BodyParams NVARCHAR(MAX),
                    @StatusCode VARCHAR(50) OUTPUT,
                    @ResponseBody NVARCHAR(MAX) OUTPUT',
        @URL = @URL,
        @Method = @Method,
        @HeadersJSON = @HeadersJSON,
        @BodyJSON = @BodyJSON,
        @BodyParams = @BodyParams,
        @StatusCode = @StatusCode OUTPUT,
        @ResponseBody = @ResponseBody OUTPUT
 
GO

Eu tenho alguns dados corrompidos e não consegui restaurar os dados por meio de backups. Isso estava afetando principalmente índices não clusterizados, portanto, após a criação de queda dos índices, os problemas foram resolvidos.

Por fim, tenho:

Msg 8905, Level 16, State 1, Line 3 Extent (1:896320) no banco de dados ID 15 está marcado como alocado no GAM, mas nenhum SGAM ou IAM o alocou. CHECKDB encontrou 1 erro de alocação e 0 erro de consistência não associado a nenhum objeto único. CHECKDB encontrou 1 erro de alocação e 0 erro de consistência no banco de dados 'db_name'. repair_allow_data_loss é o nível mínimo de reparo para os erros encontrados por DBCC CHECKDB (db_name).

Eu quero descobrir quais são as páginas de extensão e depois ver quais são os dados nessas páginas. Então, decida executar o comando CHECHDB com repair_allow_data_loss ou salve alguns dados, se possível.

Em uma fila do Service Broker, quando uma mensagem é processada ao longo do tempo, estamos executando um procedimento armazenado que está realizando algumas operações pesadas usando tabelas temporárias.

Eu acho que sp_WhoIsActiveestá mostrando valores resumidos para alocações de tempdb e uso de CPU, pois a fila está usando uma sessão.

A questão é: as alocações são liberadas quando o procedimento armazenado atual é concluído?

Tenho uma tabela com a seguinte estrutura:

ItemID           int
ItemType         char(1)
Language         char(2)
Localization     char(2)
Literal          nvarchar(4000)

que é usado para traduzir o texto de específico ItemIDpara Literalum. A Literalcoluna pode conter HTMLtags. Para um conjunto específico de consultas, essas HTMLtags precisam ser removidas e, como a sanitização é feita com SQL CLR em milhares de linhas, prefiro não executá-la na leitura.

Então, eu adicionei uma coluna persistente como esta:

ALTER TABLE [dbo].[table]
ADD [LiteralSanitized] AS NULLIF(CAST(LTRIM(RTRIM([dbo].[fn_Utils_RemoveAllHtmlTags] ([Literal]))) AS NVARCHAR(4000)), '') PERSISTED;

A tabela tem apenas um índice (a chave primária) com a seguinte definição:

ItemID, ItemType, Language, Localization

Portanto, a consulta foi mais rápida, mas estou vendo algumas leituras adicionais para esta tabela:

Scan count    - 2     vs 12,230 
Logical reads - 3,234 vs 43,472 

o que pode ser normal, pois agora estou lendo mais dados por causa da coluna. Então, eu adicionei o seguinte índice:

(ItemID ASC, ItemType ASC, Language ASC, Localization ASC) INCLUDE ([LiteralSanitized])

mas não é usado pelo motor. Então, eu tento forçar o motor a usá-lo:

UPDATE #temp
SET [QuestionText] = PSGQ.[LiteralSanitized]
FROM #temp PQD                                            
INNER JOIN [dbo].[table_with_translations] PSGQ WITH(INDEX = [the_new_index])
    ON PQD.[ProtoQuestionID] = PSGQ.[ItemID] 
WHERE PSGQ.[ItemType] = 'Q' 
    AND PSGQ.[Language] = @language 
    AND RTRIM(PSGQ.[Localization]) = ''
    AND PSGQ.[LiteralSanitized] IS NOT NULL;

mas o motor está fazendo o seguinte:

• executa a varredura de índice (no meu novo índice)
• em seguida, executa o loop aninhado e a pesquisa de chave com o índice clusterizado
• extrair a literalcoluna

Se minha coluna for Persisted, por que o motor continua tentando devolver a Literalcoluna já que ela nem é necessária?

Eu tenho um índice armazenado em colunas clusterizado na tabela usada para log - apenas inserções (mas não inserções em massa). As estatísticas atuais da tabela são:

• 3541 milhões de linhas
• 6,6 GB de espaço reservado

Eu vejo esta manhã a seguinte operação via sp_whoisactive:

ALTER INDEX [...] ON [...].[...] 
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

Eu uso a seguinte consulta para verificar quantas linhas por row_group_idnós temos:

SELECT
    tables.name AS table_name,
    indexes.name AS index_name,
    partitions.partition_number,
    dm_db_column_store_row_group_physical_stats.row_group_id,
    dm_db_column_store_row_group_physical_stats.total_rows,
    dm_db_column_store_row_group_physical_stats.deleted_rows,
    dm_db_column_store_row_group_physical_stats.state_desc,
    dm_db_column_store_row_group_physical_stats.trim_reason_desc
FROM sys.dm_db_column_store_row_group_physical_stats
INNER JOIN sys.indexes
ON indexes.index_id = 
    dm_db_column_store_row_group_physical_stats.index_id
AND indexes.object_id = 
    dm_db_column_store_row_group_physical_stats.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
INNER JOIN sys.partitions
ON partitions.partition_number = 
    dm_db_column_store_row_group_physical_stats.partition_number
AND partitions.index_id = indexes.index_id
AND partitions.object_id = tables.object_id

e 3383remamos grupos com 1048576linhas e poucos no final assim:

O problema é que estamos usando a edição padrão (no local) e a operação de reconstrução não é realizada online e causa muitos bloqueios.

Eu nunca vi tal questão antes. Algumas semanas atrás, atualizamos de SQL Server 2016 SP1para SQL Server 2019.

Minhas perguntas são:

• se apenas inserções forem aplicadas, deve ser a operação reorganizee ser mais rápida
• caso contrário, se aplicarmos o particionamento, por exemplo, com base no ano, pois a tabela é usada para registro, o processo de automação reconstruirá apenas os dados da última partição

Eu encontrei o seguinte design de tabela para registrar referências de maneira não normalizada:

RecordGUID              uniqueidentifier
DestinationEntity       uniqueidentifier
DestinationRecordGUID   uniqueidentifier

Por exemplo, para Userstabela para descrever referências a Countries, teremos:

 RecordGUID                             DestinationEntity                       DestinationRecordGUID   
 79282C80-4474-4617-9C40-C21E97F5AC52   3A462EF8-DAB6-4B98-BA1B-FBD957A1737C    5842E524-8C5D-4DEE-A553-0857FB9C12F4
 B25DA064-8DAF-4EEB-8894-D540B3A929EA   3A462EF8-DAB6-4B98-BA1B-FBD957A1737C    C2B0AA1A-E688-4E2C-BD5F-16309C496774

Então, as 3A462EF8-DAB6-4B98-BA1B-FBD957A1737Cmédias Countries, while 5842E524-8C5D-4DEE-A553-0857FB9C12F4e C2B0AA1A-E688-4E2C-BD5F-16309C496774estão referenciando registros na Counriestabela.

Estamos tendo muitas relações aqui - por exemplo, usersto languages, companies, etc. E porque trabalhar com GUIDpara unir as tabelas não é amigável, temos uma visão indexada para cada relação assim:

CREATE VIEW [dbo].[users_countries]
WITH SCHEMABINDING
AS
SELECT [RecordGUID]
      ,[DestinationEntity]
      ,[DestinationRecordGUID]
FROM (the relationship table)
WHERE [DestinationEntity] = '3A462EF8-DAB6-4B98-BA1B-FBD957A1737C';

como resultado, pode-se facilmente fazer uma junção com esta visão à countiestabela e consultá-la - também, por usar a visão (sem expansão) e ter um índice nela, nem todos os registros da tabela original são lidos (é mais rápido ).

O sistema pode ter centenas de visualizações indexadas e estou preocupado com o custo do mecanismo para gerenciá-las, comparado aos índices filtrados na tabela original. Quero dizer, em vez disso, ter viewapenas para criar um índice assim:

CREATE INDEX (index_Name) ON (the relationship table)
(
    [RecordGUID]
   ,[DestinationRecordGUID]
)
WHERE [DestinationEntity] = '3A462EF8-DAB6-4B98-BA1B-FBD957A1737C'

Não podemos mais consultar de maneira amigável a tabela, mas isso afetará o desempenho?

Não consegui encontrar nenhum tópico sobre visualização indexada prejudicando o desempenho - mas enfrentar esse design me deixa em dúvida.

Gostaria de saber se podemos dizer que a manutenção do índice de uma view e do mesmo índice em uma tabela (como a acima) é a mesma?

Eu sei que a resposta da pergunta pode variar dependendo do que exatamente está quebrado em cada .mdfarquivo - apenas procurando conselhos se houver outra maneira de abordar isso.

Então, recebi algumas más notícias do administrador do banco de dados. Algo assim aconteceu ontem à noite (essa é a única informação que tenho dele):

1. uma unidade falhou
2. a unidade foi substituída, mas durante a reconstrução falhou novamente .. e novamente
3. era uma unidade nova, então foi movida para um slot de unidade diferente no chassi
4. fazendo o acima, eu puxei por engano a unidade cachcade que, por sua vez, bloqueou qualquer volume com proteção cachecade
5. depois de reinserir a unidade cachecade, a atividade da unidade foi retomada (as unidades foram para um estado "bloqueado")

Parece que as etapas acima levam à .mdfcorrupção de arquivos e não funcionam corretamente. Então, começamos force attacheda nova instância SQL, seguindo os passos abaixo:

1. Criar novo banco de dados
2. excluir seus arquivos

3. adicione um arquivo corrompido .mdfe ldfe restaure-os com o script abaixo:

   USE master GO
   ALTER DATABASE [] MODIFY FILE(NAME='', FILENAME= '') 
   ALTER DATABASE [] MODIFY FILE(NAME='', FILENAME= '') 
   GO
   
   ALTER DATABASE [] SET ONLINE 
   GO
   DBCC TRACEON(3604) 
   GO
   ALTER DATABASE [] SET EMERGENCY 
   GO
   
   ALTER DATABASE [] SET SINGLE_USER 
   GO
   DBCC CHECKDB('[]', REPAIR_ALLOW_DATA_LOSS) 
   WITH ALL_ERRORMSGS 
   GO
   
   ALTER DATABASE [] SET MULTI_USER 
   GO
   

   Ele também me disse isso Repair errors are different per databasee alguns databasenão podem ser reparados.

Então, eu tenho alguma outra opção aqui ou preciso verificar como funciona o acima para cada banco de dados e mover alguns dados (se possível) para o banco de dados restaurado da cópia mais antiga manualmente?

Eu me pergunto se:

• o nível de isolamento é Read Committed
• os dados são inseridos ou excluídos ou lidos

são os mesmos bloqueios (compartilhados para leitura e exclusivos para inserção/exclusão) usados ​​para a tabela temporária global que a tabela tradicional de armazenamento de linhas.

Para tabela temporária acredito que não haverá bloqueio pois ela "vive" durante o contexto atual, então não consigo ler e inserir dados ao mesmo tempo.

Mas para a tabela temporária global, digamos que eu tenha muitas operações - algumas estão inserindo novos dados, algumas estão excluindo dados e algumas querem ler os dados que serão excluídos.

Digamos que a estrutura da tabela seja:

GroupID EntityID
1       101
1       102
1       103
2       101
2       104
3       100

As mesmas regras são aplicadas aqui?

Na documentação , temos um exemplo de como tabelas temporárias podem ser substituídas por tabelas em memória e porque uma tabela com otimização de memória:

• é armazenado apenas na memória e não possui nenhum componente no disco
• não envolve nenhuma atividade de IO
• não envolve utilização ou contenção de tempdb

Eles disseram isso:

A otimização de memória resulta em aumentos de velocidade que geralmente são 10 vezes mais rápidos ou mais.

Gostaria de saber se nosso sistema já está usando ram para banco de dados tempdb, qual dos benefícios ainda se aplica? Eu acho que eles estão testando o temdb no disco rígido ou ssd e duvidam de obter resultados tão bons.

Mais detalhes. Uma das funções de variável de tabela mais usadas no sistema é a que calcula o acesso a uma entidade específica. Em seguida, o resultado é unido a várias tabelas para obter os dados da solicitação. Para otimizar as últimas junções, estou armazenando o resultado desta função em uma tabela temporária e os resultados são bons.

Mas para volumes muito grandes (por exemplo, quando a função retorna 1-2 milhões de entidades) a execução da função em si é lenta (o que é novamente normal, pois estamos inserindo tantas linhas na variável da tabela).

Então, estou pensando em reescrever essa função como procedimento armazenado e inserir as entidades na tabela de memória, esperando que a operação CRUD com a tabela seja 10 times faster or more.

Com o SQL Server 2019, a Microsoft apresenta suporte a UTF-8CHAR e VARCHARtipos de dados e diz:

Esse recurso pode proporcionar economias de armazenamento significativas, dependendo do conjunto de caracteres em uso. Por exemplo, alterar um tipo de dados de coluna existente com strings ASCII de NCHAR(10) para CHAR(10) usando um agrupamento habilitado para UTF-8 se traduz em quase 50% de redução nos requisitos de armazenamento. Essa redução ocorre porque NCHAR(10) requer 22 bytes para armazenamento, enquanto CHAR(10) requer 12 bytes para a mesma string Unicode.

O UTF-8 parece suportar todos os scripts, então basicamente podemos começar a armazenar dados Unicode em varchare charcolunas. E como é dito na documentação, isso pode reduzir o tamanho das tabelas e índices, e a partir daí podemos obter um desempenho ainda melhor, pois uma quantidade menor de dados é lida.

Eu estou querendo saber se isso significa que podemos parar de usar nvarchare ncharcolunas que implementam UTF-16?

Alguém pode apontar um cenário e motivo, para não usar os tipos de dados char com UTFcodificação e continuar usando os n-chars?

Atualmente, estou usando SQL Server 2016 SPa edição padrão e quero instalar os serviços R. Na documentação é dito que uma instalação autônoma é permitida apenas no Enterprise ( Machine Learning Server (Standalone)).

Além disso, encontrei um post, onde o cara está falando que no padrão não consigo usar operações paralelas e há limitações de memória:

Alguém poderia dizer quais são as limitações exatas de memória/processadores?

Preciso de detalhes como usar cálculos pesados ​​de R e os testes por enquanto mostram que a implementação na edição padrão não está funcionando como eu esperava.

Estou usando a seguinte consulta para verificar se o esquema padrão está definido como dbo.

SELECT DB_NAME() AS [database] 
      ,[name]
      ,[type_desc]
      ,[default_schema_name]
FROM [sys].[database_principals]
WHERE [type] IN ('U', 'G')
    --AND [default_schema_name] <> 'dbo';

Se não for definido como dboe alguém criar um objeto sem especificar um identificador de esquema, um novo schemaserá criado com suas credenciais do Windows.

Então, eu queria escrever um script para verificar (em determinado período de tempo) se tudo está correto (porque novas máquinas e bancos de dados são restaurados/criados diariamente).

O problema com o script acima é que consigo ver que o esquema padrão do meu usuário não é dbo, mas quando executado com outra conta, meu usuário não está listado.

Suponho que seja por causa do WINDOWS_GROUPqual todos somos membros, então preciso verificar suas configurações, mas sempre obter NULLvalor para grupos.

Neste artigo é explicado como descriptografar uma chave simétrica . Por exemplo:

SELECT SK.name, SK.symmetric_key_id, SK.key_length, SK.algorithm_desc,
       KE.crypt_type_desc,
       COALESCE(C.name,AK.name,PSK.name) AS protector_name,
       KE.crypt_property AS encrypted_key,
       COALESCE(DECRYPTBYCERT(C.certificate_id,KE.crypt_property),
                DECRYPTBYASYMKEY(AK.asymmetric_key_id,KE.crypt_property)) AS decrypted_key
  FROM sys.key_encryptions AS KE
  JOIN sys.symmetric_keys AS SK
    ON KE.key_id = SK.symmetric_key_id
  LEFT JOIN sys.certificates AS C
    ON KE.thumbprint = C.thumbprint
  LEFT JOIN sys.asymmetric_keys AS AK
    ON KE.thumbprint = AK.thumbprint
  LEFT JOIN sys.symmetric_keys AS PSK
    ON KE.thumbprint = CAST(PSK.key_guid AS VARBINARY(50));

Ele pode ser testado usando a seguinte consulta:

-- 
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'smGK_MasterKeyPassword@';

-- 
CREATE CERTIFICATE [CERT_V001]
WITH SUBJECT = 'User for protecting SM symetric keys.'


--
CREATE SYMMETRIC KEY [SK_SecurityUsers_V001]
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE [CERT_V001]
GO


DECLARE @Email NVARCHAR(128) = '[email protected]';
DECLARE @EmailEncrypted VARBINARY(256);

OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION
BY CERTIFICATE [CERT_V001];

SELECT @EmailEncrypted = ENCRYPTBYKEY(KEY_GUID('SK_SecurityUsers_V001'),@Email);
SELECT @EmailEncrypted;

CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];


OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION
BY CERTIFICATE [CERT_V001];

SELECT CONVERT(NVARCHAR(128), DECRYPTBYKEY(@EmailEncrypted));

CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];


--DROP SYMMETRIC KEY [SK_SecurityUsers_V001];
--DROP CERTIFICATE [CERT_V001];
--DROP MASTER KEY;

Eu estou querendo saber, isso significa que os dados não estão protegidos?

No artigo é dito que:

No entanto, para chaves protegidas por senha e chave simétrica, isso infelizmente não funciona.

Acho que isso significa que preciso usar um desses tipos de criptografia para ter certeza de que os dados estão protegidos?

Gostaria de saber se as chaves simétricas criadas em determinado banco de dados estão sendo armazenadas no grupo de arquivos primário?

Ou eles são armazenados em algum grupo de arquivos especial com o qual o usuário não pode interagir?

Por exemplo, se estou realizando um backup parcial em um grupo de arquivos (primário ou secundário), posso ter certeza de que o backup não contém as chaves simétricas?

Conhecendo a hierarquia dos objetos de segurança, podemos ver que para criar um Symmetric Keye criptografar dados, precisamos criar:

1. Database Master Keyque é protegido por senha
2. Certificateque está protegido peloDatabase Master key

3. O Symmetric Keypróprio, que é protegido peloCertificate

   

Em Always Encrypted, podemos usar EKMo módulo para simplificar essa hierarquia. Por exemplo, podemos armazenar os certificados no Windows Certification Storee esses certificados estão protegendo nossas chaves mestras de criptografia.

Eu estou querendo saber, se eu quiser usar Always Encryptedfuncionalidades de criptografia não internas, posso usar EKMo módulo para criar e gerenciar minhas chaves simétricas (como é mostrado no diagrama). Na documentação CREATE SYMMETRIC KEY temos provideropção, mas não informações suficientes sobre possíveis provedores e exemplos.

Tenho interesse em armazenar o Certificateque está protegendo as chaves simétricas ou as chaves simétricas em armazenamento externo, pois desta forma os dados são separados das chaves e no banco de dados estamos armazenando apenas referências às chaves (como em sempre criptografado). A Windows Certification Store será a melhor opção para mim, mas o Azure Key Vault ou outra coisa também funcionará, eu acho.

Eu tenho:

• tabela com dados existentes
• SQL Server 2016 SP1
• SQL Server Management Studio 17.5

Estou usando a seguinte declaração para tornar minha tabela temporal:

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT GETUTCDATE()  
   ,[SysEndTime] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2(0), '9999-12-31 23:59:59'),   
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]); 

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]   
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));

O problema:

Na minha instância SQL local tenho muitos bancos de dados; é muito estranho que a consulta seja executada com sucesso em alguns deles, e em alguns deles me dá o seguinte erro:

Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME na tabela 'dbo.AnalysisCustomRollupsV2JoinGroups' falhou porque há registros abertos com início de período definido para um valor no futuro.

Às vezes, quando estou depurando/executando a consulta, a consulta inicial é executada com êxito.

Eu li, que isso pode ser porque eu tenho dados existentes na tabela. Então, eu mudei a lógica assim:

• crie uma tabela de buffer e a preencha com todos os registros
• excluir os registros da tabela original
• criar a tabela temporal
• mova os registros de volta e elimine a tabela de buffer

e novamente, em alguns bancos de dados está OK, e em outros não. Tentando resolver o problema encontrei o seguinte:

Para StartDate, especifiquei a data UTC atual – pode ser qualquer data e hora que não seja no futuro, embora observe que deve ser UTC. Se eu tentasse usar GETDATE, pois estou atualmente no horário de verão britânico, obteria o seguinte erro: Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table 'TestAudit.dbo.SomeData' falhou porque há registros abertos com início de período definido para um valor no futuro.

O que significa acima? Preciso alterar o horário da máquina? Ou porque minha máquina local não está no horário UTC, estou recebendo esse erro às vezes?

Estou testando always encryptedo recurso e uma vez que a operação da coluna de criptografia é concluída, tenho um arquivo com centenas de objetos que não foram atualizados pelo sp_refresh_parameter_encryptionprocedimento.

Devo investigar esses erros e realizar a atualização sozinho? Quais são os riscos de não fazer isso?