1. OG_SQL-DB

    Article: AN0001779Updated: 03.12.2018

    This function returns a list of databases with given properties from SQL servers according to configuration.

    Main prerequisites for use

    Prerequisite Description
    Powershell remoting When communicating with external systems using execution of Powershell commands by an external system it is necessary to enable remoting on the external system.
    Master DB_datareader In order to read data about databases from SQL server it is necessary to assign access rigts for reading this data to the account performing the query.
    Práva na službu WinRM When communicating with external systems using execution of Powershell commands by an external system it is necessary to assign access rights for remoting to the account sending the command.
    Powershell v3 Microsoft Powershell version 3 or higher.

    Description

    Function creates a text file containing information about databases according to the configuration. The file is in UTF-8 encoding and uses tab as a separator.

     

    Header name Description
    SQLServer Name of the server which hosts SQL server.
    Domain Name of the domain of this server.
    InstanceName Name of the SQL server instance.
    IP IP address of the server.
    DBName Database name.
    Recovery model Recovery model of the database.
    Collation Database collation.
    Last Database Backup (yyyy-mm-dd hh:mm:ss) Date and time of the last database back up.
    Last Database Log Backup (yyyy-mm-dd hh:mm:ss) Date and time of the last database log back up. (Databases in  a simple recovery model do not have database log backed up.)
    Size (GB) Net size of the database in GB (decimal).
    Space Available (MB) Size of the free space in the database in GB (decimal).
    Compatibility level Database compatibility level.

    Parameters

    Include hereinafter stated parameters in the file og_configuration.ps1 (generic parameters shared with other functions) or in the file OG_PC-Start.config (specific parameters of this function). Parameter configuration is described in the documentation of the programme Piklo.

     

    Parameter Description Example
    [string]$WebServer Address of the web service containing class with list of SQL servers, from which information about databases shall be read. [string]$WebServer = "https://yourserver.yourdomain.com/OGService.asmx?WSDL"
    [string]$filterModelCode Cde of the model containing class with list of SQL servers, from which information about databases shall be read.

    [string]$filterModelCode = "mon-and-man"

    [string]$filterParentCode Code of the class containing list of SQL servers, from which information about databases shall be read.

    [string]$filterParentCode = "server-sqldb"

    [int]$AttrKey_Server Id of the column with names of servers.

    [int]$AttrKey_Server = 6155

    [int]$AttrKey_Domain Id of the column with name of server domain.

    [int]$AttrKey_Domain = 6156

    [int]$AttrKey_Instance Id of the column with instance name.

    [int]$AttrKey_Instance = 6157

    [int]$AttrKey_Aktivni Id of the column determining, wheather information about databases shall be read from the respective server. It can be used to temporarily exclude some server from the data collection.

    [int]$AttrKey_Aktivni = 6158

    [int]$AttrKey_MSSqlServerServiceName Id of the column with the name of service of SQL server.

    [int]$AttrKey_MSSqlServerServiceName = 6161

    [string]$ExportFile Path to the file, in which output of the function shall be stored.

    [string]$ExportFile = "\\yourserver\data\OG-output\SQL-DB.txt"

    [string]$LogFile Path to the file, in which log of the function shall be stored.

    [string]$LogFile = "\\yourserver\data\OG-output\SQL-DB_Log.txt"

    Call

    The function is called by means of the programme Piklo, that is part of ObjectGears package, from ObjectGears script. Result of the call has to be assigned to a variable, with which the script can further work.

    a = OG.Process.RunPiklo('OG_SQL-DB','-type C')

×