Criar e administrar o banco de dados, criar usuários no SQL Server
Mục lục
Modificar dados do sistema
Os usuários não têm permissão para atualizar informações diretamente nos objetos do banco de dados do sistema, como tabelas do sistema, procedimentos de armazenamento do sistema e visualizações de catálogo (índice). ). No entanto, os usuários podem aproveitar um conjunto completo de ferramentas administrativas que permitem administrar todo o sistema e gerenciar todos os usuários e objetos de banco de dados da seguinte forma:
- Utilitários de administração do SSMS : a partir do SQL Server 2015, vários utilitários de administração do SQL Server são integrados ao SSMS. Este é o console de administração principal para uma instalação do SQL Server. Permite funções administrativas completas de alto nível, agendamento de tarefas de manutenção de rotina, etc.
- API SQL Server Management Objects (SQL-SMO) : inclui recursos completos para administrar aplicativos SQL Server
- Scripts Transact-SQL e procedimentos armazenados : estes são procedimentos armazenados do sistema e instruções T-SQL DDL
Banco de dados definido pelo usuário
No SQL Server, os usuários podem criar seus próprios bancos de dados, chamados de bancos de dados definidos pelo usuário, e trabalhar com eles. O objetivo desses bancos de dados é armazenar dados do usuário.
Cada instância do SQL Server pode conter até 32.767 bancos de dados, cada banco de dados contém até 32.767 arquivos. Ao criar um banco de dados no SQL Server, haverá pelo menos dois arquivos no sistema operacional, um arquivo de dados e um arquivo de log. Os arquivos de dados contêm dados e objetos como tabelas, índices, procedimentos armazenados e exibições. O arquivo de log registra as alterações do banco de dados com a finalidade de arquivar e auxiliar no retorno ao estado do banco de dados
Basicamente banco de dados SQL Server só precisamos nos preocupar com esses arquivos, tudo que pertence a um banco de dados é encapsulado nesses arquivos e você pode trazer o banco de dados de um lugar para outro simplesmente copiando todos esses arquivos para outra máquina (claro que existem outros maneiras como backup/restauração) e anexá-los ao SQL Server nesse servidor.
Em resumo, o Banco de Dados SQL Server possui 2 tipos principais de arquivos:
- O arquivo .mdf é chamado de arquivo de banco de dados principal, contém esquema e dados
- O arquivo .ldf contém logs
- Além disso, o banco de dados pode usar o segundo arquivo de banco de dados, com a extensão .ndf
Criar banco de dados por T-SQL (Criar banco de dados com T-SQL)
Documentos: https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database?view=sql-server-ver15
Sintaxe completa (observação, cláusulas em [ ] são opcionais:
CREATE DATABASE DATABASE_NAME [ON [PRIMARY] [<filespec>[,...n] [,<filegroup>[,...n]] [LOGON {<filespec[,...n]}] ] [collate collation_name] [;]Explique:
- DATABASE_NAME: o nome do banco de dados a ser criado
- ON: indica onde o arquivo está armazenado no disco rígido.
- PRIMARY: associado a <filespec> define os arquivos primários
- <filespec> : controle dos arquivos .atributo
- <filegroup>: controles do atributo <filegroup> .
- LOG ON: indica o arquivo para os arquivos de log.
- COLLATE : especifica o código de agrupamento para o banco de dados, Collation define as regras para comparar e classificar dados de caracteres com base em critérios locais e específicos do idioma (geralmente escolhendo agrupamento em caso de necessidade) o banco de dados pode armazenar dados unicode
Por exemplo:
CREATE DATABASE [Customer] ON PRIMARY (NAME = 'Customer_DBX', FILENAME = 'C:DATACustomer_DB.mdf') LOG ON (NAME = 'Customer_DB_log', FILENAME = 'C:DATACustomer_DB_log.ldf') COLLATE SQL_Latin1_General_CP1_CI_ASApós executar o comando o SQL Server irá criar um novo banco de dados com um arquivo rígido armazenado no caminho C:data
Atualize o Explorador de Objetos para exibir o banco de dados do Cliente novamente
Ou mais sucintamente:
CREATE DATABASE [CustomerDB] -- Voi cach tao nay duong dan file database se do Microsoft SQL dinh nghia
Modificar bancos de dados
À medida que o banco de dados definido pelo usuário aumenta ou diminui, o tamanho do banco de dados será expandido ou reduzido automaticamente ou manualmente. Com base em requisitos que mudam ao longo do tempo, existem algumas situações em que são necessárias modificações no banco de dados.
ALTER DATABASE database_name {<add_or_modify_files> | <add_or_modify_filegroups> | <set_database_options> | MODIFY NAME = new_database_name | COLLATE collation_name } [;]Explique:
- database_name: db. nome
- MODIFY NAME = new_database_name: é o novo nome do banco de dados que você deseja alterar
- collate collation_name: nome de collation do db
- <add_or_modify_files>: adicione arquivos, exclua arquivos ou edite arquivos
- <add_or_modify_filegroups>: grupos de arquivos podem ser adicionados, editados ou excluídos do banco de dados
- <set_database_options>: é uma opção no nível do banco de dados que afeta as propriedades do banco de dados que podem ser definidas para cada banco de dados. Essas opções são exclusivas para cada banco de dados e não afetam outros bancos de dados.
Exemplo para editar o nome do banco de dados:
ALTER DATABASE Customer MODIFY NAME = CusDBCriar usuário do SQL Server (criar usuário do SQL Server)
Por exemplo:
-- Creates the login toanngo92 with password '1234'. CREATE LOGIN toanngo92 WITH PASSWORD = '1234'; GO -- Creates a database user for the login created above. CREATE USER toanngo92 FOR LOGIN toanngo92; GOProprietário do banco de dados (propriedade do banco de dados)
No SQL Server, a propriedade do banco de dados definido pelo usuário pode ser alterada. A propriedade do banco de dados do sistema não pode ser alterada. O procedimento do sistema chamado sp_changedbowner é usado para alterar a propriedade do banco de dados.
Sintaxe:
sp_changedbowner [@loginname=]'login'Com login como o nome de usuário existente.
Depois que sp_changedbowner for executado, um novo proprietário (chamado dbo) será selecionado como proprietário do banco de dados. Dbo terá autoridade para lidar com todas as ações do banco de dados. Proprietário do master, model, tempdb (db no banco de dados do sistema) não pode alterar o proprietário.
Por exemplo:
use 'CusDB' sp_changedbowner 'toanngo92'Depois de executar o comando, o banco de dados CusDB mudou seu proprietário para ‘toanngo92’, então a conta de login chamada ‘toanngo92’ pode manipular diretamente o banco de dados CusDB
Configurando opções de banco de dados
As opções no nível do banco de dados especificam as propriedades do banco de dados e podem ser definidas para cada banco de dados. Essas opções são exclusivas para cada banco de dados e não afetam outros bancos de dados. As opções do banco de dados recebem valores padrão quando o banco de dados é criado, que podem ser alterados usando a cláusula SET na instrução ALTER DATABASE .
Opções de banco de dados no SQL Server
| Tipo de opção | Descrição |
| Opções automáticas | Controlar o comportamento automático do banco de dados |
| Opções do cursor | Controle de comportamento do cursor |
| Opções de recuperação | Controlar o modelo de recuperação (fallback) do banco de dados |
| Opções diversas | ANSI . Controle Padrão |
| Opções de estado | Controlar o estado do banco de dados, como online/offline, conexão do usuário |
Observação: defina as configurações do banco de dados por meio do procedimento denominado sp_configure system stored procedure ou SQL Management Studio.
Exemplo de execução da opção AUTO_SHRINK para o banco de dados CusDB para ON. Opções quando ativadas, o banco de dados diminuirá / diminuirá automaticamente para economizar mais memória (mas esse recurso não é recomendado)
Documentos AUTO_SHRINK: https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/set-the-auto-shrink-database-option-to-off?view=sql-server -2017
USE 'CusDB' ALTER DATABASE 'CusDB' SET AUTO_SHRINK ONGrupos de arquivos
No SQL Server, os arquivos de dados no disco rígido são usados para armazenar o banco de dados. Os arquivos de dados podem ser subdivididos em grupos de arquivos para melhorar o desempenho. Cada grupo de arquivos (filegroup_ usado para agrupar arquivos relacionados armazena um objeto de banco de dados. Todo banco de dados tem um grupo de arquivos principal por padrão. Este grupo de arquivos contém arquivos de dados. O grupo de arquivos principal e o arquivo de dados são criados automaticamente com valores de atributo padrão no momento da administração, alocação de dados e fins de localização.
Por exemplo, existem 3 arquivos chamados customer_data1.ndf , customer_data2.ndf , customer_data3.ndf , que podem ser criados em 3 discos rígidos correspondentes, eles podem ser atribuídos a 1 grupo de arquivos chamado customer_fgroup1. uma tabela pode ser criada especificamente no grupo de arquivos customer_fgroup1. Então uma tabela pode ser criada especificamente no filgroup customer_fgroup1. As consultas de dados da tabela serão distribuídas em três unidades, melhorando ainda mais o desempenho.
Adicionar grupos de arquivos ao banco de dados atual
Os grupos de arquivos podem ser criados quando o banco de dados é criado pela primeira vez ou podem ser criados após a inserção dos arquivos no banco de dados. No entanto, os arquivos não podem ser movidos para outro grupo de arquivos depois que o arquivo é incluído no banco de dados.
O arquivo não pode ser membro de mais de um grupo de arquivos ao mesmo tempo. Apenas um máximo de 32.767 grupos de arquivos são permitidos por banco de dados. Os grupos de arquivos podem conter apenas arquivos de dados. O arquivo de log de transações não pode pertencer ao grupo de arquivos.
Exemplo de criação de um grupo de arquivos que coloca o grupo de arquivos no banco de dados durante a inicialização:
CREATE DATABASE [SalesDB] ON PRIMARY (NAME = 'SalesDB' , FILENAME = 'C:dataSalesDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), FILEGROUP [MyFileGroup] (NAME = 'SalesDB_FG', FILENAME='C:dataSalesDB_FG.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) LOG ON (NAME = 'SalesDB_log', FILENAME='C:dataSalesDB_log.ldf', SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS
Sintaxe ao colocar o grupo de arquivos no banco de dados atual:
ALTER DATABASE database_name (<add_or_modify_files> |<add_or_modify_filegroups> |<set_database_options> |MODIFY NAME=new_database_name |COLLATE collation_name )[;]Por exemplo:
USE 'CusDB' ALTER DATABASE 'CusDB' ADD FILEGROUP FG_ReadonlyApós a execução do comando, o SQL Server exibe a mensagem ‘Comando(s) concluído(s) com sucesso’ e o grupo de arquivos FG_Readonly é inserido no banco de dados atual como ‘CusDB’.
Grupo de arquivos padrão
Os objetos são atribuídos ao grupo de arquivos padrão quando são criados no banco de dados. O grupo de arquivos PRIMARY é o grupo de arquivos padrão. O grupo de arquivos padrão pode ser alterado usando a instrução ALTER DATABASE. Objetos de sistema e tabela permanecem no grupo de arquivos PRIMARY, embora ALTER ainda não entre no novo grupo de arquivos.
Exemplo de como criar um novo arquivo, colocar no grupo de arquivos FG_ReadOnly e definir FG_ReadOnly como grupo de arquivos padrão
USE 'CusDB' ALTER DATABASE 'CusDB' ADD FILE (NAME = CusDB1, FILENAME = 'C:dataCusDB1.ndf') TO FILEGROUP FG_ReadOnly ALTER DATABASE CusDB MODIFY FILEGROUP FG_Readonly DefaultLog de transações
O log de transações no SQL Server registra todas as transações e modificações no banco de dados criado por cada transação. O log de transações é um componente importante no banco de dados. Pode ser a única solução para acessar a fonte de dados recente em caso de falha do sistema
O log de transações suporta as seguintes operações:
- Uma transação incompleta é revertida ou o mecanismo de banco de dados detecta um erro. Os logs são usados para reverter modificações.
- Se o servidor que executa o SQL Server falhar, o banco de dados pode estar em um estado inconsistente. Quando uma instância do SQL Server é iniciada, ela executa uma restauração de cada banco de dados.
- O banco de dados pode ser restaurado até o ponto de falha após a perda de dados do hardware afetar os arquivos do banco de dados.
- O Log Reader Agent monitora o log de transações de cada banco de dados configurado para replicar transações
- As soluções de servidor de backup, espelhamento de banco de dados e envio de logs dependem do log de transações.
Trabalhando com logs de transações:
Os bancos de dados do SQL Server têm pelo menos um arquivo de dados e um arquivo de log de transações. Os dados e informações do log de transações são armazenados separadamente, de preferência em unidades separadas. Esses arquivos são usados por um banco de dados.
O SQL Server usa o log de transações de cada banco de dados para reverter transações. O log de transações é um registro serial que armazena todas as modificações que ocorreram no banco de dados, bem como as transações que realizaram as modificações. Este log contém informações suficientes para desfazer as modificações feitas em cada transação. O log de transações registra a alocação e alocação de páginas, bem como o commit ou rollback de cada transação. Esse recurso permite ao SQL Server mais flexibilidade na restauração do estado dos dados.
A reversão de cada transação pode ser implementada das seguintes maneiras:
- As transações são movidas para frente quando o log de transações é aplicado
- Uma transação é revertida quando é feito backup de uma transação incompleta.
Adicionar arquivos de log ao banco de dados
Sintaxe para editar banco de dados e adicionar arquivos de log:
ALTER DATABASE database_name ( ... ) [;] <add_or_modify_files>::= {ADD FILE <filespec>[,...n] [TO FILEGROUP {filegroup_name|DEFAULT}] | ADD LOG FILE <filespec>[,...n] | REMOVE FILE logical_file_name | MODIFY FILE <filespec> }Criar banco de dados usando SSMS
Etapas para criar um banco de dados usando o SSMS:
- No Pesquisador de Objetos, conecte-se à instância do Mecanismo de Banco de Dados do SQL Server e expanda a instância clicando no ícone ‘+’ ao lado da instância.
- Clique com o botão direito do mouse no banco de dados, clique em New Database como mostrado na Figura 1.1
- Na caixa de diálogo Novo Banco de Dados, insira o nome do banco de dados que deseja criar
- Se o banco de dados for criado com valores padrão, clique em OK para finalizar. Caso contrário, continue escolhendo os parâmetros, configure o banco de dados conforme mostrado na Figura 1.2
- Para alterar o nome do proprietário, clique no botão […] e selecione outro proprietário
- Para alterar os valores padrão dos dados primários e dos arquivos de log de transações, na tabela Arquivos do banco de dados, clique na célula correspondente e insira o valor.
- Para alterar o agrupamento do banco de dados, clique na guia de opções e selecione o agrupamento na lista, conforme mostrado na Figura 1.3
- Para alterar o modelo de recuperação, selecione a guia de opções e selecione o modelo de recuperação na caixa de seleção, conforme mostrado na Figura 1.4
- Para alterar outras opções do banco de dados, edite as informações na guia de opções.
- Para adicionar um grupo de arquivos, clique na guia Grupos de arquivos. A próxima etapa é clicar no botão Adicionar e, em seguida, inserir o grupo de arquivos lieej, conforme mostrado na Figura 1.5
- Para adicionar propriedades high nanag ao banco de dados, selecione a guia Extended Properties
- Na coluna Nome, insira um nome para a propriedade de extensão
- Na coluna Valor, insira um valor para a propriedade de extensão. Por exemplo, podemos preencher 1,2 linhas para descrever o banco de dados.
- Clique em OK para criar o banco de dados
Soltar banco de dados (excluir banco de dados)
Antes de descartar DATABASE, certifique-se de manter alguns backups recentes se o banco de dados for importante, esta é a regra em todos os casos. O banco de dados excluído só pode ser recriado restaurando o backup.
Sintaxe de descarte do banco de dados:
DROP DATABASE [databasename]Para excluir o banco de dados usando o SSMS, faça o seguinte:
- No Pesquisador de Objetos, conecte-se à instância do Mecanismo de Banco de Dados do SQL Server e expanda a instância pressionando o sinal ‘+’.
- Após expandir, selecione banco de dados, clique com o botão direito e clique em Excluir
- Verifique se o banco de dados está selecionado, clique em DELETE
Criar instantâneos de banco de dados
O instantâneo do banco de dados é um recurso introduzido a partir do SQL Server 215. Esse recurso fornece uma visualização rígida e somente leitura para o banco de dados SQL. Se o usuário manipular e executar o comando errado, causando falha no banco de dados, o banco de dados de origem retornará ao estado quando o instantâneo for criado. O SSMS não oferece suporte à criação de instantâneos, mas deve usar instruções T-SQL para fazer isso.
Vantagens dos instantâneos:
- Forneça uma versão de cópia conveniente e somente leitura dos dados
- Quando consultado, nenhuma degradação de desempenho
- O arquivo de instantâneo é leve e rápido para inicializar
Desvantagens dos instantâneos:
- Não foi possível criar backup de instantâneo
- O instantâneo deve existir no mesmo servidor de banco de dados que a origem do banco de dados
- Novos usuários não podem receber direitos de acesso a dados no instantâneo
Sintaxe:
CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name , FILNAME = 'os_file_name' )[,...n] AS SNAPSHOT OF source_database_name [;]Explique:
- database_snapshot_name: nome do instantâneo do banco de dados
- ON (NAME = logical_file_new, FILENAME = ‘os_file_name’): lista de arquivos no banco de dados de origem. Para que os instantâneos funcionem, todos os arquivos de dados devem ser identificados de forma inequívoca
- COMO INSTANTÂNEO DE source_database_name: banco de dados de origem chamado source_database_name
Exemplo de criação de um instantâneo para o banco de dados AdventureWorks2019:
CREATE DATABASE AdvventureWorks_snapshot on (NAME = AdventureWorks2017, FILENAME = 'C:dataAdventureWorks_snapshot.ss') AS SNAPSHOT OF AdventureWorks2019; GOA razão pela qual a declaração em NAME = AdventureWorks2017 e não em 2019 é porque 2019 receberá um erro, leia estes documentos:
Resultado:
Bài viết liên quan:
Dịch vụ thiết kế Wesbite
