SKLearn – Python

4 Steps:

  1. Import Class:

    or

    from sklearn.linear model import logistic regression

    1. instantiate / estimates

    or

    logreg=logistic regression

    1. Fit model with data [ Model Training ]

    logreg.fit(x,y)

    1. Predict response:

    logreg.predict(x)

    Posted in AI, Python | Leave a comment

    ML Models

    In this series I would be talking mostly about Python and R, we would understand the different ML model supported in Python. Python is a great open source language with multiple provides, SQL Server ML also provides build in, I am not going in detail about python in this series. Python has multiple libraries, for ML it has SKLearn.

    Using Python we can implement ML for Classification and Regression Models. Following are the Algorithms Python supports for ML

    Classification:

    • Decision Tree
    • Bagging Models(Ensebles)
    • Random Forest
    • Voting Model
    • Boosting Model
    • Support Vector Machines [SVM]
    • Naive Bayer Classification [Bayer’s Theorem] – K for KNN
    • K- Means.
    • Unsupervised clustering classification

    Regression:

    • Linear Regression
    • ARIMA [Auto Regression Integrated Moving Average] this is a combination of model [ARIMA, ARMA,AR & MA] Time Series regression

    Classification:

    • Logestic Regression
    • KNN Model [K Nearest Neighbors]
    • Train-Test – Split
    • K – Fold

    Posted in AI | Leave a comment

    Machine Learning – Introduction

    Machine Learning : it is a system where system will learn how the pattern is going and accordingly we can predict the future information.

    There are two types in machine learning:

    1. Classification
    2. Regression

    Classification:

    in this category it would be like – true or false. For any condition system has to predict if the output would be true or false. Standard example of ML on this would be an email would be SPAM or NO-SPAM how do we predict that.their are similar examples for it.

    ML has multiple algorithms for classification.

    Regression:

    this is ML for prediction of the future expected value per the given data upon pattern of the input data and sample data to predict the future data on the given sample. Their are different algorithms depending upon the pattern of data and the combination of input data values.

    • The important about prediction would be the have input data would be only NUMBERS and prediction of that would be numbers. It will not support other then numbers. It is based on mathematical calculation.
    • The calculation part is integrated in the model so it is not necessary to be expert in mathematics, but good to have knowledge of it.
    • ML should have only data, it contains two setup of values – row data (Training data) and sample data (Test Data) to predict
    • Different model fit works for different type of data and works best for some and some do not work well.
    • It could be possible that the model works best today would not work well in future so continuous testing is needed.
    • Prediction never be 100% as it is on algorithm it would be mostly good but it would never be 100% accurate.
    • Their are multiple ways to validate the prediction – rg. Least mean square root criteria – errors.

    Posted in AI | Leave a comment

    Powershell : Desire State Configuration -DSC

    Powershell in 2012, Powershell 4.x onwards introduced Desire State Configuration(DSC) which help to build the standard configuration/ process /template for your system state and can be use or maintain that state for compliance and standardization of our environment. this can be customized as well.

    This will helpful for:

    • Make SQL Server Installation configuration template and flow per our request and can build the SQL template
    • Desire state of the File System
    • Desire configuration of system
    • it will ensure to maintain the desire state on a schedule time and fix if it not.
    • Error Handling is internal – it performs get() set() and Test() functions so if condition is true it will just skip no action taken

    DSC has two process

    • Create the DSC system
    • Setup the DSC Local Configuration Manager( LSM)

    There are two ways of processing DSC:

    • PUSH (Default)
    • PULL

    DSC requires “DSCResouces” module which generally included. the process flow is create the DSC configuration system which will create a MOF file (Managed Object Format )- metadata or configuration file. MOF file is very important for processing the DSC ,according to MOF file the desire state configuration executes and ensure to have Desire space as generated by MOF file. Powershell 5.x onwards this MOF file are default encrypted so avoid compliance and risk of having sensitive data/passwords in plain text.

    Example: to create simple folder/file and ensure it exists all the time for checking every 20min.

    Configuration DirCheck {
    #Node name can be remote/local host
    Node Node1{
    #Resource type “FILE”
    File DirCheck{
    #Desire State check Code
    Type = ‘Directory’
    DestinationPath = ‘C:\DoNotDelete’
    Ensure = “Present”
    }
    File FileCheck{
    #Desire State check Code
    DestinationPath = ‘C:\DoNotDelete\DND_File.txt’
    Ensure = “Present”
    Contents = ‘Vin’
    }
    }
    }

    DirCheck -InstanceName localhost -OutputPath “d:\dsc\DirCheck\”

    Start-DscConfiguration -Wait -verbose -Path D:\dsc\DirCheck\ -computername Node1-Force

    Posted in Automation, Future DBA, Others, Powershell, What I learned today, Whats New | Tagged , | Leave a comment

    POWERSHELL -SQL Assessment

    June/July 2019 Microsoft – powershell is providing another great feature with SMO and SQL Server module as SQL Assessment.

    This is a great feature where Microsoft- SQL Server is providing the assessment for your sql server with general recommendation on the configuration. it would really help and solve most of the problem DBA is having and DBA has to write multiple code and difficult task for them and it is one of the most important activity DBA to perform.

    I am very glad to see it. this will make DBA support at next level. used for SQL Server 2012 onwards and works for both SQL Server Windows and Linux

    eg:

    #To get the Assessment for Named instance ‘.\test1’ :

    PS>Get-SqlInstance -ServerInstance ‘.\test1’ | Invoke-SqlAssessment

     

    #Database level assement for Named instance ‘.\test1’ – testdb

    PS>Get-SqlDatabase -ServerInstance ‘.\test1’ -Database testdb| Invoke-SqlAssessment
     
     

    This will provide all recommendation by Microsoft… Cool.

    We can import this in table and can work on it.

    Get-SqlInstance -ServerInstance ‘.\inst1’ | Invoke-SqlAssessment -FlattenOutput |
    Write-SqlTableData -ServerInstance ‘.\inst1’ -DatabaseName ‘db1’ -SchemaName dbo -TableName table_SqlAssessment -Force

    Get-SqlDatabase -ServerInstance ‘.\inst1’ -Database db1 | Invoke-SqlAssessment -FlattenOutput |
    Write-SqlTableData -ServerInstance ‘.\inst1’ -DatabaseName ‘db1’ -SchemaName dbo -TableName table_SqlAssessment

     

    Posted in Future DBA, Others, Powershell, SQL Server 2019, SQLonLinux, What I learned today, Whats New | Tagged , , , , | Leave a comment

    Next Powershell 6+ (CORE) and 7

    Powershell is no longer been installed or delivered with windows bundle after 5.x and new powershell would be independent of windows/operating system.

    powershell would be a separate system /world and would be available on gethub

    Early this year we got Powershell 6 and now we have powershell 7 Preview been released.

    their is tone of great stuff associated with it.

    you can install it using gethub

    https://github.com/PowerShell/PowerShell/releases/tag/v7.0.0-rc.1

    Also unlike Azure studio would be replacement of SSMS now Powershll ISE would no longer be having enhancement and powershell would having POWERSHELL CORE 6 , POWERSHELL CORE 7 … would be released which would be platform independent…. very interesting…

    So for powershell code ISE we would be having VISUAL STUDIO CODE

    on Visual studio code you have to install powershell extension to use as an ISE way of look.

    good to have next level of POWERSHELL…

     

    happy Learning !!!

     

     

    Posted in Open Source, Others, Powershell, What I learned today, Whats New | Tagged , , , , | Leave a comment

    Cursor Dynamic SQL

    Today was working on SQL Server cursor

    standard cursor deification would be like this:

    sqlcursor from (Azure Data Studio)

    ———————————————————————————————————————–

    — Declare a cursor for a Table or a View ‘TableOrViewName’ in schema ‘dbo’
    DECLARE @ColumnName1 NVARCHAR(50), @ColumnName2 NVARCHAR(50)
    DECLARE db_cursor CURSOR FOR
    SELECT name FROM dbo.TableOrViewName
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @ColumnName1, @ColumnName2
    WHILE @@FETCH_STATUS = 0
    BEGIN
        — add instructions to be executed for every rows
    SELECT @db_nm
        FETCH NEXT FROM db_cursor INTO Sys.Databases
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor
    GO

     

    ———————————————————————————————————————–
    but when you want to run the “SELECT xxx” with  if you want to run a cursor with passing the variable(db_name) and if you pass the db_name run the cursor for that variable else run for all the databases.
    so when you try to declare cursorif (@db_nm is null)
    select @SQL=convert(nvarchar(250),’SELECT name FROM Sys.Databases’)
    else
    Select @SQL=convert(nvarchar(250),’SELECT name FROM Sys.Databases where name =”’+@db_nm+””)
    This will give an error:
    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near ‘@SQL’.
    ———————————————————————————————————————-

    —-This is the Correct way and solution for it. hope this helps.

    DECLARE @db_nm varchar(20)
    DECLARE @SQL nvarchar(1024)

    if (@db_nm is null)
    select @SQL=N’DECLARE allDB_cursor CURSOR FOR SELECT name FROM Sys.Databases’
    else
    Select @SQL=convert(nvarchar(250),’DECLARE allDB_cursor CURSOR FOR SELECT name FROM Sys.Databases where name =”’+@db_nm+””)

    EXEC (@SQL)
    OPEN allDB_cursor
    FETCH NEXT FROM allDB_cursor INTO @db_nm
    WHILE @@FETCH_STATUS = 0
    BEGIN

    print @db_nm

    FETCH NEXT FROM allDB_cursor INTO @db_nm
    END

    CLOSE allDB_cursor
    DEALLOCATE allDB_cursor
    GO

     

    Posted in Others, Troubleshooting, What I learned today | Tagged | Leave a comment

    Transaction log Information gathering

    Here are the different ways to know the tlog file related information

    sys.dm_db_log_stats – Provide summary of tlog information

    SELECT * FROM sys.dm_db_log_stats(db_id())

    sys.dm_db_log_info -DMV function for VLF information same as dbcc loginfo which is non documented by Microsoft.

    SELECT * FROM sys.dm_db_log_info ( db_id())

    sys.master_files – Detail information for all the database – database files (data/log). with related information

    replacement of sysaltfiles(old)

    select * from sys.master_files where database_id=db_id() and file_id=2

    sys.sysfiles – One row for each database file for current database.

    SELECT * FROM sys.sysfiles WHERE fileid=2

    dbcc sqlperf() – old and good dbcc to see the log size and used space for all databases.

    also used to clear the stats

    dbcc sqlperf(logspace)

    sys.dm_db_log_space_usage – only size related information of tlog

    select * from sys.dm_db_log_space_usage

    select log_reuse_wait ,log_reuse_wait_desc from sys.databases where name =DB_NAME()

    this helps me a lot, hth to someone.

     

     

    Posted in Others, Transaction Log | Tagged | Leave a comment

    Azure Data Studeo- Nov 2019 Release

    SQL Server Management studio is now can be installed separately not part of SQL Server package.

    with this enhancement SSMS has several features and version is getting updated and new versions getting release which we can install separately.

    in addition to this SQL Server is available for Azure and with Linux and machine learning enhancement it has to go beyond SSMS to use multiple languages and features which cannot be accommodated in SSMS, SSMS is a great tool for In premise system but for others recently Microsoft has introduced a tool called “Azure Data Studio” it will has almost all the feature SSMS is having with addition to that it can have

    “Notebook” like Jupyter

    can include multiple Languages: Python, R Powershell integrated, also allow to include more as needed..

    and SSMS query intellisense with wonderful chart feature

    Highly recommended for Future SQL Server DBA’s

    **November 2019 version is released last week.

    Reference:

    SQLTiger Team Video

     

     

     

     

     

     

     

     

    Posted in Azure, Future DBA, Others, SQL Server 2017, Tool | Tagged , , , | Leave a comment

    AG without WSFC SQL 2017 and more…

    Starting Sql Server 2017 Microsoft can allow you to enable the configuration manager for “Always ON” feature for standalone system without WSFC and linux without Pacemaker .

    it has a limitation as :

    • It cannot have listener configuration
    • This would only be use as read replica
    • This secondary replica can not be auto failover

     

    This is important as SQL Server has limitation of replica with version

    SQL Server 2017 (9):

    1 Primary +2 Sync Secondary Replica +6Async Secondary Replica

    SQL Server 2019(9):

    1 Primary+4Sync Secondary Replica +4Async Secondary replica

    this is the reason for read only replication there is another concept called “Distributed AG” where 2 AG can be merge and can have more secondary read-only replica.

    Reference

    SQL Tiger Webcast

     

     

     

    Posted in Disaster Recovery, High Avaliability, Others, SQL Server 2017, SQL Server 2019, Webcast, What I learned today, Whats New | Tagged , , , , , | Leave a comment