/** Created by Mack Bell - Oracle Corporation 2011 **/
/** Additional Functionality by George Vasser - Included STATUS and COLLATION **/
/** Updated 02.11.2014 **/
/** SQL Server 2000 (Version 8) capture is NOT supported as of April 2013.
SQL Server 2005 (Version 9) capture is re-supported as of June 1st 2013, for OGG 11.# only.
SQL Server 2005 (Version 9) delivery is supported under the normal Oracle Fusion Middleware support guidelines.
SQL Server 2008 (Version 10) capture and delivery are supported under the normal Oracle Fusion Middleware support guidelines.
SQL Server 2008 R2 (Version 10) capture and delivery are supported under the normal Oracle Fusion Middleware support guidelines.
SQL Server 2012 (Version 11) capture and delivery are supported under the normal Oracle Fusion Middleware support guidelines (Beginning with OGG 12.1.2.0.1).
Revised query on supported characters in table and column names based on improvements in 11.2.1 (August 2013).
**/
/**
INSTRUCTIONS:
1. Connect to the Source Database in Management Studio with db_owner role at minimum.
2. Ensure that the correct user database is listed in the drop-down list.
3. Set the session output to TEXT, by selecting CTRL+T.
4. Run the script, F5.
5. Review the output.
FUNCTIONALITY:
A. SQL Server instance analysis
i. Exit if SQL Server 2000.
ii. Warn for SQL Server 2008 or 2012 if not Enterprise Edition.
iii. Warn for SQL Server 2005 that only OGG 11.# can be used.
B. Report current database, recovery model, status, and collation.
i. Exit if a System Database (DB_ID <=4) or if dbname = 'distribution'.
ii. Print Database status information and collation.
iii. Warn if not in FULL recovery if to be used as a Source database.
C. Check for Transactional Replication of Database for SQL Server.
i. Warns to use NOMANAGESECONDARYTRUNCTIONPOINT if Transactional.
ii. Warns to use NOMANAGESECONDARYTRUNCTIONPOINT if non-Oracle GoldenGate CDC Enabled.
D. Check whether user tables exist in current database.
i. Exit if no user tables.
ii. Report total number of user tables.
E. Report Table and Data Type counts.
F. Report UnSupported Schema Names.
G. Report UnSupported Table Names.
H. Report UnSupported Column Names
I. Report Columns with UnSupported Data Types (SQL_VARIANT).
J. Report Tables without Primary Keys and without Unique Constraints.
K. Report Tables with Identity Columns.
L. Report Tables with TIMESTAMP columns and no Uniqueness.
M. Report TIMESTAMP Columns as Part of Primary Key or Unique Index.
N. Caution on Max Text Replication Size.
O. Caution on BLOB Data Load Restrictions.
P. Report Tables with Triggers. Only a concern for a target database.
Q. Report Tables with Cascade Delete/Update Constraints. Only a concern for a target database.
**/
/** Set Session Execution Funtions **/
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
/** Drop Temporary Tables **/
IF OBJECT_ID('tempdb..#TempServerInfo') IS NOT NULL
DROP TABLE #TempServerInfo
IF OBJECT_ID('tempdb..#TempSPConfigure') IS NOT NULL
DROP TABLE #TempSPConfigure
IF OBJECT_ID('tempdb..#TempUserTableInfo') IS NOT NULL
DROP TABLE #TempUserTableInfo
GO
/** Create Temporary Tables **/
CREATE TABLE #TempServerInfo
(SQLVersion nvarchar (2000), SQLInstance nvarchar (257))
CREATE TABLE #TempSPConfigure
([name] nvarchar(35), minimum int, maximum int, config_value int, run_value int)
CREATE TABLE #TempUserTableInfo
(IdentID int IDENTITY (1,1) PRIMARY KEY, ObjectID int, SchemaName nvarchar (128)
, TableName nvarchar (128), ColumnID int, ColumnName nvarchar (128)
, DataTypeName nvarchar (128), IsMax bit, HasIdentity bit, HasUC bit, QualifiedTableName nvarchar (261))
/** Declare Session Variables **/
DECLARE @ColumnName nvarchar (128)
DECLARE @ConstraintName nvarchar (128)
DECLARE @CurrentDatabase nvarchar (128)
DECLARE @DataTypeName nvarchar (128)
DECLARE @DBCollation nvarchar (128)
DECLARE @DBStatus nvarchar (128)
DECLARE @EnabledConstraints int
DECLARE @IndexName nvarchar (128)
DECLARE @HasIdentity bit
DECLARE @HasUC bit
DECLARE @IdentityCount int
DECLARE @MaxTextReplConfig int
DECLARE @NoPKCount int
DECLARE @NoUICount int
DECLARE @QualifiedTableName nvarchar (261)
DECLARE @ProcessorType nvarchar (5)
DECLARE @RecoveryModel nvarchar (128)
DECLARE @RequirementCheckNum int
DECLARE @SchemaName nvarchar (128)
DECLARE @SPConfigCMD nchar (40)
DECLARE @SQLServer nchar (4)
DECLARE @SQLServerEntEdition bit
DECLARE @SQLVersionMajorNum nchar (2)
DECLARE @SQLVersionMinorNum nchar (4)
DECLARE @TableName nvarchar (259)--(128 * 2) + 3 for .[] IN String
DECLARE @TimeStampPKUICount int
DECLARE @TransReplEnabled int
DECLARE @TriggerFlag bit
/** A. SQL Server Instance Analysis **/
/********************************** SQL Instance Analysis Section **********************************/
/*****************************************************************************************************/
/** Load SQL Server Instance Information **/
INSERT INTO #TempServerInfo
SELECT @@VERSION,@@SERVERNAME
/** Set Initial Variables **/
SET @ProcessorType = (SELECT UPPER(SUBSTRING(SQLVersion,PATINDEX(N'%)%',SQLVersion)-5,5)) FROM #TempServerInfo)
SET @RequirementCheckNum = 0
SET @SQLServerEntEdition = 0 --Initially sets edition as not Enterprise Edition, which is required for 2008 and 2012 Capture.
SET @SQLVersionMajorNum = (SELECT SUBSTRING(SQLVersion,PATINDEX(N'%.%',SQLVersion)-2,2) FROM #TempServerInfo)
SET @SQLVersionMinorNum = (SELECT SUBSTRING(SQLVersion,PATINDEX(N'%-%',SQLVersion)+7,4) FROM #TempServerInfo)
SET @TransReplEnabled = 0
/** A.i. SQL Server 2000 is no longer supported by OGG as of April 2013 **/
/** If SQL Server 2000, warn and exit **/
IF @SQLVersionMajorNum = 8
BEGIN
SET @SQLServer = '2000'
PRINT N'**************************************************************************************************************'
PRINT N'**************************************************************************************************************'
PRINT N' Oracle GoldenGate no longer supports SQL Server '+@SQLServer+''
PRINT N'**************************************************************************************************************'
PRINT N'**************************************************************************************************************'
PRINT N''
IF (SELECT COUNT(*) FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE N'#TempServerInfo%')>0
BEGIN
DROP TABLE #TempServerInfo
END
IF (SELECT COUNT(*) FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE N'#TempSPConfigure%')>0
BEGIN
DROP TABLE #TempSPConfigure
END
IF (SELECT COUNT(*) FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE N'#TempUserTableInfo%')>0
BEGIN
DROP TABLE #TempUserTableInfo
END
RETURN
END
IF @SQLVersionMajorNum = 9
SET @SQLServer = '2005'
IF @SQLVersionMajorNum = 10
BEGIN
SET @SQLServer = '2008'
IF (SELECT COUNT(SQLVersion) FROM #TempServerInfo WHERE SQLVersion LIKE N'%Enterprise Edition%' OR SQLVersion LIKE N'%Developer Edition%') > 0
SET @SQLServerEntEdition = 1
END
IF @SQLVersionMajorNum = 11
BEGIN
SET @SQLServer = '2012'
IF (SELECT COUNT(SQLVersion) FROM #TempServerInfo WHERE SQLVersion LIKE N'%Enterprise Edition%' OR SQLVersion LIKE N'%Developer Edition%') > 0
SET @SQLServerEntEdition = 1
END
/** Print Report Header Information **/
PRINT N'**************************************************************************************************************'
PRINT N' Analysis of SQL Server '+@SQLServer+' for Oracle GoldenGate'
PRINT N'**************************************************************************************************************'
PRINT N''
/** A.ii. SQL Server 2008 and 2012 require Enterprise Edition for Capture, due to TRANDATA implementation using CDC objects **/
/** Print notice if SQL Server 2008 or 2012 are not Enterprise Edition **/
IF @SQLServerEntEdition = 0 AND @SQLVersionMajorNum >= 10
BEGIN
PRINT N'**************************************************************************************************************'
PRINT N' Oracle GoldenGate for SQL Server '+@SQLServer+' Supports Capture on Enterprise Edition only.'
PRINT N' This edition of SQL Server '+@SQLServer+' is not Enterprise Edition.'
PRINT N'**************************************************************************************************************'
PRINT N''
END
/** A.iii. SQL Server 2005 requires OGG 11.#. OGG 12c cannot be used to Capture from SQL Server 2005. **/
/** Print notice for SQL Server 2005 that only OGG 11.2.1.# is supported for Capture not OGG 12c **/
IF @SQLVersionMajorNum = (9)
BEGIN
PRINT N'**************************************************************************************************************'
PRINT N' Oracle GoldenGate 11.# Supports SQL Server '+@SQLServer+' Capture. OGG 12c Does Not Support SQL Server '+@SQLServer+' Capture.'
PRINT N'**************************************************************************************************************'
PRINT N''
END
/** Print SQL Server Version Information **/
SELECT GETUTCDATE() AS N'=====UTC Start Date/Time====================================================='
SELECT SQLVersion AS N'=====SQL Server Version======================================================' FROM #TempServerInfo
SELECT SQLInstance AS N'=====SQL Server Instance Name================================================' FROM #TempServerInfo
/** B. SQL Server Database Analysis **/
/********************************** Database Analysis Section **************************************/
/*****************************************************************************************************/
/** Print Oracle GoldenGate Header **/
PRINT N'=====Oracle GoldenGate Database Requirement Checks=================================='
PRINT N''
/** Set Database Related Variables **/
SET @CurrentDatabase = (SELECT DB_NAME())
SET @RecoveryModel = (SELECT CAST(DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') AS nvarchar (128)))
SET @DBCollation = (SELECT CAST(DATABASEPROPERTYEX(@CurrentDatabase,'Collation') AS nvarchar (128)))
SET @DBStatus = (SELECT CAST(DATABASEPROPERTYEX(@CurrentDatabase,'Status') AS nvarchar (128)))
/** B.i. Determine if Current Database is a System Database, and Exit if True **/
/** Continue if not System Database **/
IF (SELECT DB_ID()) <= 4 OR (SELECT DB_NAME()) = 'distribution'
BEGIN
PRINT N'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT N' Current Database is '''+@CurrentDatabase+'''.'
PRINT N' Oracle GoldenGate does not support System databases.'
PRINT N' Select a User Database from the drop-down menu and re-run this script.'
PRINT N'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
IF (SELECT COUNT(*) FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE N'#TempServerInfo%')>0
BEGIN
DROP TABLE #TempServerInfo
END
IF (SELECT COUNT(*) FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE N'#TempSPConfigure%')>0
BEGIN
DROP TABLE #TempSPConfigure
END
IF (SELECT COUNT(*) FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE N'#TempUserTableInfo%')>0
BEGIN
DROP TABLE #TempUserTableInfo
END
RETURN
END
/** B.ii. Print Current Database Information **/
PRINT N' Database Name = '+@CurrentDatabase
PRINT N' Recovery Model = '+@RecoveryModel
PRINT N' Database Collation = '+@DBCollation
PRINT N' Database Status = '+@DBStatus
PRINT N''
/** B.iii. Print Recovery Model Warning if not FULL **/
IF @RecoveryModel <> N'FULL'
BEGIN
SET @RequirementCheckNum = @RequirementCheckNum + 1
PRINT N'ORACLE GOLDENGATE WARNING:'
PRINT CAST(@RequirementCheckNum AS nchar (1))+N') If this is to be a Source database for Oracle GoldenGate, it will need to be set to FULL recovery mode'
PRINT N' followed by a FULL database backup.'
PRINT N''
END
/** C. Check for SQL Server Transactional Replication or non-Oracle CDC **/
/*************************************************************************/
/** C.i. Determine if Published for Transactional Replication **/
IF (SELECT CAST(@SQLVersionMajorNum AS int)) IN (9,10,11)
BEGIN
IF (SELECT is_published FROM sys.databases WHERE [name] = @CurrentDatabase) = 1
AND
EXISTS (SELECT [name] FROM sys.objects WHERE [object_id] = OBJECT_ID('syspublications'))
BEGIN
IF (SELECT COUNT(*) FROM syspublications WHERE repl_freq = 0) > 0
BEGIN
SET @RequirementCheckNum = @RequirementCheckNum + 1
SET @TransReplEnabled = 1
PRINT CAST(@RequirementCheckNum AS nchar (1))+N') WARNING: The datatabase '''+@CurrentDatabase+N''' has Transactional Replication enabled.'
PRINT N' You must use TRANLOGOPTIONS NOMANAGESECONDARYTRUNCATIONPOINT for Extract(s).'
PRINT N''
END
END
END
/** C.ii. Determine if non-Oracle GoldenGate CDC enabled for 2008 and 2012 **/
IF (SELECT CAST(@SQLVersionMajorNum AS int)) IN (10,11)
BEGIN
IF EXISTS (SELECT [name] from sys.objects where [name] = 'change_tables' and is_ms_shipped = 1)
BEGIN
IF (SELECT COUNT(capture_instance) FROM [cdc].[change_tables] WHERE capture_instance NOT LIKE 'OracleGG%') > 0
BEGIN
SET @RequirementCheckNum = @RequirementCheckNum + 1
PRINT CAST(@RequirementCheckNum AS nchar (1))+N') WARNING: The datatabase '''+@CurrentDatabase+N''' has a non-Oracle GoldenGate configuration of CDC enabled.'
PRINT N' You must use TRANLOGOPTIONS NOMANAGESECONDARYTRUNCATIONPOINT for Extract(s).'
PRINT N''
END
END
END
/** D. User Table Check and Counts **/
/*************************************/
IF (SELECT CAST(@SQLVersionMajorNum AS int)) >= 9
BEGIN
INSERT INTO #TempUserTableInfo
(ObjectID, SchemaName, TableName, ColumnID, ColumnName, DataTypeName, IsMax, HasUC, QualifiedTableName)
SELECT syst.[object_id] AS ObjectID
,ss.[name] AS SchemaName
,syst.[name] AS TableName
,sc.column_id AS ColumnID
,sc.[name] AS ColumnName
,CASE sc.max_length
WHEN -1 THEN UPPER(st.[name])+N'(MAX)'
ELSE UPPER(st.[name])
END AS DataTypeName
,CASE sc.max_length
WHEN -1 THEN 1
ELSE 0
END AS IsMax
,1
,N'['+ss.[name]+N'].['+syst.[name]+N']' AS QualifiedTableName
FROM sys.tables syst
INNER JOIN sys.schemas ss
ON syst.schema_id=ss.schema_id
INNER JOIN sys.columns sc
ON syst.[object_id]=sc.[object_id]
INNER JOIN sys.types st
ON sc.user_type_id=st.user_type_id--Modified 5/13/2010. Was sc.system_type_id=st.user_type_id
WHERE (CAST(CASE WHEN syst.is_ms_shipped = 1 THEN 1
WHEN (SELECT major_id FROM sys.extended_properties
WHERE major_id = syst.object_id AND minor_id = 0
AND class = 1 AND [name] = N'microsoft_database_tools_support')
IS NOT NULL THEN 1
ELSE 0
END AS bit)=0)
ORDER BY [SchemaName], [TableName] DESC
UPDATE #TempUserTableInfo SET DataTypeName = 'GEOGRAPHY' WHERE DataTypeName = 'GEOGRAPHY(MAX)'
UPDATE #TempUserTableInfo SET DataTypeName = 'GEOMETRY' WHERE DataTypeName = 'GEOMETRY(MAX)'
END
/** D.i. Determine if No User Tables in the Database and Exit if none **/
/** Continue if User Tables are Present **/
IF (
SELECT COUNT(IdentID) FROM #TempUserTableInfo
) = 0
BEGIN
PRINT N'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT N' There are no User Tables in the '''+@CurrentDatabase+N''' Database. Exiting script.'
PRINT N'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
IF (SELECT COUNT(*) FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE N'#TempServerInfo%')>0
BEGIN
DROP TABLE #TempServerInfo
END
IF (SELECT COUNT(*) FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE N'#TempSPConfigure%')>0
BEGIN
DROP TABLE #TempSPConfigure
END
IF (SELECT COUNT(*) FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE N'#TempUserTableInfo%')>0
BEGIN
DROP TABLE #TempUserTableInfo
END
RETURN
END
/** D.ii. Report Total Number of User Tables **/
PRINT N'=====Report Total Number of User Tables in the Database======================'
PRINT N''
SELECT COUNT(*) AS N'User Table Count'
FROM
(SELECT DISTINCT(ObjectID) FROM #TempUserTableInfo)
AS TableCount
/** E. Report All Data Types and Counts for Each **/
PRINT N'=====Report Data Types and Counts for all User Tables in the Database========'
PRINT N''
SELECT CAST(DataTypeName AS nchar(20)) AS N'Data Type'
,COUNT(DataTypeName) AS N'Count/User Tables'
FROM #TempUserTableInfo
GROUP BY DataTypeName
ORDER BY DataTypeName
PRINT N''
/** Print UnSupported Schema, Table, or Column Names **/
PRINT N'=====Report UnSupported Schema, Table, or Column Names======================='
PRINT N''
/** F. Report UnSupported Schema Names **/
PRINT N' Schemas:'
IF (SELECT COUNT(ObjectID) FROM #TempUserTableInfo
WHERE SchemaName LIKE N'%\%'
) > 0
BEGIN
PRINT N' The following Schema.Table(s) are not supported by Oracle GoldenGate due to unsupported Schema Names.'
DECLARE UnSupportedSchemaNames CURSOR FOR
SELECT SchemaName, TableName FROM #TempUserTableInfo
WHERE SchemaName LIKE N'%\%'
GROUP BY SchemaName, TableName
OPEN UnSupportedSchemaNames
FETCH NEXT FROM UnSupportedSchemaNames INTO @SchemaName, @TableName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
PRINT N' ['+@SchemaName+N'].['+@TableName+N']'
FETCH NEXT FROM UnSupportedSchemaNames INTO @SchemaName, @TableName
END
CLOSE UnSupportedSchemaNames
DEALLOCATE UnSupportedSchemaNames
END
ELSE
BEGIN
PRINT N' All Schema Names are supported by Oracle GoldenGate.'
END
PRINT N''
/** G. Report UnSupported Table Names **/
PRINT N' Tables:'
IF (SELECT COUNT(DISTINCT(TableName)) FROM #TempUserTableInfo
WHERE TableName LIKE N'%"%'--" Double quote
OR TableName LIKE N'%.%'--. Period
OR TableName LIKE N'%''%'--' Single quote
OR TableName = N' '--Space - Doesn't work when table name is only a space.
) > 0
BEGIN
PRINT N' The following Tables are not supported by Oracle GoldenGate due to unsupported Characters in the Table Name.'
DECLARE UnSupportedTableNames CURSOR FOR
SELECT SchemaName,TableName FROM #TempUserTableInfo
WHERE TableName LIKE N'%"%'--" Double quote
OR TableName LIKE N'%.%'--. Period
OR TableName LIKE N'%''%'--' Single quote
OR TableName = N' '--Space - Doesn't work when table name is only a space.
GROUP BY SchemaName,TableName
OPEN UnSupportedTableNames
FETCH NEXT FROM UnSupportedTableNames INTO @SchemaName, @TableName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
PRINT N' ['+@SchemaName+N'].['+@TableName+N']'
FETCH NEXT FROM UnSupportedTableNames INTO @SchemaName, @TableName
END
CLOSE UnSupportedTableNames
DEALLOCATE UnSupportedTableNames
END
ELSE
BEGIN
PRINT N' All Table Names are supported by Oracle GoldenGate.'
END
PRINT N''
/** H. Report UnSupported Column Names **/
PRINT N' Columns:'
IF (SELECT COUNT(DISTINCT(ColumnName)) FROM #TempUserTableInfo
WHERE ColumnName LIKE N'%,%'--, Comma
OR ColumnName LIKE N'%"%'--" Double quote
OR ColumnName LIKE N'%''%'--' Single quotes
) > 0
BEGIN
PRINT N' The following Columns are not supported by Oracle GoldenGate due to unsupported Characters in the Column Name.'
DECLARE UnSupportedColumnNames CURSOR FOR
SELECT SchemaName,TableName,ColumnName FROM #TempUserTableInfo
WHERE ColumnName LIKE N'%,%'--, Comma
OR ColumnName LIKE N'%"%'--" Double quote
OR ColumnName LIKE N'%''%'--' Single quotes
GROUP BY SchemaName,TableName,ColumnName
OPEN UnSupportedColumnNames
FETCH NEXT FROM UnSupportedColumnNames INTO @SchemaName, @TableName, @ColumnName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
PRINT N' ['+@SchemaName+N'].['+@TableName+N'].['+@ColumnName+N']'
FETCH NEXT FROM UnSupportedColumnNames INTO @SchemaName, @TableName, @ColumnName
END
CLOSE UnSupportedColumnNames
DEALLOCATE UnSupportedColumnNames
END
ELSE
BEGIN
PRINT N' All Column Names are supported by Oracle GoldenGate.'
END
PRINT N''
/** Print UnSupported Data Types **/
PRINT N'=====Report Data Type Considerations========================================='
PRINT N''
/** I. Report Columns with UnSupported Data Types **/
PRINT N' Data Types:'
IF (SELECT COUNT(DISTINCT(DataTypeName)) FROM #TempUserTableInfo
WHERE (DataTypeName = N'SQL_VARIANT'
OR (DataTypeName IN (N'DECIMAL',N'NUMERIC')
AND OBJECTPROPERTY(OBJECT_ID(QualifiedTableName),N'TableHasVarDecimalStorageFormat') = 1))
) > 0
BEGIN
PRINT N' The following tables are not supported by Oracle GoldenGate due to unsupported Data Types in the Columns.'
DECLARE UnSupportedDataTypes CURSOR FOR
SELECT SchemaName,TableName,ColumnName,DataTypeName FROM #TempUserTableInfo
WHERE (DataTypeName = N'SQL_VARIANT'
OR (DataTypeName IN (N'DECIMAL',N'NUMERIC')
AND OBJECTPROPERTY(OBJECT_ID(QualifiedTableName),N'TableHasVarDecimalStorageFormat') = 1))
GROUP BY SchemaName,TableName,ColumnName,DataTypeName
OPEN UnSupportedDataTypes
FETCH NEXT FROM UnSupportedDataTypes INTO @SchemaName, @TableName, @ColumnName, @DataTypeName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
IF @DataTypeName = N'SQL_VARIANT'
PRINT N' ['+@SchemaName+N'].['+@TableName+N'].['+@ColumnName+N'], DataType = '+@DataTypeName
ELSE
PRINT N' ['+@SchemaName+N'].['+@TableName+N'].['+@ColumnName+N'], DataType = '+@DataTypeName+N' with VARDECIMAL Storage Enabled.'
FETCH NEXT FROM UnSupportedDataTypes INTO @SchemaName, @TableName, @ColumnName, @DataTypeName
END
CLOSE UnSupportedDataTypes
DEALLOCATE UnSupportedDataTypes
END
ELSE
BEGIN
PRINT N' All Data Types are supported by Oracle GoldenGate.'
END
PRINT N''
/** J. Report Tables without Primary Keys if Required, and without any Unique Keys otherwise **/
PRINT N'=====Report Tables without a Primary Key, Unique Contraints, or Unique Indexes========='
PRINT N''
SET @NoPKCount = 0
SET @NoUICount = 0
DECLARE NoPrimaryKeys CURSOR FOR
SELECT DISTINCT(QualifiedTableName) FROM #TempUserTableInfo
ORDER BY QualifiedTableName
OPEN NoPrimaryKeys
FETCH NEXT FROM NoPrimaryKeys INTO @QualifiedTableName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
SET @HasUC = 1
IF (OBJECTPROPERTY (OBJECT_ID(@QualifiedTableName),N'TableHasPrimaryKey') = 0 AND @TransReplEnabled = 1)
BEGIN
SET @HasUC = 0
SET @NoPKCount = @NoPKCount + 1
PRINT N' '+@QualifiedTableName+N' does not have a Primary Key.'
END
IF OBJECTPROPERTY (OBJECT_ID(@QualifiedTableName),N'TableHasPrimaryKey') = 0 AND @TransReplEnabled = 0
BEGIN
SET @HasUC = 0
SET @NoPKCount = @NoPKCount + 1
IF OBJECTPROPERTY (OBJECT_ID(@QualifiedTableName),N'TableHasUniqueCnst') = 0
BEGIN
IF @SQLVersionMajorNum >= 9
BEGIN
DECLARE DetermineUniqueIndex CURSOR FOR
SELECT [name] FROM sys.indexes
WHERE [object_id] = OBJECT_ID(@QualifiedTableName)AND index_id BETWEEN 1 AND 254
END
OPEN DetermineUniqueIndex
FETCH NEXT FROM DetermineUniqueIndex INTO @IndexName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
IF INDEXPROPERTY(OBJECT_ID(@QualifiedTableName),@IndexName, 'IsUnique')= 1
BEGIN
SET @HasUC = 1
END
FETCH NEXT FROM DetermineUniqueIndex INTO @IndexName
END
CLOSE DetermineUniqueIndex
DEALLOCATE DetermineUniqueIndex
IF @HasUC = 0
BEGIN
PRINT N' '+@QualifiedTableName
SET @NoUICount = @NoUICount + 1
END
END
ELSE
SET @HasUC = 1
END
UPDATE #TempUserTableInfo SET HasUC = @HasUC WHERE QualifiedTableName = @QualifiedTableName
FETCH NEXT FROM NoPrimaryKeys INTO @QualifiedTableName
END
CLOSE NoPrimaryKeys
DEALLOCATE NoPrimaryKeys
IF @NoPKCount = 0
BEGIN
PRINT N' All Tables Have a Primary Key or either a Unique Constraint or Unique Index.'
END
ELSE IF @NoPKCount > 0
BEGIN
PRINT N''
PRINT N' **There is/are '+CAST(@NoPKCount as nvarchar (50))+N' table(s) without a Primary Key in database '''+@CurrentDatabase+N'''.'
IF @NoUICount > 0
PRINT N' **There is/are '+CAST(@NoUICount as nvarchar (50))+N' table(s) without any unique key combination(s) in database '''+@CurrentDatabase+N'''.'
END
PRINT N''
/** K. Report Tables with Identity Columns **/
PRINT N'=====Report Tables with an Identity Column========='
PRINT N''
SET @IdentityCount = 0
DECLARE IdentityEnabled CURSOR FOR
SELECT DISTINCT(QualifiedTableName) FROM #TempUserTableInfo
ORDER BY QualifiedTableName
OPEN IdentityEnabled
FETCH NEXT FROM IdentityEnabled INTO @QualifiedTableName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
SET @HasIdentity = 0
IF (OBJECTPROPERTY (OBJECT_ID(@QualifiedTableName),N'TableHasIdentity') = 1)
BEGIN
SET @HasIdentity = 1
SET @IdentityCount = @IdentityCount + 1
PRINT N' '+@QualifiedTableName
END
UPDATE #TempUserTableInfo SET HasIdentity = @HasIdentity WHERE QualifiedTableName = @QualifiedTableName
FETCH NEXT FROM IdentityEnabled INTO @QualifiedTableName
END
CLOSE IdentityEnabled
DEALLOCATE IdentityEnabled
IF @IdentityCount = 0
PRINT N' There are No Tables with Identity Columns.'
ELSE IF @IdentityCount > 0
BEGIN
PRINT N''
PRINT N' There is/are '+CAST(@IdentityCount as nvarchar (50))+N' table(s) with an Identity Column in database '''+@CurrentDatabase+N'''.'
PRINT N' **Seed/Increment values must be staggered for Active-Active configurations. Example: Source1=(0,2), Source2=(1,2).'
PRINT N' **For target tables, consider setting the Identity column property NOT FOR REPLICATION'
PRINT N' and use Replicat parameter DBOPTIONS USEREPLICATIONUSER for best performance.'
END
PRINT N''
/** L. Report tables with TIMESTAMP Columns and no Uniqueness **/
PRINT N'=====Report Tables containing a TIMESTAMP column but no Primary Key, Unique Contraints, or Unique Indexes========='
PRINT N''
IF (SELECT COUNT(tuti.QualifiedTableName) FROM #TempUserTableInfo tuti
WHERE tuti.DataTypeName = N'TIMESTAMP'
AND tuti.HasUC = 0
) > 0
BEGIN
PRINT N' The following reports tables containing a TIMESTAMP column and having no Uniqueness, therefore the entire column list will be'
PRINT N' used by default to generate a psuedo key, of which will contain a TIMESTAMP data type.'
PRINT N' This is not supported by Oracle GoldenGate and an alternate key must be chosen using KEYCOLS, in order to Capture from this table.'
DECLARE TimestampNoUC CURSOR FOR
SELECT tuti.QualifiedTableName FROM #TempUserTableInfo tuti
WHERE tuti.DataTypeName = N'TIMESTAMP'
AND tuti.HasUC = 0
ORDER BY tuti.QualifiedTableName
OPEN TimestampNoUC
FETCH NEXT FROM TimestampNoUC INTO @QualifiedTableName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
PRINT N' '+@QualifiedTableName
FETCH NEXT FROM TimestampNoUC INTO @QualifiedTableName
END
CLOSE TimestampNoUC
DEALLOCATE TimestampNoUC
END
ELSE
BEGIN
PRINT N' No tables with non-unique column combinations exist that contain a TIMESTAMP column.'
END
PRINT N''
/** M. Report TIMESTAMP Columns as Part of Primary Key or Unique Index **/
PRINT N'=====Report Tables containing a TIMESTAMP column as part of a Primary Key or Unique Index========='
PRINT N''
IF (SELECT COUNT(tuti.QualifiedTableName) FROM #TempUserTableInfo tuti
INNER JOIN sys.indexes si ON si.[object_id] = tuti.ObjectID
INNER JOIN sys.index_columns sic ON sic.[object_id] = tuti.ObjectID
WHERE (si.is_primary_key = 1 OR si.is_unique = 1)
AND sic.column_id = tuti.ColumnID
AND sic.index_id = si.index_id
AND tuti.DataTypeName = N'TIMESTAMP'
) > 0
BEGIN
PRINT N' The following reports TIMESTAMP Columns as part of a Primary Key or Unique Index.'
PRINT N' Use KEYCOLS to ensure an alternate key is designated that DOES NOT containt a TIMESTAMP column.'
DECLARE TimestampPKUI CURSOR FOR
SELECT tuti.QualifiedTableName, tuti.ColumnName FROM #TempUserTableInfo tuti
INNER JOIN sys.indexes si ON si.[object_id] = tuti.ObjectID
INNER JOIN sys.index_columns sic ON sic.[object_id] = tuti.ObjectID
WHERE (si.is_primary_key = 1 OR si.is_unique = 1)
AND sic.column_id = tuti.ColumnID
AND sic.index_id = si.index_id
AND tuti.DataTypeName = N'TIMESTAMP'
GROUP BY tuti.QualifiedTableName, tuti.ColumnName
ORDER BY tuti.QualifiedTableName, tuti.ColumnName
OPEN TimestampPKUI
FETCH NEXT FROM TimestampPKUI INTO @QualifiedTableName, @ColumnName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
PRINT N' '+@QualifiedTableName+N'.['+@ColumnName+N']'
FETCH NEXT FROM TimestampPKUI INTO @QualifiedTableName, @ColumnName
END
CLOSE TimestampPKUI
DEALLOCATE TimestampPKUI
END
ELSE
BEGIN
PRINT N' There are no tables with TIMESTAMP columns as part of a Primary Key or Unique Index.'
END
PRINT N''
/** N. Caution on Max Text Replication Size for IMAGE/NTEXT/NVARCHAR(MAX)/TEXT/VARBINARY(MAX)/VARCHAR(MAX) Columns **/
IF (SELECT COUNT(DataTypeName)
FROM #TempUserTableInfo
WHERE DataTypeName IN (N'IMAGE',N'NTEXT',N'NVARCHAR(MAX)',N'TEXT',N'VARBINARY(MAX)',N'VARCHAR(MAX)')
) > 0
BEGIN
SET @SPConfigCMD = N'EXEC sp_configure ''max text repl size'''
INSERT INTO #TempSPConfigure EXEC (@SPConfigCMD)
SET @MaxTextReplConfig = (SELECT run_value FROM #TempSPConfigure)
IF @MaxTextReplConfig < 2147483647
BEGIN
PRINT N'=====NOTE: IMAGE, NTEXT, NVARCHAR(MAX), TEXT, VARBINARY(MAX), or VARCHAR(MAX) column(s) exist,'
PRINT N' therefore, the "Max Text Repl Size" SQL Server Parameter may need to be increased.'
PRINT N' Use "SP_CONFIGURE ''Max Text Repl Size'',
PRINT N' TRANDATA enabled and input data for these columns will be larger than the current Run_Value of'
PRINT N' '+CAST((@MaxTextReplConfig/1024) AS nvarchar (10))+N' KB.'
PRINT N''
BEGIN
PRINT N' The following tables contain IMAGE, NTEXT, NVARCHAR(MAX), TEXT, VARBINARY(MAX), or VARCHAR(MAX) column(s)'
PRINT N''
DECLARE BinaryDataTypes CURSOR FOR
SELECT SchemaName,TableName,ColumnName,DataTypeName FROM #TempUserTableInfo
WHERE (DataTypeName IN (N'IMAGE',N'NTEXT',N'NVARCHAR(MAX)',N'TEXT',N'VARBINARY(MAX)',N'VARCHAR(MAX)'))
GROUP BY SchemaName,TableName,ColumnName,DataTypeName
OPEN BinaryDataTypes
FETCH NEXT FROM BinaryDataTypes INTO @SchemaName, @TableName, @ColumnName, @DataTypeName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
IF @DataTypeName = N'IMAGE' OR @DataTypeName = N'NTEXT' OR @DataTypeName = N'NVARCHAR(MAX)' or @DataTypeName = N'TEXT' or @DataTypeName = N'VARBINARY(MAX)' or @DataTypeName = N'VARCHAR(MAX)'
PRINT N' ['+@SchemaName+N'].['+@TableName+N'].['+@ColumnName+N'], DataType = '+@DataTypeName
FETCH NEXT FROM BinaryDataTypes INTO @SchemaName, @TableName, @ColumnName, @DataTypeName
END
CLOSE BinaryDataTypes
DEALLOCATE BinaryDataTypes
PRINT N''
END
END
END
/** O. Caution on Unsupported DML Operations for IMAGE/NTEXT/NVARCHAR(MAX)/TEXT/VARBINARY(MAX)/VARCHAR(MAX) Columns **/
IF (SELECT COUNT(DataTypeName)
FROM #TempUserTableInfo
WHERE DataTypeName IN (N'IMAGE',N'NTEXT',N'NVARCHAR(MAX)',N'TEXT',N'VARBINARY(MAX)',N'VARCHAR(MAX)')
) > 0
BEGIN
BEGIN
PRINT N'=====NOTE: IMAGE, NTEXT, NVARCHAR(MAX), TEXT, VARBINARY(MAX), or VARCHAR(MAX) column(s) exist.'
PRINT N' The DML statements WRITETEXT and UPDATETEXT, and the binary TEXTCOPY perform minimally logged'
PRINT N' operations and therefore there is no data for OGG to Capture if these methods are used'
PRINT N' by the application.'
PRINT N''
END
END
/** Report Target Database Considerations **/
PRINT N'=====Report Target Database Considerations==================================='
PRINT N''
/** P. Report Triggers **/
PRINT N' The following reports any Triggers installed:'
PRINT N''
SET @TriggerFlag = 0
DECLARE TableTriggers CURSOR FOR
SELECT DISTINCT(QualifiedTableName) FROM #TempUserTableInfo
ORDER BY QualifiedTableName
OPEN TableTriggers
FETCH NEXT FROM TableTriggers INTO @QualifiedTableName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
IF OBJECTPROPERTY (OBJECT_ID(@QualifiedTableName),N'TableInsertTrigger') >1
BEGIN
PRINT N' Table '+@QualifiedTableName+N' Has an INSERT Trigger'
SET @TriggerFlag = 1
END
IF OBJECTPROPERTY (OBJECT_ID(@QualifiedTableName),N'TableUpdateTrigger') >1
BEGIN
PRINT N' Table '+@QualifiedTableName+N' Has an UPDATE Trigger'
SET @TriggerFlag = 1
END
IF OBJECTPROPERTY (OBJECT_ID(@QualifiedTableName),N'TableDeleteTrigger') >1
BEGIN
PRINT N' Table '+@QualifiedTableName+N' Has a DELETE Trigger'
SET @TriggerFlag = 1
END
FETCH NEXT FROM TableTriggers INTO @QualifiedTableName
END
CLOSE TableTriggers
DEALLOCATE TableTriggers
IF @TriggerFlag = 0
PRINT N' There are no Triggers in database '''+@CurrentDatabase+N'''.'
IF @TriggerFlag = 1
BEGIN
PRINT N''
PRINT N' **If the table affected by a TRIGGER operation is configured for OGG Replication, then triggers'
PRINT N' **should either be disabled on the Target for Active-Passive Replication to avoid conflicts'
PRINT N' **or altered to include NOT FOR REPLICATION option and use Replicat parameter'
PRINT N' **DBOPTIONS USEREPLICATIONUSER. The later option is valid for Active-Passive and Active_Active setups.'
END
PRINT N''
/** Q. Report Tables with Cascade Delete/Update Constraints **/
PRINT N' The following reports any enabled Cascade DELETE or UPDATE Constraints:'
PRINT N''
SET @EnabledConstraints = 0
DECLARE TableCascades CURSOR FOR
SELECT DISTINCT(tuti.QualifiedTableName),so.[name] COLLATE Latin1_General_CI_AS AS ConstraintName
FROM #TempUserTableInfo tuti
INNER JOIN sys.objects so
ON so.parent_object_id = tuti.ObjectID
WHERE so.type = N'F'
ORDER BY QualifiedTableName, ConstraintName
OPEN TableCascades
FETCH NEXT FROM TableCascades INTO @QualifiedTableName, @ConstraintName
WHILE (@@FETCH_STATUS <>-1)
BEGIN
IF (OBJECTPROPERTY (OBJECT_ID(@ConstraintName),N'CnstIsDeleteCascade') = 1
AND
OBJECTPROPERTY (OBJECT_ID(@ConstraintName),N'CnstIsDisabled') = 0)
BEGIN
PRINT N' Table '+@QualifiedTableName+N' Has Constraint ['+@ConstraintName+N'] with CASCADE DELETE Operations Enabled.'
SET @EnabledConstraints = @EnabledConstraints + 1
END
IF (OBJECTPROPERTY (OBJECT_ID(@ConstraintName),N'CnstIsUpdateCascade') = 1
AND
OBJECTPROPERTY (OBJECT_ID(@ConstraintName),N'CnstIsDisabled') = 0)
BEGIN
PRINT N' Table '+@QualifiedTableName+N' Has Constraint ['+@ConstraintName+N'] with CASCADE UPDATE Operations Enabled.'
SET @EnabledConstraints = @EnabledConstraints + 1
END
FETCH NEXT FROM TableCascades INTO @QualifiedTableName, @ConstraintName
END
CLOSE TableCascades
DEALLOCATE TableCascades
IF @EnabledConstraints = 0
PRINT N' There are no enabled Cascade operations in database '''+@CurrentDatabase+N'''.'
ELSE
BEGIN
PRINT N''
PRINT N' **If the table affected by a CASCADE operation is configured for OGG Replication, then cascade constraints'
PRINT N' **should either be disabled on the Target for Active-Passive Replication to avoid conflicts'
PRINT N' **or altered to include NOT FOR REPLICATION option and use Replicat parameter'
PRINT N' **DBOPTIONS USEREPLICATIONUSER. The later option is valid for Active-Passive and Active_Active setups.'
END
PRINT N''
/** Print Report Footer Information **/
PRINT N'**************************************************************************************************************'
PRINT N' End Analysis of SQL Server '+@SQLServer+' for Oracle GoldenGate'
PRINT N'**************************************************************************************************************'
PRINT N''
SELECT GETUTCDATE() AS N'=====UTC End Date/Time======================================================='
GO
/** Remove Temporary Tables **/
IF OBJECT_ID('tempdb..#TempServerInfo') IS NOT NULL
DROP TABLE #TempServerInfo
IF OBJECT_ID('tempdb..#TempSPConfigure') IS NOT NULL
DROP TABLE #TempSPConfigure
IF OBJECT_ID('tempdb..#TempUserTableInfo') IS NOT NULL
DROP TABLE #TempUserTableInfo
GO
Nenhum comentário:
Postar um comentário