Depois de migrar seus dados do Access para o SQL Server, você agora tem um banco de dados cliente/servidor, que pode ser uma solução de nuvem do Azure local ou híbrida. De qualquer forma, o Access agora é a camada de apresentação e o SQL Server é a camada de dados. Agora é um bom momento para repensar aspectos de sua solução, especialmente desempenho de consulta, a segurança e a continuidade do negócio, para que você possa melhorar e dimensionar sua solução de banco de dados.
Para um usuário do Access conhecer pela primeira vez a documentação SQL Server e Azure pode parecer assustadora. Isso pede um tour para guiá-lo pelos destaques relevantes para você. Depois de concluir essa excursão, você estará pronto para explorar os avanços na tecnologia de banco de dados e fazer uma viagem mais longa.
Neste artigo
Promova a continuidade dos negócios
Para a solução do Access, você deseja mantê-la em funcionamento com o mínimo de interrupções, mas as opções com um banco de dados back-end do Access são limitadas. Fazer backup do banco de dados do Access é essencial para proteger seus dados, mas isso exige que os usuários fiquem offline. Além disso, há um tempo de inatividade não planejado causado por atualizações de manutenção de hardware/software, interrupções de rede ou de energia, falhas de hardware, violações de segurança ou até mesmo ataques cibernéticos. Para minimizar o tempo de inatividade e o impacto na sua empresa, você pode fazer o backup de um banco de dados do SQL Server enquanto ele estiver em uso. Além disso, o SQL Server também oferece estratégias de alta disponibilidade (HA) e recuperação de desastres (DR). Essas duas tecnologias combinadas são chamadas de HADR. Para obter mais informações, consulte Continuidade de negócios e recuperação de banco de dados e Impulsionar a continuidade dos negócios com SQL Server (e-book).
Faça backup durante o uso
O SQL Server usa um processo de backup online que pode ocorrer enquanto o banco de dados está sendo executado. Você pode fazer um backup completo, um backup parcial ou um backup de arquivo. Um backup copia dados e logs de transação para garantir uma operação de restauração completa. Especialmente para soluções locais, esteja ciente das diferenças entre as opções de recuperação simples e completa e como elas afetam o crescimento do log de transações. Para obter mais informações, consulte Modelos de Recuperação.
A maioria das operações de backup ocorre imediatamente, exceto as operações de gerenciamento de arquivos e de banco de dados. Por outro lado, se você tentar criar ou excluir um arquivo de banco de dados enquanto uma operação de backup estiver em andamento, a operação falhará. Para obter mais informações, consulte Visão geral do backup.
HADR
As duas técnicas mais comuns para obter alta disponibilidade e continuidade de negócios são espelhamento e clustering. O SQL Server integra as tecnologias de espelhamento e clustering com "Sempre em Instâncias de Cluster de Failover" e "Sempre em Grupos de Disponibilidade".
O espelhamento é uma solução de continuidade a nível de banco de dados que oferece suporte a failover quase instantâneo, mantendo um banco de dados em espera, uma cópia completa ou um espelhamento do banco de dados ativo em hardware separado. Ele pode operar em um modo síncrono (alta segurança), no qual uma transação de entrada é comprometida a todos os servidores ao mesmo tempo, ou em um modo assíncrono (alto desempenho), em que uma transação de entrada é comprometida ao banco de dados ativo e, em seguida, em um ponto pré-determinado, copiada para o espelho. O espelhamento é uma solução a nível de banco de dados e funciona apenas com bancos de dados que usam o modelo de recuperação completa.
O clustering é uma solução a nível do servidor que combina servidores em um único armazenamento de dados que enxerga o usuário como uma única instância. Os usuários se conectam à instância e nunca precisam saber qual servidor da instância está ativo no momento. Se um servidor falhar ou precisar ser desativado para manutenção, a experiência do usuário não será alterada. Cada servidor no cluster é monitorado pelo gerenciador de cluster usando uma pulsação; portanto, ele detecta quando o servidor ativo no cluster fica offline e tenta alternar facilmente para o próximo servidor no cluster, embora haja um atraso de tempo variável durante a alternância.
Para obter mais informações, consulte Always On Instâncias de Cluster de Failover e grupos de disponibilidade Always On: uma solução de alta disponibilidade e recuperação de desastres.
Segurança do SQL Server
Embora você possa proteger seu banco de dados do Access usando a Central de Confiabilidade e criptografando o banco de dados, o SQL Server possui recursos de segurança mais avançados. Vamos examinar três funcionalidades que se destacam para o usuário do Access. Para obter mais informações, consulte Proteção de SQL Server.
Autenticação de banco de dados
Existem quatro métodos de autenticação de banco de dados no SQL Server, cada um dos quais você pode especificar em uma cadeia de conexão ODBC. Para obter mais informações, confira Vincular ou importar dados de um banco de dados do Azure SQL Server. Cada método tem seus próprios benefícios.
Integrado autenticação do Windows Usar credenciais do Windows para validação do usuário, funções de segurança e limitar usuários a recursos e dados. Você pode aproveitar as credenciais de domínio e gerenciar facilmente os direitos de usuário em seu aplicativo. Opcionalmente, insira um SPNs (Nomes de Entidade de Serviço). Para obter mais informações, consulte Escolher um modo de autenticação.
SQL Server Usuários de Autenticação precisam se conectar com credenciais que foram configuradas no banco de dados inserindo a ID de logon e a senha na primeira vez que acessarem o banco de dados em uma sessão. Para obter mais informações, consulte Escolher um modo de autenticação.
Azure autenticação integrada do Active Directory Conecte-se ao Banco de Dados do Servidor SQL do Azure usando Azure Active Directory. Depois de configurar a autenticação do Azure Active Directory, nenhum logon adicional e senha são necessários. Para obter mais informações, consulte Conectar-se a Banco de Dados SQL usando Azure Autenticação do Active Directory.
Autenticação de senha do Active Directory Conecte-se com credenciais que foram configuradas no Azure Active Directory inserindo o nome de logon e a senha. Para obter mais informações, consulte Conectar-se a Banco de Dados SQL usando Azure Autenticação do Active Directory.
Ponta Use a Detecção de Ameaças para receber alertas sobre atividades de banco de dados anômalas indicando possíveis ameaças de segurança a um banco de dados do servidor SQL do Azure. Para obter mais informações, consulte Banco de Dados SQL Detecção de Ameaças.
Segurança de aplicativos
O SQL Server tem dois recursos de segurança a nível de aplicativo que você pode aproveitar com o Access.
Mascaramento dinâmico de dados Ocultar informações confidenciais mascarando-as de usuários não privilegiados. Por exemplo, você pode mascarar os números da Previdência Social, parcial ou integralmente.
Uma máscara de dados parcial |
Uma máscara de dados completa |
|---|
Existem várias maneiras de definir uma máscara de dados, e você pode aplicá-las a diferentes tipos de dados. O mascaramento de dados é orientado por política a nível de tabela e coluna para um conjunto definido de usuários e é aplicado em tempo real para consulta. Para obter mais informações, confira Mascaramento dinâmico de dados.
Segurança no nível da linha Você pode controlar o acesso a linhas de banco de dados específicas com informações confidenciais com base nas características do usuário usando Row-Level Segurança. O sistema de banco de dados aplica essas restrições de acesso e isso torna o sistema de segurança mais confiável e robusto.
Há dois tipos de predicados de segurança:
- Um predicado de filtro filtra linhas de uma consulta. O filtro é transparente e o usuário final não tem conhecimento de nenhuma filtragem.
- Um predicado de bloco impede a ação não autorizada e gera uma exceção se a ação não puder ser executada.
Para obter mais informações, consulte Segurança no nível da linha.
Protegendo Dados com Criptografia
Proteja os dados em repouso, em trânsito e em uso sem afetar o desempenho do banco de dados. Para obter mais informações, consulte SQL Server Criptografia.
Criptografia em repouso Para proteger dados pessoais contra ataques de mídia offline na camada de armazenamento físico, use criptografia em repouso, também chamada TDE (Transparent Data Encryption). Isso significa que seus dados estão protegidos, mesmo se a mídia física for roubada ou descartada indevidamente. A TDE executa criptografia e descriptografia em tempo real de bancos de dados, backups e logs de transações, sem exigir qualquer alteração em seus aplicativos.
Encriptação em trânsito Para proteger contra ataques "man-in-the-middle" e de snooping, pode encriptar os dados transmitidos através da rede. O SQL Server oferece suporte a TLS (Transport Layer Security) 1.2 para comunicações altamente seguras. O protocolo TDS (Tabular Data Stream) também é usado para proteger as comunicações em redes não confiáveis.
Encriptação em utilização no cliente Para proteger os dados pessoais durante a utilização, "Always Encrypted" é a funcionalidade que pretende. Os dados pessoais são criptografados e descriptografados por um driver no computador cliente sem revelar as chaves de criptografia para o mecanismo do banco de dados. Como resultado, os dados encriptados só são visíveis para as pessoas responsáveis pela gestão dos mesmos e não para outros utilizadores altamente privilegiados que não devem ter acesso. Dependendo do tipo de criptografia selecionada, Always Encrypted pode limitar algumas funcionalidades do banco de dados, como pesquisa, agrupamento e indexação de colunas criptografadas.
Lidar com questões de privacidade
As preocupações com privacidade são tão comuns que a União Europeia definiu requisitos legais por meio do Regulamento Geral de Proteção de Dados (GDPR). Felizmente, um back-end do SQL Server é adequado para responder a esses requisitos. Pense em implementar o GDPR em uma estrutura de três etapas.
Passo 1: Avaliar e gerir o risco de conformidade
O GDPR exige que você identifique e inventarie as informações pessoais que você possui em tabelas e arquivos. Essas informações podem ser qualquer coisa, desde um nome, uma foto, um endereço de email, dados bancários, postagens em sites de redes sociais, informações médicas ou até mesmo um endereço IP.
Uma nova ferramenta, Deteção e Classificação de Dados SQL, incorporada no SQL Server Management Studio ajuda-o a detetar, classificar, etiquetar e reportar dados confidenciais ao aplicar dois atributos de metadados a colunas:
- Etiquetas Para definir a sensibilidade dos dados.
- Tipos de informações Para fornecer granularidade adicional sobre os tipos de dados armazenados numa coluna.
Outro mecanismo de descoberta que você pode usar é a pesquisa de texto completo, que inclui o uso dos predicados CONTAINS e FREETEXT e funções com valor de conjunto de linhas como CONTAINSTABLE e FREETEXTTABLE para uso com a instrução SELECT. Usando a pesquisa de texto completo, você pode pesquisar tabelas para descobrir palavras, combinações de palavras ou variações de uma palavra, como sinônimos ou flexões verbais. Para obter mais informações, consulte Pesquisa em Texto Completo.
Passo 2: Proteger informações pessoais
O GDPR exige que você proteja informações pessoais e limite o acesso a elas. Além das etapas padrão que você toma para gerenciar o acesso à sua rede e recursos, como configurações de firewall, você pode usar os recursos de segurança do SQL Server para ajudá-lo a controlar o acesso a dados:
- Autenticação do SQL Server para gerenciar a identidade do usuário e impedir o acesso não autorizado.
- Segurança em nível de linha para limitar o acesso a linhas em uma tabela com base no relacionamento entre o usuário e esses dados.
- Mascaramento Dinâmico de Dados para limitar a exposição a dados pessoais, mascarando-os para usuários não privilegiados.
- Criptografia para garantir que os dados pessoais estejam protegidos durante a transmissão e o armazenamento e estejam protegidos contra comprometimentos, inclusive no lado do servidor.
Para mais informações, confira Segurança do SQL Server.
Etapa 3: Responda eficientemente às solicitações
O GDPR exige que você mantenha registros de processamento de dados pessoais e disponibilize esses registros para as autoridades de supervisão mediante solicitação. Se ocorrerem problemas, incluindo a liberação acidental de dados, os controles de proteção permitirão que você responda rapidamente. Os dados devem estar rapidamente disponíveis quando o relatório for necessário. Por exemplo, o RGPD exige que uma violação de dados pessoais seja comunicada à autoridade de supervisão "não mais do que 72 horas depois de ter tomado conhecimento da mesma".
O SQL Server 2017 ajuda você a relatar tarefas de várias maneiras:
- SQL Server Auditoria ajuda-o a garantir que existem registos persistentes de atividades de processamento e acesso à base de dados. Ela realiza uma auditoria detalhada que rastreia as atividades do banco de dados para ajudar você a entender e identificar possíveis ameaças, suspeitas de abuso ou violações de segurança. Você pode executar prontamente a perícia de dados.
- As tabelas temporais do SQL Server são tabelas de usuário com versão do sistema projetadas para manter um histórico completo de alterações de dados. Você pode usá-las para relatórios fáceis e análises pontuais.
- A Avaliação de Vulnerabilidades do SQL ajuda-o a detetar problemas de segurança e permissões. Quando um problema é detectado, você também pode fazer uma busca detalhada nos relatórios de verificação do banco de dados para localizar ações para resolução.
Para obter mais informações, veja Criar uma plataforma de confiança (e-book) e Percurso para a Conformidade do RGPD.
Crie instantâneos do banco de dados
Um instantâneo do banco de dados é uma exibição somente leitura e estática de um banco de dados do SQL Server em um determinado momento. Embora você possa copiar um arquivo de banco de dados do Access para criar efetivamente um instantâneo de banco de dados, o Access não tem uma metodologia interna como o SQL Server. Você pode usar um instantâneo do banco de dados para gravar relatórios com base nos dados no momento da criação do instantâneo do banco de dados. Você também pode usar um instantâneo de banco de dados para manter dados históricos, como um para cada trimestre financeiro usado para criar relatórios de fim de período. Recomendamos as seguintes práticas:
- Atribua o nome snapshot Cada snapshot de base de dados requer um nome de base de dados exclusivo. Adicione a finalidade e o prazo ao nome para facilitar a identificação. Por exemplo, para obter um instantâneo do banco de dados AdventureWorks três vezes por dia, em intervalos de 6 horas entre as 6h e as 18h com base em um relógio de 24 horas, nomeie-os como AdventureWorks_snapshot_0600, AdventureWorks_snapshot_1200 e AdventureWorks_snapshot_1800.
- Limitar o número de instantâneos Cada base de dados snapshot persiste até que seja explicitamente removida. Como cada instantâneo continuará a crescer, talvez você queira economizar espaço em disco excluindo um instantâneo mais antigo depois de criar um novo. Por exemplo, se você estiver fazendo relatórios diários, mantenha o instantâneo do banco de dados por 24 horas e, em seguida, descarte e substitua por um novo.
- Ligar à snapshot correta Para utilizar uma base de dados snapshot, o front-end do Access tem de saber a localização correta. Quando você substitui um novo instantâneo por um existente, é necessário redirecionar o acesso ao novo instantâneo. Adicione lógica ao front-end do Access para verificar se você está se conectando ao snapshot correto do banco de dados.
Eis como criar uma base de dados snapshot:
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_snapshot_0600' )
AS SNAPSHOT OF AdventureWorks;
Para obter mais informações, veja Database Snapshots (SQL Server).
Controle de simultaneidade
Quando muitas pessoas tentam modificar dados em um banco de dados ao mesmo tempo, é necessário um sistema de controles para que as modificações feitas por uma pessoa não afetem adversamente as de outra pessoa. Isso é chamado de controle de simultaneidade e existem duas estratégias básicas de bloqueio, pessimistas e otimistas. O bloqueio pode impedir que os usuários modifiquem dados de uma maneira que afete outros usuários. O bloqueio também ajuda a garantir a integridade do banco de dados, especialmente com consultas que, de outra forma, podem produzir resultados inesperados. Existem diferenças importantes na maneira como o Access e o SQL Server implementam essas estratégias de controle de simultaneidade.
No Access, a estratégia de bloqueio padrão é otimista e concede a propriedade do bloqueio à primeira pessoa a tentar gravar em um registro. O Access exibe a caixa de diálogo Conflito de Gravação para a outra pessoa tentando gravar no mesmo registro ao mesmo tempo. Para resolver o conflito, a outra pessoa pode salvar o registro ou copiá-lo para a área de transferência, ou descartar as alterações.
Você também pode usar a propriedade RecordLocks para alterar a estratégia de controle de simultaneidade. Essa propriedade afeta formulários, relatórios e consultas, e possui três configurações:
- Sem Bloqueios Num formulário, os utilizadores podem tentar editar o mesmo registo em simultâneo, mas a caixa de diálogo Conflito de Escrita pode aparecer. Em um relatório, os registros não são bloqueados enquanto o relatório é visualizado ou impresso. Em uma consulta, os registros não são bloqueados enquanto a consulta é executada. Esta é a forma com que o Access implementa o bloqueio otimista.
- Todos os Registos Todos os registos na tabela ou consulta subjacente estão bloqueados enquanto o formulário está aberto na vista Formulário ou vista folha de dados, enquanto o relatório é pré-visualizado ou impresso ou enquanto a consulta é executada. Os usuários podem ler os registros durante o bloqueio.
- Registo Editado Para formulários e consultas, uma página de registos é bloqueada assim que qualquer utilizador começa a editar qualquer campo no registo e permanece bloqueada até que o utilizador se mova para outro registo. Consequentemente, um registro pode ser editado por apenas um usuário por vez. Esta é a forma com que o Access implementa o bloqueio pessimista.
Para obter mais informações, veja a caixa de diálogo Conflito de Escrita e a Propriedade RecordLocks.
No SQL Server, o controle de simultaneidade funciona da seguinte maneira:
- Pessimista Depois de um utilizador efetuar uma ação que faz com que seja aplicado um bloqueio, os outros utilizadores não podem executar ações que entrem em conflito com o bloqueio até que o proprietário o liberte. Esse controle de simultaneidade é usado principalmente em ambientes onde há alta contenção de dados.
- Otimista No controlo de simultaneidade otimista, os utilizadores não bloqueiam os dados quando os leem. Quando um usuário atualiza dados, o sistema verifica se outro usuário alterou os dados após a leitura. Se outro usuário atualizou os dados, um erro será gerado. Normalmente, o usuário que recebe o erro reverte a transação e inicia novamente. Esse controle de simultaneidade é usado principalmente em ambientes onde há baixa contenção de dados.
Você pode especificar o tipo de controle de simultaneidade selecionando vários níveis de isolamento de transação, que definem o nível de proteção para a transação de modificações feitas por outras transações usando a instrução SET TRANSACTION:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
| Nível de isolamento | Descrição |
|---|---|
| Leitura não confirmados | As transações são isoladas apenas o suficiente para garantir que os dados fisicamente corrompidos não sejam lidos. |
| Leitura confirmados | As transações podem ler dados previamente lidos por outra transação sem aguardar a conclusão da primeira transação. |
| Leitura repetida | Os bloqueios de leitura e gravação ocorrem nos dados selecionados até o final da transação, mas podem ocorrer leituras fantasmas. |
| Instantâneo | Usa a versão da linha para fornecer consistência de leitura a nível de transação. |
| Serializável | As transações são completamente isoladas umas das outras. |
Para obter mais informações, veja Transaction Locking and Row Versioning Guide (Guia de Controlo de Versões de Linhas e Bloqueio de Transações).
Melhore o desempenho da consulta
Depois de ter uma consulta de passagem do Access funcionando, aproveite as maneiras sofisticadas com que o SQL Server pode executá-la com mais eficiência.
Ao contrário de um banco de dados do Access, o SQL Server fornece consultas paralelas para otimizar a execução de consultas e operações de índice para computadores que possuem mais de um microprocessador (CPU). Como o SQL Server pode executar uma operação de consulta ou índice em paralelo usando diversos threads de trabalho do sistema, a operação pode ser concluída de maneira rápida e eficiente.
As consultas são um componente crítico para melhorar o desempenho geral da sua solução de banco de dados. Consultas inválidas são executadas indefinidamente, esgotam o tempo limite e usam recursos como CPUs, memória e largura de banda de rede. Isso dificulta a disponibilidade de informações críticas de negócios. Até mesmo uma consulta incorreta pode causar sérios problemas de desempenho no seu banco de dados.
Para obter mais informações, veja Consultas mais rápidas com SQL Server (e-book).
Otimização de consulta
Várias ferramentas trabalham em conjunto para o ajudar a analisar o desempenho de uma consulta e a melhorá-la: Otimizador de Consultas, planos de execução e Repositório de Consultas.
Otimizador de consultas
O otimizador de consultas é um dos componentes mais importantes do SQL Server. Use o otimizador de consultas para analisar uma consulta e determinar a maneira mais eficiente de acessar os dados necessários. A entrada para o Query Optimizer consiste na consulta, no esquema do banco de dados (definições de tabela e índice) e nas estatísticas do banco de dados. A saída do otimizador de consultas é um plano de execução.
Para obter mais informações, veja The SQL Server Query Optimizer (Otimizador de Consultas do SQL Server).
Plano de execução
Um plano de execução é uma definição que sequencia as tabelas de origem a serem acessadas e os métodos usados para extrair dados de cada tabela. Otimização é o processo de selecionar um plano de execução a partir de vários possíveis planos. Cada plano de execução possível tem um custo associado na quantidade de recursos de computação usados e o Otimizador de Consultas escolhe aquele com o menor custo estimado.
O SQL Server também deve se ajustar dinamicamente às mudanças de condições no banco de dados. Regressões em planos de execução de consulta podem afetar significativamente o desempenho. Certas mudanças em um banco de dados podem fazer com que um plano de execução seja ineficiente ou inválido, com base no novo estado do banco de dados. O SQL Server detecta as alterações que invalidam um plano de execução e marca o plano como não válido.
Um novo plano deve então ser recompilado para a próxima conexão que executa a consulta. As condições que invalidam um plano incluem:
- Alterações feitas em uma tabela ou exibição referenciada pela consulta (ALTER TABLE e ALTER VIEW).
- Alterações nos índices usados pelo plano de execução.
- Atualizações nas estatísticas usadas pelo plano de execução, geradas explicitamente a partir de uma instrução, como UPDATE STATISTICS, ou automaticamente.
Para obter mais informações, veja Planos de execução.
Repositório de Consultas
O Repositório de Consultas fornece informações sobre a escolha e o desempenho do plano de execução. Ele simplifica a solução de problemas de desempenho, ajudando você a encontrar rapidamente diferenças de desempenho causadas por alterações no plano de execução. A Loja de Consultas reúne dados de telemetria, como um histórico de consultas, planos, estatísticas de runtime e estatísticas de espera. Use a instrução ALTER DATABASE para implementar o Repositório de Consultas:
ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;
Para obter mais informações, veja Monitorizar o desempenho com o Repositório de Consultas.
Correção Automática de Plano
Talvez a maneira mais fácil de melhorar o desempenho da consulta seja com a Correção Automática de Plano, que é um recurso disponível no Banco de Dados SQL do Azure. Você só precisa ligá-la e deixá-la fazer o trabalho. Ela executa o monitoramento e a análise do plano de execução continuamente, detecta planos de execução problemáticos e corrige os problemas de desempenho automaticamente. Nos bastidores, a Correção Automática de Plano usa uma estratégia de quatro etapas: aprender, adaptar, verificar e repetir.
Para obter mais informações, veja Ajuste automático.
Processamento de Consulta Adaptável
Você também pode obter consultas mais rápidas apenas atualizando para o SQL Server 2017, que possui um novo recurso chamado processamento de consulta adaptável. O SQL Server ajusta as opções do plano de consulta com base nas características de runtime.
A estimativa de cardinalidade aproxima o número de linhas processadas em cada etapa em um plano de execução. Estimativas imprecisas podem resultar em lentidão no tempo de resposta da consulta, utilização desnecessária de recursos (memória, CPU e E/S) e na redução da taxa de transferência e simultaneidade. Três técnicas são usadas para se adaptar às características da carga de trabalho do aplicativo:
- Comentários sobre a concessão de memória no modo batch Estimativas de cardinalidade fracas podem fazer com que as consultas "transbordem para o disco" ou levem demasiada memória. O SQL Server 2017 ajusta as concessões de memória com base no feedback de execução, remove os despejos no disco e melhora a simultaneidade para consultas repetidas.
- Associações adaptáveis no modo batch As associações adaptáveis selecionam dinamicamente um tipo de associação interna melhor (associações de ciclo aninhadas, associações de intercalação ou associações hash) durante o runtime, com base nas linhas de entrada reais. Consequentemente, um plano pode alternar dinamicamente para uma melhor estratégia de associação durante a execução.
- Execução intercalada As funções com valores de tabela de várias instruções têm sido tradicionalmente tratadas como uma caixa preta através do processamento de consultas. O SQL Server 2017 pode melhor estimar as contagens de linhas para melhorar as operações downstream.
Você pode tornar cargas de trabalho automaticamente elegíveis para processamento de consulta adaptável, permitindo um nível de compatibilidade de 140 para o banco de dados:
ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 140;
Para obter mais informações, veja Processamento inteligente de consultas em bases de dados SQL.
Formas de consultar
No SQL Server, existem várias maneiras de consultar, e cada uma delas tem seus benefícios. Você quer saber quais são elas, para poder fazer a escolha certa para sua solução do Access. A melhor forma de criar as suas consultas TSQL é editá-las e testá-las interativamente com o editor transact-SQL do SQL Server Management Studio (SSMS), que tem intellisense para ajudá-lo a escolher as palavras-chave e marcar certos para erros de sintaxe.
Exibições
No SQL Server, uma exibição é como uma tabela virtual em que os dados da exibição são provenientes de uma ou mais tabelas ou outras exibições. No entanto, as exibições são referenciadas exatamente como as tabelas nas consultas. As exibições podem ocultar a complexidade das consultas e ajudar a proteger os dados, limitando o conjunto de linhas e colunas. A seguir, um exemplo de uma exibição simples:
CREATE VIEW HumanResources.EmployeeHireDate AS
SELECT p.FirstName, p.LastName, e.HireDate
FROM HumanResources.Employee AS e JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID;
Para otimizar o desempenho e editar os resultados da visualização, crie uma visualização indexada, que persiste no banco de dados como uma tabela, tem armazenamento alocado para ela e pode ser consultada como qualquer tabela. Para usá-la no Access, crie um link para a exibição da mesma forma que você vincula a uma tabela. Este é um exemplo de uma exibição indexada:
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
No entanto, existem restrições. Não pode atualizar dados se mais do que uma tabela base for afetada ou a vista contiver funções de agregação ou uma cláusula DISTINCT. Se o SQL Server retornar uma mensagem de erro informando que não sabe qual registro excluir, talvez seja necessário adicionar um gatilho de exclusão na exibição. Por fim, você não pode usar a cláusula ORDER BY como pode com uma consulta do Access.
Para obter mais informações, veja Vistas e Criar Vistas indexadas.
Procedimentos armazenados
Um procedimento armazenado é um grupo de uma ou mais instruções TSQL que recebem parâmetros de entrada, retornam parâmetros de saída e indicam sucesso ou falha com um valor de status. Eles atuam como uma camada intermediária entre o front-end do Access e o back-end do SQL Server. Os procedimentos armazenados podem ser tão simples quanto uma instrução SELECT ou tão complexos quanto qualquer programa. Aqui está um exemplo:
CREATE PROCEDURE HumanResources.uspGetEmployees
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName
AND EndDate IS NULL;
Quando você usa um procedimento armazenado no Access, ele geralmente retorna um conjunto de resultados para um formulário ou relatório. No entanto, pode executar outras ações que não devolvem resultados, como instruções DDL ou DML. Ao usar uma consulta de passagem, certifique-se de definir a propriedade Devolve Registros adequadamente.
Para obter mais informações, veja Procedimentos armazenados.
Common Table Expressions
Um Common Table Expressions (CTE) é como uma tabela temporária que gera um conjunto de resultados nomeado. Ele existe apenas para a execução de uma única consulta ou instrução DML. Um CTE é construído na mesma linha de código que a instrução SELECT ou a instrução DML que o utiliza, enquanto criar e usar uma tabela ou exibição temporária é geralmente um processo de duas etapas. Aqui está um exemplo:
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
Um CTE tem vários benefícios, incluindo o seguinte:
- Uma vez que os CTEs são transitórios, não tem de criá-los como objetos de base de dados permanentes, como vistas.
- Você pode fazer referência ao mesmo CTE mais de uma vez em uma consulta ou instrução DML, tornando seu código mais gerenciável.
- Você pode usar consultas que fazem referência a um CTE para definir um cursor.
Para obter mais informações, consulte WITH common_table_expression.
Funções Definidas pelo Usuário
Uma função definida pelo usuário (UDF) pode executar consultas e cálculos e retornar valores escalares ou conjuntos de resultados de dados. Elas são como funções em linguagens de programação que aceitam parâmetros, executam uma ação como um cálculo complexo e retornam o resultado dessa ação como um valor. Aqui está um exemplo:
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int WITH SCHEMABINDING -- Helps improve performance
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int;
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
-- Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
-- Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1;
RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';
As UDFs têm certas limitações. Por exemplo, eles não podem usar determinadas funções nãodeterminísticas do sistema, executar instruções DML ou DDL ou fazer consultas SQL dinâmicas.
Para obter mais informações, consulte Funções definidas pelo usuário.
Adicionar chaves e índices
Independentemente de qual seja o sistema de banco de dados que você usa, chaves e índices andam de mãos dadas.
Teclas
No SQL Server, certifique-se de criar chaves primárias para cada tabela e chaves estrangeiras para cada tabela relacionada. O recurso equivalente no SQL Server para o tipo de dados AutoNumber do Access é a propriedade IDENTITY, que pode ser usada para criar valores de chave. Depois de aplicar essa propriedade a qualquer coluna numérica, ela se tornará somente leitura e será mantida pelo sistema de banco de dados. Ao inserir um registro em uma tabela que contém uma coluna IDENTITY, o sistema incrementa automaticamente o valor da coluna IDENTITY em 1 e iniciando em 1, mas você pode controlar esses valores com argumentos.
Para obter mais informações, consulte CREATE TABLE, IDENTITY (Propriedade).
Índices
Como sempre, a seleção de índices é um ato de equilíbrio entre a velocidade da consulta e o custo de atualização. No Access, você tem um tipo de índice, mas no SQL Server você tem doze. Felizmente, você pode usar o otimizador de consulta para ajudá-lo a escolher com segurança o índice mais eficaz. E no SQL do Azure, você pode usar o gerenciamento automático de índices, um recurso de ajuste automático, o qual recomenda para você a adição ou remoção de índices. Ao contrário do Access, você deve criar seus próprios índices para chaves estrangeiras no SQL Server. Você também pode criar índices em uma exibição indexada para melhorar o desempenho da consulta. A desvantagem de uma exibição indexada é maior quando você modifica dados nas tabelas base do modo de exibição, pois o modo de exibição também deve ser atualizado. Para obter mais informações, consulte SQL Server Index Architecture and Design Guide and Indexes.
Realizar transações
Executar um Online Transaction Process (OLTP) é difícil ao usar o Access, mas é relativamente fácil com o SQL Server. Uma transação é uma única unidade de trabalho que confirma todas as alterações de dados quando bem-sucedida, mas reverte as alterações quando não bem-sucedida. Uma transação deve ter quatro propriedades, geralmente chamadas de ACID:
- Atomicidade Uma transação deve ser uma unidade atômica de trabalho; todas as modificações de dados são executadas ou nenhuma é executada.
- Consistência Quando concluída, uma transação deve deixar todos os dados em um estado consistente. Isso significa que todas as regras de integridade de dados são aplicadas.
- Isolamento As alterações feitas por transações simultâneas são isoladas da transação atual.
- Durabilidade Após a conclusão de uma transação, as alterações serão permanentes mesmo em caso de falha no sistema.
As transações são usadas para garantir a integridade de dados, como uma retirada de dinheiro de um caixa eletrônico ou um depósito automático de um cheque. Você pode fazer transações explícitas, implícitas ou com escopo de lote. Aqui estão dois exemplos de TSQL:
-- Using an explicit transaction
BEGIN TRANSACTION;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
COMMIT;
-- the ROLLBACK statement rolls back the INSERT statement, but the created table still exists.
CREATE TABLE ValueTable (id int);
BEGIN TRANSACTION;
INSERT INTO ValueTable VALUES(1);
INSERT INTO ValueTable VALUES(2);
ROLLBACK;
Para obter mais informações, consulte Transações.
Usando restrições e gatilhos
Todos os bancos de dados têm formas de manter a integridade dos dados.
Restrições
No Access, você aplica a integridade referencial em um relacionamento de tabela por meio de emparelhamentos de chave primária-chave estrangeira, atualizações e exclusões em cascata e regras de validação. Para obter mais informações, confira Guia para relações de tabela e Restringir entrada de dados usando regras de validação.
No SQL Server, você usa as restrições UNIQUE e CHECK, que são objetos de banco de dados que reforçam a integridade dos dados nas tabelas do SQL Server. Para validar que um valor é válido em outra tabela, use uma restrição de chave estrangeira. Para validar se um valor em uma coluna está dentro de um intervalo específico, use uma restrição de verificação. Esses objetos são sua primeira linha de defesa e são projetados para funcionar de maneira eficiente. Para obter mais informações, confira Restrições exclusivas e restrições de verificação.
Gatilhos
O Access não possui gatilhos de banco de dados. No SQL Server, você pode usar gatilhos para impor regras complexas de integridade de dados e para executar essa lógica de negócios no servidor. Um acionador de banco de dados é um procedimento armazenado que é executado quando ações específicas ocorrem em um banco de dados. O gatilho é um evento, como adicionar ou excluir um registro em uma tabela, que é acionado e, em seguida, executa o procedimento armazenado. Embora um banco de dados do Access possa garantir a integridade referencial quando um usuário tenta atualizar ou excluir dados, o SQL Server possui um conjunto sofisticado de gatilhos. Por exemplo, você pode programar um gatilho para excluir registros em massa e garantir a integridade dos dados. Você pode até adicionar gatilhos a tabelas e exibições.
Para obter mais informações, consulte Gatilhos – DML, Gatilhos – DDL e Design de um gatilho T-SQL.
Usar colunas computadas
No Access, você cria uma coluna calculada adicionando-a a uma consulta e criando uma expressão, como:
Extended Price: [Quantity] * [Unit Price]
No SQL Server, o recurso equivalente é chamado de coluna computada, que é uma coluna virtual que não é fisicamente armazenada na tabela, a menos que a coluna esteja marcada como PERSISTED. Como uma coluna calculada, uma coluna computada usa dados de outras colunas em uma expressão. Para criar uma coluna computada, adicione-a a uma tabela. Por exemplo:
CREATE TABLE dbo.Products
(
ProductID int IDENTITY (1,1) NOT NULL
, QtyAvailable smallint
, UnitPrice money
, InventoryValue AS QtyAvailable * UnitPrice
);
Para obter mais informações, consulte Especificar colunas computadas em uma tabela.
Adicionar carimbo de data/hora aos dados
Às vezes, você adiciona um campo de tabela para inserir um carimbo de data/hora quando um registro é criado para que você possa registrar em log a entrada de dados. No Access, você pode simplesmente criar uma coluna de data com o valor padrão de =Now(). Para registrar uma data ou hora em SQL Server, use o tipo de dados datetime2 com o valor padrão de SYSDATETIME().
Nota Evite confundir a opção rowversion com a adição de um carimbo de data/hora aos seus dados. A palavra-chave timestamp é um sinônimo de rowversion no SQL Server, mas você deve usar a palavra-chave rowversion. No SQL Server, rowversion é um tipo de dados que expõe números binários exclusivos gerados automaticamente de um banco de dados, e é geralmente usado como um mecanismo para marcação de versão nas linhas da tabela. No entanto, o tipo de dados rowversion é apenas um número de incremento; ele não preserva uma data ou uma hora e tampouco é projetado para carimbo de data/hora em uma linha.
Para obter mais informações, consulte rowversion. Para obter mais informações sobre como usar o rowversion para minimizar conflitos de registros, confira Migrar um banco de dados do Access para o SQL Server.
Gerenciar objetos grandes
No Access, você gerencia dados não estruturados, como arquivos, fotos e imagens, usando o tipo de dados Anexo. Na terminologia do SQL Server, os dados não estruturados são chamados de Blob (Objeto Binário Grande) e existem várias maneiras de trabalhar com eles:
FILESTREAM Usa o tipo de dados varbinary(max) para armazenar os dados não estruturados no sistema de arquivos em vez do banco de dados. Para obter mais informações, consulte Acessar dados FILESTREAM com Transact-SQL.
FileTable Armazena blobs em tabelas especiais chamadas FileTables e fornece compatibilidade com aplicativos Windows como se fossem armazenados no sistema de arquivos e sem fazer alterações em seus aplicativos cliente. FileTable requer o uso de FILESTREAM. Para obter mais informações, consulte FileTables.
Repositório BLOB Remoto (RBS) Armazena BLOBs (objetos grandes binários) em soluções de armazenamento de commodities em vez de diretamente no servidor. Isso economiza espaço e reduz os recursos de hardware. Para obter mais informações, consulte Dados de Blob (Objeto Grande Binário).
Trabalhar com dados hierárquicos
Embora os bancos de dados relacionais, como o Access, sejam muito flexíveis, o trabalho com relacionamentos hierárquicos é uma exceção e geralmente requer instruções ou códigos SQL complexos. Exemplos de dados hierárquicos incluem: uma estrutura organizacional, um sistema de arquivos, uma taxonomia de termos de um idioma e um gráfico de links entre páginas da Web. O SQL Server possui um tipo de dados hierarchyid interno e um conjunto de funções hierárquicas para armazenar, consultar e gerenciar facilmente dados hierárquicos.
Para obter mais informações, consulte Dados hierárquicos e Tutorial: usando o tipo de dados hierarchyid.
Manipular o texto JSON
JavaScript Object Notation (JSON) é um serviço da Web que usa texto legível para transmitir dados como pares atributo-valor na comunicação assíncrona navegador-servidor. Por exemplo:
{
"firstName": "Mary",
"lastName": "Contrary",
"spouse": null,
"age": 27
}
O Access não tem nenhuma maneira interna de gerenciar dados JSON, mas no SQL Server você pode armazenar, indexar, consultar e extrair dados JSON sem problemas. Você pode converter e armazenar texto JSON em uma tabela ou formatar dados como texto JSON. Por exemplo, você pode querer formatar os resultados da consulta como JSON para um aplicativo da Web ou adicionar estruturas de dados JSON em linhas e colunas.
Nota Não há suporte para JSON no VBA. Como alternativa, você pode usar XML no VBA usando a biblioteca MSXML.
Para obter mais informações, consulte dados JSON em SQL Server.
Recursos
Agora é um ótimo momento para aprender mais sobre o SQL Server e o Transact SQL (TSQL). Como você viu, há muitos recursos como o Access, mas também recursos que o Access simplesmente não tem. Para elevar seu aprendizado ao próximo nível, aqui estão alguns recursos:
| Recurso | Descrição |
|---|---|
| Consulta com Transact-SQL | Curso baseado em vídeo |
| Tutoriais do Mecanismo de Banco de Dados | Tutoriais sobre o SQL Server 2017 |
| Microsoft Learn | Aprendizagem prática para o Azure |
| SQL Server treinamento e certificação | Torne-se um especialista |
| SQL Server 2017 | A principal página de destino |
| Documentação SQL Server | Informações de ajuda |
| documentação do banco de dados SQL do Azure | Informações de ajuda |
| O guia essencial para dados na nuvem (e-book) | Uma visão geral da nuvem |
| Folha de dados SQL Server 2017 | Um resumo visual dos novos recursos |
| Comparar a versão do Microsoft SQL Server | Um resumo dos recursos por versão |
| edições Microsoft SQL Server Express | Baixe o SQL Server Express 2017 |
| Bancos de dados de exemplo SQL | Baixar bancos de dados de exemplo |