Change \ Transfer Schema for all Objects
In my working environment we will be using different schema for production and development environments. Our development apps are configured to Dev schema and Production apps are configured to Prod schema, so when we restore the db from production to development environment, all our dev apps stopped working since the objects \ securable will be using prod schema. Here I need to change the schema to dev from prod, so the syntax will be like below
How to Copy \ Move a Database that is encrypted with TDE
In my previous articles on TDE I’ve gone through what’s TDE and how to configure it. Now here in this article I’m going to test whether I’m able to restore \ attach the database without encryption key since the main property of TDE is not to allow to use the physical files of the database without certificate. I’ll also brief about how to copy \ move the database that is encrypted with TDE.
Testing TDE without Certificate in the Instance
In the previous article I’ve encrypted the database TDE_Testing, lets try to use the physical files of this database with different methods to restore \ attach in another instance which doesn’t have this certificate and lets check whether SQL Server 2008 detects this.
Implementing \ Configuring Transparent Data Encryption (TDE)
In my previous article we have discussed what is TDE, how it works, its pros and cons. In this article I’m going to cover how to implement transparent data encryption to your server. It’s very simple to implement TDE, below are the steps that needs to be completed to enable TDE.
- Create a master key
- Create or obtain a certificate protected by the master key
- Create a database encryption key and protect it by the certificate
- Set the database to use encryption
Transparent Data Encryption (TDE) – SQL Server 2008
Introduction
Securing each part and data’s in a database is now an important task for DBA’s to accomplish else the data’s might be hacked. Keeping in mind Microsoft has started encryption features from SQL 2005 and it’s greatly improved it’s feature in SQL Server 2008. Lets recall the encryption features in previous versions of SQL Server.
- SQL Server 2000 – This version of SQL Server is not accompanied with any of the encryption facility. Hence the developers need to write their own code in their client applications to encrypt their data’s.
- SQL Server 2005 – Microsoft has introduced new encryption feature to encrypt column level (some times called as cell level) data’s. This method offers data’s to be encrypted in the database level however applications need to re-architecture to accompany this feature.
- SQL Server 2008 – Ok, In SQL Server 2005 we have encryptions to data’s. What happens if the database files itself is copied \ stolen, so that the data’s can be easily read loosing confidential data’s. To prevent this Microsoft has introduced a new encryption feature in SQL Server 2008 Enterprise edition and this is “Transparent Data Encryption (TDE)”.