[转载]Saas模式数据库层数据架构以及数据删除处理

[转载]Saas模式数据库层数据架构以及数据删除处理 – 展翅高飞 – 博客园.

Saas 模式,所有的模式和服务可以为多个企业服务,每个企业都有自己的一套数据,在同一环境,管理相互隔离的数据,保证数据安全和可用性,是多企业数据架构解决的核心问题!

公司的产品涉及的客户公司有几千家,以前的数据库设计是每个客户独立的一个数据库,通过一个目录数据库独立的访问各家企业!在应用服务器配置不同的连接 池。不过问题来了,虽然不同的客户,数据物理分离,安全性好,但是硬件成本太高,数据库连接效率不高,因此采用了单一数据源,利用muti- Schema,不同企业通过Schema 的不同,一一对应,这样共享数据源与连接池,效率上来说更高,成本更低!不过整个数据库架构都需要从新设计,可喜的是产品架构设计独立性,扩展性都兼顾 了,只需要修改了数据库层,添加新的代码规范,系统很好的过渡到Muti-Schema!

在整个框架过渡中,对于Muti-Schema存在的问题是数据库的备份,导入,删除!数据库的备份费时,所有的公司只能够同时备份,不能够单独单个企业 的处理!数据的导入大量的应用了SSIS,产品的升级,从旧版本升级到新版本,海量数据的迁移,充分利用了SSIS,做了一个海量数据迁移工具,比起 DTS,充分利用SSIS重写一个迁移工具,针对不同的业务,充分发掘SSIS的优势,与DTS不在一个级别!

数据库的删除,由于不是在单一的数据库(单一的数据库直接delete)!删除数据库比较麻烦,要删除一个公司的数据库,由于所有公司客户的数据在同一数 据库,公司客户是针对Schema 来区分的,所以一个解决方案是通过删除对应公司的Schema 以及Shcema的所有对象!通过脚本,写了一个存储过程,首先找出外键,以及约束,生成脚本插入到一个零时表,按顺序的删除外键,约束,再 view,proc,trigger 相关对象,从而达到删除相关的公司客户数据,而不影响其它的公司客户的数据!

下面是具体的Muti-Schema数据库删除脚本:

1 ALTER PROCEDURE [dbo].[DropSchemanameAndAllObjects]
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 userdefined 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!不断继续深入着!

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏