When did this DB property Change?

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    Comments posted to this topic are about the item When did this DB property Change?

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • OldFashionGang

    SSC Enthusiast

    Points: 121

    Hi, Kimberly. I believe your decision is not to exact. The idea to have a servers list for centralized monitoring is pretty reasonable. But tracking DB-settings changes by results of querying system table/view from time to time is not too good. It is very ease to have "missing states" scenario after some one changes setting forth and back again between requests. Database settings changes logging pretty accurately to ERRORLOG, so there is no reason to investigate such events sideway. If there is need to centralize such data and fire alerts on it, you can parse errorlog files with appropriate sp_s on servers under control.

  • okbangas

    SSChampion

    Points: 11773

    I would definitely not enable xp_cmdshell on all my servers, due to the security risk of it.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Upsert

    SSC Veteran

    Points: 296

    I would suggest setting up a database trigger instead that tracks ddl events:

    create trigger [LOG_EVENTS]

    on database

    for create_procedure, alter_procedure, drop_procedure,

    create_table, alter_table, drop_table,

    create_function, alter_function, drop_function

    as

    set nocount on

    declare @data xml

    set @data = EVENTDATA()

    insert into dbo.CHANGE_LOG(DATABASE_NAME, EVENT_TYPE,

    [OBJECT_NAME], OBJECT_TYPE, SQL_COMMAND, LOGIN_NAME)

    values(

    @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),

    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),

    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),

    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),

    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),

    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')

    )

  • Geoff A

    SSChampion

    Points: 11407

    Hi Kim,

    thanks for sharing. i would have to agree that this info you are after is easily gathered from the SQL Server Logs.

    Also, you can get what you are after PLUS a lot more by reading the logs. below is a script you can run on your central server since you already have all your linked servers setup. i too am a huge fan of a centralized servers where i run things from via linked servers. the script will get all info out of the current SQL Server Logs. you could modify the final select statement with a WHERE clause to see what you want to see. as it stands, it will show you everything. hope all is well with you.

    might take awhile to run if your DBA_Reports.dbo.ServerList is huge and some of your Servers are off-line....

    declare @table table (ServerName varchar(128),LogDate datetime, ProcessInfo varchar(50), [Text] varchar(max))

    declare @server_name nvarchar(250)

    declare db_crsr_DBS cursor for

    select [Server] from [DBA_Reports].[dbo].[ServerList]

    open db_crsr_DBS

    fetch next from db_crsr_DBS into @server_name

    while @@fetch_status = 0

    begin

    declare @retval int;

    begin try

    exec @retval = sys.sp_testlinkedserver @server_name;

    end try

    begin catch

    set @retval = sign(@@error);

    end catch;

    if @retval = 0

    begin

    declare @cmd varchar(500)

    set @cmd = 'exec ['+@server_name+'].master.dbo.xp_readerrorlog 0'

    insert into @table (LogDate, ProcessInfo, Text)

    exec (@cmd)

    update @table

    set ServerName = @server_name

    where ServerName is NULL

    end

    else

    print 'Server not online';

    fetch next from db_crsr_DBS into @server_name

    end

    close db_crsr_DBS

    deallocate db_crsr_DBS

    select * from @table

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    Thanks for the feed back everyone! Yes the logs are also a useful way to collect this information. The intention of this article is for an alternative collection as there are 10 different ways to complete something. I use this method because I have already setup complex reporting and this solution fit nicely into my current solution. 😉

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • DavidSimpson

    Hall of Fame

    Points: 3542

    Good article. As you pointed out there are many ways to pull information and your method is just as valid as any other.

    David

  • TravisDBA

    SSCoach

    Points: 15780

    Good article, and I agree that there are many different ways to arrive at the same result. I am reminded of something my father once said about this concept. "Son, you can cut down a tree with a power saw or a Swiss Army knife. They both will achieve the same result, the difference is the time and energy expended to get there." 😀

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hello,

    Nice article!

    You should really say "no" to xp_cmdshell. It is very bad for security.

    Instead of using xp_cmdshell, why not just create a local database on each server (very small one) to collect information using stored procs and then use your centralized server to create a link to each server and then copy the data back to the centralized server. This link can be created and dropped once the data copy is completed and the data on each server can be overwritten.

    From your centralized server you can then use SSRS to create your reports and database mail to send you information/alerts.

    I've create this process for over 120+ servers and it works well.

    Just my 2 bytes worth,

    Rudy

    Rudy

  • TryingToLearn

    SSCrazy

    Points: 2641

    thanks for posting this, in my environment it would be helpful, Unfortunately i am new to development and SSIS. You have provided in detail all the steps and content except the content of the last two steps for the 'collect database info' loop container, and i'm not sure what to do with the actual DBProperties.dtx you have provided at the end of the post.

    thanks again.

    Jim

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    Hi Jim,

    Here is a word copy of my article and I added the instruction you asked for.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • smthembu

    Hall of Fame

    Points: 3449

    Nyc article but have few things to understand

    U have other connections that refers to "FROM [].[VES_MS].[dbo].[Logs]" with no script to create a table referenced

    Also the following step but no script for the table creation

    SELECT RTRIM(Server) AS servername

    FROM SSIS_ServerList

    WHERE (Skip_SQL_Overview is null or Skip_SQL_Overview = 'FALSE')

    ORDER BY 1

    Thanks

    It's better to fail while trying, rather than fail without trying!!!

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    Jim,

    I'm sorry I just now saw this post. Did you figure it out?

    Kim

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    @smthembu sorry here is the create script

    CREATE TABLE [dbo].[SSIS_Errors](

    [Server] [varchar](128) NOT NULL,

    [TaskName] [varchar](128) NULL,

    [ErrorCode] [int] NULL,

    [ErrorDescription] [varchar](max) NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Also, I have updated the ssis package as it was not the correct one that was uploaded.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    @jim - I have updated the copy of the ssis package as the previous one was uploaded in error. You will need to create a new integration services project then add this xdts package. Change the variables such as server name and database names then move the package to your integration services server and finally call it from a job to run.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply