一 : 解析SQL Server数据应用在不同的数据库中

在软件开发的初始阶段，开发商们总是想把整个系统的最小的细节设计好了，然后再去单线程的编写代码。这样软件开发完成需要很长时间，但开发商们一直都在这么做。

所以开发者不得不去缩小他们的野心，先设计好一个小系统，然而这个小系统只是解决了整个系统难题中的一部分。这导致了由不同的团队设计和创建的许多小系统几乎都不能相互之间兼容。

目前，许多组织都聘请了数据库建模者或者DBA，这些人能监督数据库设计和开发。不幸的是，那些组织只有在出现某些状况后，才会意识到在他们的员工中需要这样的人。

数据建模者和DBA面对的一个非常普遍的问题就是，如何在不同的数据库中解析SQL数据。本文将通过一个可靠的方法来说明这个问题。

一个实例

在这个实例方案中，有两个包含相似数据的数据库：一个使用术语OrderNumber，另一个使用术语OrderNo。第一个数据库有关键字而第二个数据库没有。

首先，你会在两个数据库中发现它们的顺序是相似的。清单A创建了两个数据库（Test_Cross_1和Test_Cross_2），每个数据库中有一个表（分别是Orders_1和Orders_2）。

假设Orders_1包括这些行：

以下为引用的内容： OrderNumberOrderDate             2007-02-23 00:00:00.000             2007-02-24 00:00:00.000             2007-02-25 00:00:00.000             假设Orders_2包括这些行：             OrderNo 　OrderDate             2007-02-23 00:00:00.000             2 　2007-02-24 00:00:00.000             2007-02-24 00:00:00.000             3011 2007-02-25 00:00:00.000

一旦你明白了如何引用表SQL执行合并操作是很简单的。简而言之，你要有合法的名字。如清单B所示。这将不会成功，因为Order_2包括一行Order_1没有的数据。改变连接，加入对外连接符也不会成功，就像你在清单C中看到的那样。第二个查询得到跟第一个查询相同的结果，因为OrderNo 301不存在于第一个表中。找到这一行，你必须在第二个查询中，将这个表的顺序反转。如清单D。现在你就能发现不匹配的行了。

以下为引用的内容： OrderNo 　       OrderDate 　          OrderNumberOrderDate             1 　        2007-02-23 00:00:00.0001 　2007-02-23 00:00:00.000             2 　        2007-02-24 00:00:00.0002 　2007-02-24 00:00:00.000             2007-02-24 00:00:00.000    NULL NULL             2007-02-25 00:00:00.0003   011 2007-02-25 00:00:00.000

假设有一些行存在于Order_1中而不存在于Order_2中。你将上面的查询翻转，它也能工作，然而，随后你会得到两个查询和两个结果集，并结束你的查询，这些你都必须手动比较。如果每个表只有四行，这是不困难的，但是想象一下如果有4,000行记录的话，怎么使用这种不切实际的方法进行比较呢。你必须对两个表的每行没出现在另外那张表的记录进行检查。

以下为引用的内容： CREATE DATABASE [Test_Cross_1] ON PRIMARY             ( NAME = N'Test_Cross_1', FILENAME = N'             C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATATest_Cross_1.mdf',             SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )             LOG ON ( NAME = N'Test_Cross_1_log',             FILENAME = N'C:aProgram FilesMicrosoft             SQL ServerMSSQL.1MSSQLDATATest_Cross_1_log.ldf',             SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GO             USE [Test_Cross_1]             GO             CREATE TABLE [dbo].[Orders_1]             ( [OrderNumber] [int] NOT NULL,             [OrderDate] [datetime] NOT NULL,             CONSTRAINT [PK_Orders_1] PRIMARY KEY CLUSTERED             ( [OrderNumber] ASC)WITH (PAD_INDEX = OFF,             STATISTICS_NORECOMPUTE = OFF,             IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,             ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]             CREATE DATABASE [Test_Cross_2] ON PRIMARY             ( NAME = N'Test_Cross_2',             FILENAME = N'C:Program FilesMicrosoft             SQL ServerMSSQL.1MSSQLDATATest_Cross_2.mdf',             SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )             LOG ON ( NAME = N'Test_Cross_2_log',             FILENAME = N'C:Program FilesMicrosoft             SQL ServerMSSQL.1MSSQLDATATest_Cross_2_log.ldf',             SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GO             CREATE TABLE [dbo].[Orders_2]             ( [OrderNo] [int] NOT NULL, [OrderDate] [datetime]             NOT NULL CONSTRAINT [DF_Orders_2_OrderDate]             DEFAULT (getdate()), CONSTRAINT [PK_Orders_2]             PRIMARY KEY CLUSTERED ( [OrderNo] ASC)WITH             (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,             IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,             ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

清单A

以下为引用的内容： SELECT * FROM Test_Cross_1.dbo.Orders_1INNER             JOIN Test_Cross_2.dbo.Orders_2             ON Test_Cross_1.dbo.Orders_1.OrderNumber

清单B

以下为引用的内容： SELECT * FROM Test_Cross_1.dbo.Orders_1LEFT OUTER             JOIN Test_Cross_2.dbo.Orders_2             ON Test_Cross_1.dbo.Orders_1.OrderNumber

清单C

以下为引用的内容： SELECT * FROM Test_Cross_2.dbo.Orders_2LEFT OUTER             JOIN Test_Cross_1.dbo.Orders_1             ON Test_Cross_2.dbo.Orders_2.OrderNo

清单D

二 : SQL Server中生成测试数据

简介

     在实际的开发过程中。[www.61k.com］很多情况下我们都需要在数据库中插入大量测试数据来对程序的功能进行测试。而生成的测试数据往往需要符合特定规则。虽然可以自己写一段程序来进行插入数据，但每一个项目就写一个插入数据的程序并不明智。本文主要介绍使用VS2010的数据生成计划在SQL Server中生成测试数据。

 

生成测试数据的方法

    1.手动编辑

          在开发过程中，非常少量的数据可以手动插入。这个方法的缺点可想而知….插入100条数据就够你忙乎一上午了。

 

   2.写程序&T-SQL语句进行插入

             这个缺点也是显而易见的，开发效率同样底下。对于再次开发不同的程序时，程序需要修改或者重写。甚至对于每一个表就要写一段代码，并且生成的数据灵活性并不高！

         比如对一个表生成1000条数据我可能就需要写这么多T-SQL:

       

        可以看出，这种方法不仅麻烦，生成的测试数据也可能不符合我们需要的。

 

     3. 使用已经上线的系统的数据

          嗯，这个方法貌似不错.简单容易，数据量足。但先抛开新系统或完全不同的系统表结构改变可能无法使用已经上线的数据这个因素之外。拿客户的商业数据进行测试..这个也太没节操了吧……

 

使用VS2010的数据生成计划来生成测试数据

     VS2010提供的数据生成计划是一个强大的工具。它可以高效的生成测试数据，其中内置的数据生成规则可以很容易的让我们实现生成所需数据。下面来看一个实际的例子:

     为了简便起见，所生成的数据的构架只有两个表(员工表和部门表),用外键连接:

   

      在VS2010创建数据库项目，添加SQL Server 2008数据库项目,然后添加数据生成计划:

   

    在VS2010中建立数据库连接，添加新项，在数据生成计划中，可以看到这两个表了:

   

    通过指定列的属性，我可以调整我所生成的数据的规范:

   

    下面，我为Employee表的几个列数据进行指定,Name列，我指定最小长度为4，最大长度为6.Gender列只允许有两个值，男和女.而Email按照正则表达式，生成符合Email地址规范的值:

   

    性别列指定只有男和女

   

    邮件列指定邮件的正则表达式

    在数据生成计划中，VS2010提供的强大功能还有外键约束生成数据。上面两个表中，假设公司有1000名员工，有10个部分，对应的每生成一个部门数据则生成100个员工数据，我可以在“相关表”和“相关表设置里进行”:

   

    一切准备就绪后，我可以通过按F5生成数据:

  

   在SSMS中查看数据:

   

    可以看到，数据基本符合我所需要生成的数据

扩展：sqlserver 生成数据 / sql server 测试数据 / sql生成测试数据

三 : SQL Server 2008 安全性——透明数据加密(TDE)

SQL Server 2008安全性——透明数据加密(TDE)

               在SQL Server 2005的安全性来说，做的不够好，比如EKM这种可扩展的外部密钥管理就不支持，包括对数据库文件本身的加密。[www.61k.com]

               SQL Server 2008中增加了透明数据加密TDE，TDE使用DEK 数据库加密密钥对数据文件进行加密，包括IO操作，加入内存等都是存在加密和解密操作的。DEK本身是受master数据库下的证书保护的，当然也支持EKM模块包含。我们对数据文件可以使用标准的DES 和AES加密。

               在某些特定场合，比如我们的服务器发生盗窃行为的时候，一些关键性数据库不被恶意附加挖掘其中的价值数据，除了使用硬件级别的文件格式加密。SQL Server 用TDE来保护文件安全。

               我主要通过T-SQL 脚本的形式描述 整个加密以及分离、附加数据库的流程。

 /*

Title:TDE加密
Author:浪客
Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise
Description:请在非生产环境下测试
*/

USE [master];
GO

--查看master数据库是否被加密
SELECT name,is_master_key_encrypted_by_server FROM sys.databases;

--创建master数据库下的主数据库密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'浪客!@#$%^&*()0A';

--查看master数据库下的密钥信息
SELECT * FROM sys.symmetric_keys;

--创建证书用来保护 数据库加密密钥 (DEK)
CREATE CERTIFICATE master_server_cert WITH SUBJECT = N'Master Protect DEK Certificate';

IF DB_ID('db_encryption_test') IS NOT NULL
    DROP DATABASE db_encryption_test

--创建测试数据库
CREATE DATABASE  db_encryption_test;
GO

USE db_encryption_test;

--创建由master_server_cert保护的DEK 数据库加密密钥 （对称密钥）
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE master_server_cert;
GO

--执行上语句以后出现：
/*
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
*/
--提示你，立刻备份证书;这里备份证书,不比制定加密私钥的 对称密钥了.因为他的密钥是通过master数据库的主数据库密钥加密了.
USE master;
BACKUP CERTIFICATE master_server_cert TO FILE = 'D:\MSSQL\Certificate\master_server_cert.cer' 
    WITH PRIVATE KEY ( 
    FILE = 'D:\MSSQL\Certificate\master_server_cert.pvk' , 
    ENCRYPTION BY PASSWORD = '浪客!@#$%^&*()0A' );

--相应的，我们也备份一下数据库主密钥(master)
USE master;
--如果没有启用主密钥的自动解密功能
--OPEN MASTER KEY DECRYPTION BY PASSWORD = '浪客!@#$%^&*()0A';
BACKUP MASTER KEY TO FILE = 'D:\MSSQL\MasterKey\master.cer' 
    ENCRYPTION BY PASSWORD = '浪客!@#$%^&*()0A';
GO 

--生产环境下，设置成单用户在运行加密
ALTER DATABASE db_encryption_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--备份成功以后，开启TDE 加密
ALTER DATABASE db_encryption_test SET ENCRYPTION ON;
GO

--设置多用户访问
ALTER DATABASE db_encryption_test SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

--查看db_encryption_test数据库是否被加密  encryption_state:3 TDE加密了
SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;
/*
发现tempdb也被加密了。MSDN解释是：如果实例中有一个数据库启用了TDE加密，那么tempdb也被加密
*/

--接下来,找另外一台机器或者实例来测试,如果数据文件被盗走了,防止附加的测试.
USE master;
EXEC sp_detach_db N'db_encryption_test';
GO

--将文件QQ发到了另外的机器,我同事 CL .


USE master;
--我先在他机器还原了MASTER KEY (他原机器master库无master key)
RESTORE MASTER KEY 
    FROM FILE = 'C:\Users\Administrator\Desktop\master.cer' 
    DECRYPTION BY PASSWORD = '浪客!@#$%^&*()0A' 
    ENCRYPTION BY PASSWORD = '浪客!@#$%^&*()0A';
GO

--如果没有自动加密
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'浪客!@#$%^&*()0A';
--创建证书
CREATE CERTIFICATE master_server_cert 
    FROM FILE = 'C:\Users\Administrator\Desktop\master_server_cert.cer' 
    WITH PRIVATE KEY (FILE = 'C:\Users\Administrator\Desktop\master_server_cert.pvk', 
    DECRYPTION BY PASSWORD = '浪客!@#$%^&*()0A');
GO 
--附加数据库
CREATE DATABASE db_encryption_test 
ON PRIMARY 
(
    FILENAME=N'C:\Users\Administrator\Desktop\db_encryption_test.mdf'
)
LOG ON 
(
    FILENAME=N'C:\Users\Administrator\Desktop\db_encryption_test_log.ldf'
)
FOR  ATTACH ;
GO

--测试成功,GG,GL

--关闭数据库联接
CLOSE MASTER KEY

         通常我们使用某种新特性的时候，还会关注他对SQL Server其他组件的影响，包括复制服务以及数据库镜像以及日志传送。复制是通过标志事务日志传送标识日志的方式同步数据，当时通过bcp生成的快照文件，不会采用TDE加密数据，这种存在传输信道的 我们通常使用OS的IpSec，SSL,TSL方式来加密连接，这种在以后的文章会向大家提到。而数据镜像和日志通过传送日志的方式，所以传输过程中的日志文件是加密的。

/Files/bhtfg538/MSSQL/Security/Encryption/encry_1.txt 

本文标题：sql server数据库-解析SQL Server数据应用在不同的数据库中
本文地址： http://www.61k.com/1066588.html