Posts

Showing posts with the label Administering a SQL Database Infrastructure

Insufficient disk space in filegroup

Image
A few days before one of my DBA friend suggested to write a blog on database file growth issues and how to troubleshoot it.  I liked his suggestion as we DBAs often face space issues in production. In this blog, we will see what steps and precautions we can take if a database file growth issue is encountered.   To reproduce the issue below steps have been followed. 1) Created database “Growth” . 2) Added one filegroup named “Growth” and make it default.  3) Added one NDF named “Growth2.ndf” in the filegroup “Growth” and kept it on another drive. 4) Created Table named “employee_details” in filegroup “Growth” 5) Inserted data into the table and created multiple indexes with low fill factor till the datafile is not getting full.  Once the data file got full and there is no space available for new data, then the below error got triggered. The statement has been terminated. Msg 1101, Level 17, State 12, Line 10 Could not allocate a new page for database 'Growth' ...

Configure data access and auditing || a - iii

Qsn) How to Implement Encrypted backup Ans : To create an encrypted backup an encryption algorithm as shown in the following steps: Create a master key.   Create or obtain a certificate protected by the master key. Create a database backup and protect it with the certificate.   The following encryption algorithms can be used:   AES_128 AES_192 AES_256 TRIPLE_DES_3KEY USE   master ; GO CREATE   MASTER   KEY   ENCRYPTION   BY   PASSWORD   = 'XYZ@123; CREATE   CERTIFICATE  BackupCertificate  WITH   SUBJECT   =   'certificte_bkp' ; BACKUP   DATABASE  [trans_DB] TO   DISK   =   N'D:\SQLBackupCRT\trans_DB_Certificate.bak' WITH   ENCRYPTION  ( ALGORITHM   =   AES_256 , SERVER   CERTIFICATE   =  certificte_bkp ); Qsn) How to restore Encrypted backup on database? Ans : In order ...

Configure data access and auditing || a - ii

Qsn)  what is always encrypted? Ans : Always Encrypted (AE) is a new feature in SQL Server 2016 that allows you encrypt both data at rest and data in flight. most important capability is its ability to secure the data with your database outside of the database engine in the client application. AE was designed so that encryption and decryption of the data happens transparently at the driver level, which minimizes the changes that have to be made to existing applications. At a high level the AE architecture works as below : The client application issues a parameterized query. It uses the new  Column Encryption Setting=Enabled;  option in the  connection string . The enhanced ADO.NET driver interrogates the database engine using the [sp_describe_parameter_encryption]   system stored procedure to determine which parameters target encrypted columns. For each parameter that will require encrypting the driver retrieves the encryption al...

Configure data access and auditing || a - i (Part - 2)

Example of Column level Encryption using Certificate USE  [trans_DB] ; GO -- Create database master key CREATE   MASTER   KEY   ENCRYPTION   BY   PASSWORD   =   'GoodLuckWithExam!' -- Create certificate CREATE   CERTIFICATE  Cert_BAN WITH   SUBJECT   =   'Bank Account Number' ; GO -- Create SMK CREATE   SYMMETRIC   KEY  Key_BAN WITH   ALGORITHM   =   AES_256 ENCRYPTION   BY   CERTIFICATE  Cert_BAN ; GO -- Create a column to store encrypted data ALTER   TABLE  Purchasing . Suppliers ADD  EncryptedBankAccountNumber  varbinary ( 128 ); GO -- Open the SMK to encrypt data OPEN   SYMMETRIC   KEY  Key_BAN DECRYPTION   BY   CERTIFICATE  Cert_BAN ; GO -- Encrypt Bank Account Number UPDATE  Purchasing . Suppliers SET  EncryptedBankAccountNumber  =   Encr...