Saas模式資料庫層資料架構以及資料刪除處理 (轉)

Saas 模式，所有的模式和服務可以為多個企業服務，每個企業都有自己的一套資料，在同一環境，管理相互隔離的資料，保證資料安全和可用性，是多企業資料架構解決的核心問題！

公司的產品涉及的客戶公司有幾千家，以前的資料庫設計是每個客戶獨立的一個資料庫，通過一個目錄資料庫獨立的訪問各家企業！在應用伺服器配置不同的連線池。不過問題來了，雖然不同的客戶，資料物理分離，安全性好，但是硬體成本太高，資料庫連線效率不高,因此採用了單一資料來源，利用muti-Schema,不同企業通過Schema 的不同，一一對應，這樣共享資料來源與連線池，效率上來說更高，成本更低！不過整個資料庫架構都需要從新設計，可喜的是產品架構設計獨立性，擴充套件性都兼顧了，只需要修改了資料庫層，新增新的程式碼規範，系統很好的過渡到Muti-Schema！

在整個框架過渡中，對於Muti-Schema存在的問題是資料庫的備份，匯入，刪除！資料庫的備份費時，所有的公司只能夠同時備份，不能夠單獨單個企業的處理！資料的匯入大量的應用了SSIS，產品的升級，從舊版本升級到新版本，海量資料的遷移，充分利用了SSIS，做了一個海量資料遷移工具，比起DTS，充分利用SSIS重寫一個遷移工具，針對不同的業務，充分發掘SSIS的優勢，與DTS不在一個級別！

資料庫的刪除，由於不是在單一的資料庫（單一的資料庫直接delete)!刪除資料庫比較麻煩，要刪除一個公司的資料庫，由於所有公司客戶的資料在同一資料庫，公司客戶是針對Schema 來區分的，所以一個解決方案是通過刪除對應公司的Schema 以及Shcema的所有物件！通過指令碼，寫了一個儲存過程，首先找出外來鍵，以及約束，生成指令碼插入到一個零時表，按順序的刪除外來鍵，約束，再view,proc,trigger 相關物件，從而達到刪除相關的公司客戶資料，而不影響其它的公司客戶的資料！

下面是具體的Muti-Schema資料庫刪除指令碼:

<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--&gt  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 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！不斷繼續深入著！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-671387/，如需轉載，請註明出處，否則將追究法律責任。

Saas模式資料庫層資料架構以及資料刪除處理 (轉)

相關文章