New Features of SQL Server 2014 (4)-native backup encryption, sql2014

New Features of SQL Server 2014 (4)-native backup encryption, sql2014

After SQL Server 2014 CTP2, a feature for backup was published, that is, native backup encryption. Considering the previous database leaks that have had a bad impact on the network, they are essentially caused by database backup leaks to third parties. The original data backup of SQL Server can be used even if the backup itself is stolen, it cannot be used without an encrypted certificate, which effectively solves the above data leakage problem. Original Solution

Before SQL Server 2014, if you want to encrypt the backup, either of the following two methods will be used:

Use transparent data encryption (TDE) to encrypt the entire database, so that backup and transaction logs are encrypted using a third-party backup encryption tool

First, let's talk about the use of transparent data encryption. If we only want to encrypt the backup, TDE is a little too small, because the use of TDE will cause the database itself and the log to be encrypted, bringing additional overhead to the CPU, in addition, if a database encrypted with TDE uses backup compression, the compression ratio is very low. If you use SQL Server 2014 native backup encryption, you do not need to encrypt the entire database. Backup is only encrypted before being written to the disk. If the original sound backup encryption is combined with the backup compression function, it will first compress and then encrypt to obtain a very high compression ratio.

Second, the use of third-party backup encryption tools requires additional costs, and there will be a relatively cumbersome operation costs during the implementation process, when using third-party backup to encrypt the backup to Azure, native backup encryption can directly encrypt the backup to Azure, providing additional security for OFF-SITE backup.

Use native Data Encryption

This feature is available in SQL Server 2014 Enterprise Edition, BI edition, and Standard Edition. There are two prerequisites for using backup encryption:

Certificate or asymmetric key selection backup Encryption Algorithm

The following uses the original sound data encryption to encrypt the backup, first create a certificate:

CREATE CERTIFICATE BackupCertificate

WITH SUBJECT = 'Backup Encryption Certificate';

GO

After the certificate is created, use the Certificate for backup encryption:

BACKUP DATABASE TestBK TO DISK ='C:\Test_Encrypted.bak'WITH COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCertificate);

As shown in result 1, figure 1 shows that SQL Server prompts you to back up the certificate and the private key of the certificate. Otherwise, the data cannot be used. Generally, in best practices, certificates should be backed up when they are created. data security is always the first priority.

Figure 1. The certificate private key is backed up

In Figure 1, we noticed that the specified backup encryption algorithm is AES_256. SQL Server 2014 provides four backup encryption algorithms: AES128, AES196, AES256, and triple DES.

GUI support for original sound backup encryption is also provided in Management Studio of SQL Server 2014, as shown in figure 2.

Figure 2. GUI support for native backup Encryption

Influence of native encrypted backup on Compression

As mentioned above, the use of native backup encryption can make backup compression almost unaffected by encryption. The following backup is a test database of about Gbps and compression backup is specified. In the first example, only compression backup is used, example 2-4 uses compression backup + different compression algorithms, as shown in test Statement 3.

Figure 3. Test statement

Figure 4 shows that using backup encryption has almost no impact on the Database Backup size.

Figure 4. Different encryption algorithms have almost no impact on backup Compression

Figure 5. Impact of encryption algorithms on backup

According to the conclusions in figures 4 and 5, No matter what encryption algorithm is used, encryption has almost no effect on the compression ratio.

Impact of native encrypted backup on Performance

Using Native backup encryption is a very CPU-consuming operation. Next we will add a non-compressed Backup Based on Figure 3, as shown in figure 6.

Figure 6. Backup time under different compression algorithms

From the test in figure 6, we can see that, except for the time required by the 3DES algorithm, the AES algorithm has no significant impact on the backup time when the CPU does not become the bottleneck of the backup machine. However, the impact of encryption on the specific environment may be due to the environment, and the test results may be different in different environments. If you worry about the impact of native encrypted backup on performance, perform a test in a specific environment.

Summary

SQL Server sound backup encryption provides a very good solution for data security. Using Native backup encryption basically does not increase the size of the backup file, and breaks the dilemma of having almost no compression ratio after using transparent data encryption. Using Native backup encryption can provide extra security protection for data at a very low cost, whether you back up data to a remote data center or back up data to the cloud.

What are the new features of SQL Server 2008 R2?

Trusted and scalable platforms

Offline business applications (LOB) are the key business of the IT and business departments. The key to secure and reliable storage, centralized management, and data distribution to users is these LOB applications. SQL Server 2008 provides a high-performance database platform, a reliable and scalable enterprise platform, and is easy to manage. SQL Server 2008 R2 will help the IT Department provide the most advanced and familiar SQL Server Management Tool Platform today, more cost-effective scalability.

Hardware Innovation

SQL Server 2008 R2 helps you minimize the total cost of ownership by leveraging the advantages of the latest hardware technology. The collaboration between Microsoft Windows Server 2008 R2 and SQL Server 2008 R2 allows customers to expand to up to 256 Logic processors.

In addition, Windows Server 2008, which supports Hyper-V technology, requires greater processing capabilities and fully utilizes the advantages of a new multi-core system. This means that the support of each physical host for multiple virtual systems reduces costs and increases scalability and flexibility of the virtual infrastructure. The real-time migration of the new Hyper-V technology allows servers to be migrated between two hosts without interrupting any services.

IT and development benefits

Allows administrators to centrally monitor and manage multiple database applications, instances or servers, accelerate development and application deployment, and provide better support, online migration is achieved through Windows Server 2008 R2 that supports the Hyper-V function.

Manage self-service business intelligence

Powerful BI tools are available to all Excel and SQL Server users and authorized users for new business accumulation and Sharing powerful business intelligence solutions, IT also enables IT to monitor and manage user-generated BI solutions.

New Features of SQL SERVER 2005

Top 10 latest features of SQL Server 2005

In the business world, everything is competing for "better, faster, and cheaper"-SQL Server 2005 also provides many new features to save energy, time, and money. From programming to management, this version of SQL Server is superior to other versions of products, and it also enhances the existing features of SQL Server 2000. Here I will list the top 10 most important new features based on their importance.

1. Strengthen the T-SQL (Transaction Processing SQL)

T-SQL is born based on the collection of relational database management system programming language, can provide high performance data access. Now, it is combined with many new features, including using TRY and CTACH for error processing at the same time. You can return a common table expression (CTEs) of the result set in the statement ), and the ability to convert a column into a row and a column into a row through the convert and unparse commands.

2. CLR (Common Language Runtime)

The second major enhancement feature in SQL Server 2005 is integration of compliance.. NET standard language, such as C #, ASP. NET or VB that can build objects (stored procedures, triggers, functions, etc.. NET. This allows you to execute. NET code in the database management system to make full use of the. NET function. It is expected to replace the extended stored procedure in the SQL Server 2000 environment, and also extend the functionality of the traditional relational engine.

3. Service Broker)

The Service proxy processes messages between senders and receivers in loose mode. A message is sent, processed, and answered to complete the entire transaction. This greatly extends the performance of data-driven applications to meet workflow or customer business needs.

4. Data Encryption

SQL Server 2000 does not have documented or publicly supported functions used to encrypt data in tables. Enterprises must rely on third-party products to meet this requirement. SQL Server 2005 supports data encryption in user-defined databases.

5. SMTP mail

It is possible to directly send emails in SQL Server 2000, but it is complicated. In SQL Server 2005, Microsoft has improved its mail performance by combining SMTP mail. SQL Server then said "bye-bye" to Outlook "!

6. HTTP Terminal

You can easily create an HTTP terminal by making an object accessible over the Internet through a simple T-SQL statement. This allows you to call a simple object on the Internet to obtain the required data.

7. Multi-activity Result Sets (MARS)

Multi-activity result set allows a persistent connection from a single client to the database, so that more than one active request is allowed on each connection. This is a major performance improvement. It allows developers to provide new capabilities when using SQL Server. For example, it allows multiple queries or one query to input data at the same time. The bottom line is that a client connection can have multiple active processes at the same time.

8. dedicated administrator connection

If all content has an error, you can only disable the SQL Server service or press the power key. The dedicated administrator connection ends. This function allows the database administrator to initiate a single diagnostic connection to SQL Server, even if the Server is experiencing problems.

9. SQL Server Integrated Service (SSIS)

SSIS has replaced DTS (Data Transmission Service) as the main ETL (extraction, transmission, and loading), and is released free of charge with SQL Server. This tool has been completely rewritten since SQL Server 2000, and now has a great deal of flexibility to meet complex data mobile needs.

... The remaining full text>