Mikel
[转载]Saas模式数据库层数据架构以及数据删除处理 – 展翅高飞 – 博客园.
Saas 模式，所有的模式和服务可以为多个企业服务，每个企业都有自己的一套数据，在同一环境，管理相互隔离的数据，保证数据安全和可用性，是多企业数据架构解决的核心问题！
公司的产品涉及的客户公司有几千家，以前的数据库设计是每个客户独立的一个数据库，通过一个目录数据库独立的访问各家企业！在应用服务器配置不同的连接 池。不过问题来了，虽然不同的客户，数据物理分离，安全性好，但是硬件成本太高，数据库连接效率不高,因此采用了单一数据源，利用muti- Schema,不同企业通过Schema 的不同，一一对应，这样共享数据源与连接池，效率上来说更高，成本更低！不过整个数据库架构都需要从新设计，可喜的是产品架构设计独立性，扩展性都兼顾 了，只需要修改了数据库层，添加新的代码规范，系统很好的过渡到Muti-Schema！
在整个框架过渡中，对于Muti-Schema存在的问题是数据库的备份，导入，删除！数据库的备份费时，所有的公司只能够同时备份，不能够单独单个企业 的处理！数据的导入大量的应用了SSIS，产品的升级，从旧版本升级到新版本，海量数据的迁移，充分利用了SSIS，做了一个海量数据迁移工具，比起 DTS，充分利用SSIS重写一个迁移工具，针对不同的业务，充分发掘SSIS的优势，与DTS不在一个级别！
数据库的删除，由于不是在单一的数据库（单一的数据库直接delete)!删除数据库比较麻烦，要删除一个公司的数据库，由于所有公司客户的数据在同一数 据库，公司客户是针对Schema 来区分的，所以一个解决方案是通过删除对应公司的Schema 以及Shcema的所有对象！通过脚本，写了一个存储过程，首先找出外键，以及约束，生成脚本插入到一个零时表，按顺序的删除外键，约束，再 view,proc,trigger 相关对象，从而达到删除相关的公司客户数据，而不影响其它的公司客户的数据！
下面是具体的Muti-Schema数据库删除脚本:
2 (
3 @SchemaName varchar(100)
4 )
5 AS
6 BEGIN
7 declare @SQL varchar(4000)
8 declare @msg varchar(500)
9
10 — Create temp table ,insert into statement for drop all objects
11 IF OBJECT_ID(‘tempdb..#DropTableCode‘) IS NOT NULL DROP TABLE #DropTableCode
12 CREATE TABLE #DropTableCode
13 (
14 ID int identity(1,1),
15 SQLstatement varchar(1000)
16 )
17
18 — drop all the foreign keys that reference a PK in the target schema
19 SELECT @SQL =
20 ‘select
21 ” ALTER TABLE ”+SCHEMA_NAME(fk.schema_id)+”.”+OBJECT_NAME(fk.parent_object_id)+” DROP CONSTRAINT ”+ fk.name
22 FROM sys.foreign_keys fk
23 join sys.tables t on t.object_id = fk.referenced_object_id
24 where t.schema_id = schema_id(”’ + @SchemaName+”’)
25 and fk.schema_id <> t.schema_id
26 order by fk.name desc‘
27 INSERT INTO #DropTableCode
28 EXEC (@SQL)
29
30 — drop all default constraints, check constraints and Foreign Keys
31 SELECT @SQL =
32 ‘SELECT
33 ” ALTER TABLE ”+schema_name(t.schema_id)+”.”+OBJECT_NAME(fk.parent_object_id)+” DROP CONSTRAINT ”+ fk.[Name]
34 FROM sys.objects fk
35 join sys.tables t on t.object_id = fk.parent_object_id
36 where t.schema_id = schema_id(”’ + @SchemaName+”’)
37 and fk.type IN (”D”, ”C”, ”F”)‘
38 INSERT INTO #DropTableCode
39 EXEC (@SQL)
40
41 — drop all other objects in order such as table,view,procedure .
42 SELECT @SQL =
43 ‘SELECT
44 CASE WHEN SO.type=”PK” THEN ” ALTER TABLE ”+SCHEMA_NAME(SO.schema_id)+”.”+OBJECT_NAME(SO.parent_object_id)+” DROP CONSTRAINT ”+ SO.name
45 WHEN SO.type=”U” THEN ” DROP TABLE ”+SCHEMA_NAME(SO.schema_id)+”.”+ SO.[Name]
46 WHEN SO.type=”V” THEN ” DROP VIEW ”+SCHEMA_NAME(SO.schema_id)+”.”+ SO.[Name]
47 WHEN SO.type=”P” THEN ” DROP PROCEDURE ”+SCHEMA_NAME(SO.schema_id)+”.”+ SO.[Name]
48 WHEN SO.type=”TR” THEN ” DROP TRIGGER ”+SCHEMA_NAME(SO.schema_id)+”.”+ SO.[Name]
49 WHEN SO.type IN (”FN”, ”TF”,”IF”,”FS”,”FT”) THEN ” DROP FUNCTION ”+SCHEMA_NAME(SO.schema_id)+”.”+ SO.[Name]
50 END
51 FROM SYS.OBJECTS SO
52 WHERE SO.schema_id = schema_id(”’+ @SchemaName +”’)
53 AND SO.type IN (”PK”, ”FN”, ”TF”, ”TR”, ”V”, ”U”, ”P”)
54 ORDER BY CASE WHEN type = ”PK” THEN 1
55 WHEN type in (”FN”, ”TF”, ”P”,”IF”,”FS”,”FT”) THEN 2
56 WHEN type = ”TR” THEN 3
57 WHEN type = ”V” THEN 4
58 WHEN type = ”U” THEN 5
59 ELSE 6
60 END‘
61 INSERT INTO #DropTableCode
62 EXEC (@SQL)
63
64 — drop all user–defined data types
65 SELECT @SQL=
66 ‘SELECT
67 ” DROP TYPE ”+”’+@SchemaName+”’+”.”+O.NAME
68 FROM sys.Types o
69 where is_user_defined=1
70 and schema_id=schema_id(”’+@SchemaName+”’)‘
71 INSERT INTO #DropTableCode
72 EXEC(@SQL)
73
74 — create a cursor for temp table ‘#DropTableCode‘ and fetch each statement to drop
75
76 DECLARE @statement varchar(1000)
77 DECLARE statement_cursor CURSOR
78 FOR SELECT SQLStatement
79 FROM #DropTableCode
80 ORDER BY ID ASC
81
82 OPEN statement_cursor
83 FETCH statement_cursor INTO @statement
84 WHILE (@@FETCH_STATUS = 0)
85 BEGIN
86 BEGIN
87 PRINT (@statement)
88 EXEC(@statement)
89 END
90 FETCH statement_cursor INTO @statement
91 END
92 CLOSE statement_cursor
93
94 DEALLOCATE statement_cursor
95 — drop schema name
96 iF UPPER(@SchemaName) = ‘DBO‘ PRINT (‘DBO Will not drop‘)
97 ELSE
98 BEGIN
99 PRINT (‘DROP SCHEMA ‘+@SchemaName)
100 EXEC (‘DROP SCHEMA ‘+@SchemaName)
101 END
102 PRINT ‘——- ALL – DONE ——-‘
103 END
上面的Proc对于 Muti-Schema数据库的设计删除以及海量数据的迁移过程中起到了很大的作用！应为在数据迁移的工程中，迁移工具要做很多工作，比如版本比较，迁移 之后的数据比较是否正确，在开发迁移工具的Test过程中，充分发挥了威力！版本比较，迁移之后数据不一致都需要删除从做，所以上面的Proc起到了很大 的帮助！
数据迁移，海量挖掘，从DTS到SSIS！不断继续深入着！
