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

iSQlServer發表於2010-08-19
模式資料庫架構

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章