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