about_DbSmo.Help.txt
|
To Do
----- Test-SQLPS and Fix-SQLPS add a tracking number / start end time thing add more fine grained time tracking, like cumulative per path per server add more error tracking where the write-verbose are, along with descriptions of why; add breakpoints go through old files looking for improvements Move the check data type into mainline and make smaller See if you can get it working on only one path, or pass in a sub path only reduce output, increase speed Modules Subfunctions Confirm whether decimals are written properly Server/DefaultData can be wrong, but rewritten if you want EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\MSSQL\Data\MSSQL10.MSSQLSERVER\MSSQL\DATA' vs SET @registry_key = N'Software\Microsoft\MSSQLServer\Setup'; EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path output; SET @path = @path + '\Data'; So basically Coalesce(DefaultFile, Server.InstallDataDirectory + '\Data') Bug Fixes --------- 01. Bunch of tables with SyncRoot columns instead of being iterated properly. This happens when the ICollection.GetEnumerator() isn't called and instead it has dropped through to be recursed as a non-array. Check: Select 'Select ''' + Schema_Name(t.schema_id) + '.' + t.name + ''' As TableName, * From ' + schema_name(t.schema_id) + '.' + t.name From sys.tables t Join sys.columns c On t.object_id = c.object_id Where c.name = 'syncroot' Order By t.name 02. A "Default" table for database defaults was being populated even though it was excluded. This happened when I'd put "Server/Default" into the exclusion list while the actual property name is "Server/Defaults". 03. Exceptions when adding a DataColumn. This was a bug after calling Get-SmoDataSetType with a System.Object; it couldn't convert it. I had special logic to handle this but had misspelt the special comparison for MemberType from ScriptProperty to ScriptMethod. 04. A bunch of tables weren't being generated anymore. This was when ICollection wasn't being recursed and instead dropping through to the non-array recurse. 05. Exception when enumerating availabilty groups; "Operation not supported on version". This is avoided with an exception check by type. 06. Exception when enumerating databases on a mirrored instance and where the database is not available. This is avoided with an exception check by number (the type is generic). 07. Dates not being returned as null. These really weren't null, I've added in code to convert the magic numbers to null. 08. When creating tables they weren't having primary keys added. I'd caught an exception but then printed it verbosely and continued on; never noticing it was failing. I had rewritten some code that was checking for a property between two objects and where the property names were slightly different and failing on one. 09. IPAddressToString wasn't being populated. This is a weird ScriptProperty on objects that has a TypeName of System.Object. 10. Foreign key names were incorrect. Because I was experimenting with changing it to get the column names from the parent primary key; but it was not checking properly whether to put a prefix on it or not. I ended up doing this by also comparing the parent primary key to the parent foreign key (if it's part of the foreign key, don't add a prefix; otherwise it's for our direct parent so we should add a prefix to the parent's column name in our table so as not to create clashes). Check: select s.name, f.name, o1.name, c1.name, o2.name, c2.name from ops2.sys.foreign_keys f join ops2.sys.objects o1 on f.parent_object_id = o1.object_id join ops2.sys.schemas s on o1.schema_id = s.schema_id join ops2.sys.objects o2 on f.referenced_object_id = o2.object_id join ops2.sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id join ops2.sys.columns c1 on fc.parent_object_id = c1.object_id and fc.parent_column_id = c1.column_id join ops2.sys.columns c2 on fc.referenced_object_id = c2.object_id and fc.referenced_column_id = c2.column_id except select s.name, f.name, o1.name, c1.name, o2.name, c2.name from ops.sys.foreign_keys f join ops.sys.objects o1 on f.parent_object_id = o1.object_id join ops.sys.schemas s on o1.schema_id = s.schema_id join ops.sys.objects o2 on f.referenced_object_id = o2.object_id join ops.sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id join ops.sys.columns c1 on fc.parent_object_id = c1.object_id and fc.parent_column_id = c1.column_id join ops.sys.columns c2 on fc.referenced_object_id = c2.object_id and fc.referenced_column_id = c2.column_id order by 1, 2, 3, 4, 5, 6 11. Error doing bulk copy from string to boolean. This was because the column orders are different and so you have to use a bulk copy mapping listing column name to column name. 12. Error doing bulk copy because of the decimal type. There are decimal LSN fields and they need to keep their precision of 25. I also switched a -like for -eq by accident causing them to still come out with the wrong precision. 13. Lots of issues getting the exceptions working. It seemed that often if you had multiple catch blocks catching .NET types, it would get confused and start sending exceptions to the wrong ones (and yes I'm absolutely sure of it; it wasn't in any of the inner or base exceptions). So I had to replace it with what I have now and it's very buggy here but the worst case is if the exception throws its own exception and doesn't describe accurately what went on; it will still stop processing properly. 14. DBNull to String error. This was caused by a database that was in Offline, AutoClosed, but did not have Auto_Close enabled. It could only be fixed by taking it back online, disabling auto close (even though it was already off), and setting it back offline again. I believe the issue stems from an incorrect status bit field in sys.databases. 15. PK_UserDefinedMessage failure doing bulk inserts. The clincher was that the return of the error message showed ??? where there should have been a string. It turned out I was accidentally using VarChar in my table columns instead of NVarChar. 16. Silent failures. It turns out Invoke-Parallel swallows exceptions and verbose output! 17. A constraint error when defining constraints. This was caused by jobs with quotations in their names, which are doubled up like ''. This likely comes from the Urn which has the exact same behaviour (because it's escaping it as Xml). I added code to switch '' to ' in the property value extracted from the Urn. -- add a tracking number / start end time thing add more fine grained time tracking, like cumulative per path per server add more error tracking where the write-verbose are, along with descriptions of why; add breakpoints Better improvement list at the top, go through old files too Move the check data type into mainline and make smaller See if you can get it working on only one path, or pass in a sub path only reduce output, increase speed windiff github module Error adding datacolumn; because the property type being checked was wrong (scriptmethod / scriptproperty) 9. It looks like lots of tables aren't being generated anymore, maybe broken by the non-array recurse being changed for the collection. 0. Reduce logging, like on a property collection before the recurse? 1. Seems to include Server/Database/Default for some reason. VERBOSE: Recursing through collection VERBOSE: Working on Server[@Name='.']/Database[@Name='msdb']/Default[@Name='default_current_date' and @Schema='dbo'], the skeleton path is Server/Database/Default VERBOSE: Adding table Default VERBOSE: Calculating primary keys 2a. Exception while enumerating availability group? VERBOSE: Recursing through non-array node Exception on .; The following exception occurred while trying to enumerate the collection: "Operation not supported on version 10.0.6000.". 2b. Exception while enumerating databases on a mirrored instance 4. (Seems fixed) Also in the Ops table there are a bunch of dummy stuff. I need to work out why these aren't being treated as proper collections. select 'Select ''' + schema_name(t.schema_id) + '.' + t.name + ''', * From ' + schema_name(t.schema_id) + '.' + t.name from sys.tables t join sys.columns c on t.object_id = c.object_id where c.name = 'syncroot' order by t.name Select 'smo.ApplicationRoles', * From smo.ApplicationRoles Select 'smo.AvailabilityGroups', * From smo.AvailabilityGroups Select 'smo.Certificates', * From smo.Certificates Select 'smo.Defaults', * From smo.Defaults Select 'smo.FileGroups', * From smo.FileGroups Select 'smo.FullTextCatalogs', * From smo.FullTextCatalogs Select 'smo.FullTextStopLists', * From smo.FullTextStopLists Select 'smo.LogFiles', * From smo.LogFiles Select 'smo.Priorities', * From smo.Priorities Select 'smo.Queues', * From smo.Queues Select 'smo.RemoteServiceBindings', * From smo.RemoteServiceBindings Select 'smo.Roles', * From smo.Roles Select 'smo.Routes', * From smo.Routes Select 'smo.SymmetricKeys', * From smo.SymmetricKeys Select 'smo.Users', * From smo.Users 1. Bulk Insert ManagedComputer and Server schemas 2. When it fails to Insert, retry after attempting to recreate specific columns You can get this working by just disabling the bit that creates foreign key constraints. It doesn't work if the primary key hasn't been created on the lower table first. Otherwise it creates a unique constraint, and then causes an error if you try to create the primary key afterwards. Also foreign key violations are in these: $smods.tables | ForEach-Object { if ($_.geterrors()) { $_.TableName} } NumaNodes Schedulers ResourcePoolNumaNodes 1. Alter the Create Smo DataTable to include the Primary Key 2a. Bulk Insert ManagedComputer and Server schemas 2b. When it fails to Insert, retry after attempting to recreate specific columns 3. Add Foreign Key constraint generation 4. Alter the Create Smo DataTable to include the Foreign Key 5. Need to split between 2012/2014 WMI ---- $instances = @(".") foreach ($serverInstance in $instances) { $started = Get-Date Write-Host "Started $serverInstance at $started" try { $smo = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance) # $wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer($serverInstance) $dsSmo = ConvertFrom-Smo $smo -Verbose try { $dsSmo.EnforceConstraints = $true } catch { $dsSmo.Tables | ForEach-Object { if ($_.GetErrors()) { $_.TableName; $_.GetErrors() }} } $schemaName = "smo" $tables = ConvertFrom-DataSet $opsServerInstance $opsDatabaseName $schemaName $dsSmo -Verbose Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[Database]') Is Not Null Alter Table smo.[Database] Alter Column MirroringFailoverLogSequenceNumber Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[AvailabilityDatabase]') Is Not Null Alter Table smo.[AvailabilityDatabase] Alter Column RecoveryLSN Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[AvailabilityDatabase]') Is Not Null Alter Table smo.[AvailabilityDatabase] Alter Column TruncationLSN Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[DatabaseReplicaState]') Is Not Null Alter Table smo.[DatabaseReplicaState] Alter Column EndOfLogLSN Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[DatabaseReplicaState]') Is Not Null Alter Table smo.[DatabaseReplicaState] Alter Column LastCommitLSN Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[DatabaseReplicaState]') Is Not Null Alter Table smo.[DatabaseReplicaState] Alter Column LastHardenedLSN Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[DatabaseReplicaState]') Is Not Null Alter Table smo.[DatabaseReplicaState] Alter Column LastReceivedLSN Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[DatabaseReplicaState]') Is Not Null Alter Table smo.[DatabaseReplicaState] Alter Column LastRedoneLSN Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[DatabaseReplicaState]') Is Not Null Alter Table smo.[DatabaseReplicaState] Alter Column LastSentLSN Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[DatabaseReplicaState]') Is Not Null Alter Table smo.[DatabaseReplicaState] Alter Column RecoveryLSN Decimal(25, 0) Null" Invoke-SqlCmd -ServerInstance $opsServerInstance -Database $opsDatabaseName -Query "If Object_Id('smo.[DatabaseReplicaState]') Is Not Null Alter Table smo.[DatabaseReplicaState] Alter Column TruncationLSN Decimal(25, 0) Null" $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server=$opsServerInstance;Database=$opsDatabaseName;Trusted_Connection=True") $sqlConnection.Open() $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConnection) $dsSmo.Tables | ForEach-Object Write-Host "Writing $($_.TableName)" $bulkCopy.DestinationTableName = "[$schemaName].[$($_.TableName)]" # Required in case we've added columns, they will not be in order, and as long as you specify the names here it will all work okay $bulkCopy.ColumnMappings.Clear() $_.Columns | ForEach-Object { $bulkCopy.ColumnMappings.Add((New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping($_.ColumnName, $_.ColumnName))) | Out-Null } $bulkCopy.WriteToServer($_) } } catch { Write-Host "Exception on $serverInstance; $_" } $ended = Get-Date Write-Host "Finished $serverInstance at $ended" $performance.Add($serverInstance, $ended - $started) } $performance Setup script: ------------- Use master If Db_Id('Ops') Is Not Null Begin Alter Database Ops Set Single_User With Rollback Immediate Drop Database Ops End Go Create Database Ops Go Use Ops Go Create Schema wmi Go Create Schema smo Go Create Procedure dbo.ClearSchema @SchemaName Sysname As Begin Set Nocount On Declare @Commands Table ( Command Nvarchar(Max) ) Insert @Commands Select 'Alter Table ' + Quotename(@SchemaName) + '.' + Quotename(o.name) + ' Drop Constraint ' + Quotename(f.name) From sys.objects o Join sys.foreign_keys f On o.object_id = f.parent_object_id Where o.schema_id = Schema_Id(@SchemaName) Insert @Commands Select 'Alter Table ' + Quotename(@SchemaName) + '.' + Quotename(o.name) + ' Drop Constraint ' + Quotename(i.name) From sys.objects o Join sys.indexes i On o.object_id = i.object_id Where o.schema_id = Schema_Id(@SchemaName) And i.is_primary_key = 1 Insert @Commands Select 'Drop Table ' + Quotename(@SchemaName) + '.' + Quotename(o.name) From sys.objects o Where o.schema_id = Schema_Id(@SchemaName) Declare @Command Nvarchar(Max) Declare ClearSchema Cursor Static Forward_Only Read_Only Static For Select Command From @Commands Open ClearSchema Fetch Next From ClearSchema Into @Command While @@Fetch_Status = 0 Begin Print @Command Exec sp_executesql @Command Fetch Next From ClearSchema Into @Command End Close ClearSchema Deallocate ClearSchema End Go |