SQL Server 2022

Posted on November 6, 2021 by Vinay

yes SQL Server 2022 is coming and would have many advance features.

https://www.microsoft.com/en-us/sql-server/sql-server-2022

whats new in SQL Server 2022 would be know more here:

https://techcommunity.microsoft.com/t5/microsoft-mechanics-blog/what-s-new-in-sql-server-2022/ba-p/2922227

Bob Ward would be sharing more on data explose about SQL Server 2022

Posted in Others | Tagged , | Leave a comment

Azure Arc

Posted on June 20, 2021 by Vinay

Azure arc is another great stuff coming with Microsoft Azure, as we were expecting Azure cloud Microsoft has great DevOps options and nice for everyone.

Azure has multiple subscribtion option, where you can have Servers (Windows/Linux) and softwares include Database(s), SQL Server. with Azure MS has different features and options MS will provide support like subsribtion where MS will take care of your system/monitoring/support and you only have to use your system/software and focus on application.

So fare all Cloud provider supports the system which we deploy on their Cloud and they would be only responsible for their system.

Wiith Azure Arc Microsoft has extended that support with Hyberid system where now if you have your system -Physical/Virtual on your datacenter/local and that MS can be subsribe and take care of monitoring and other support per subsribeription, that way MS will now able to support your system even if it not on their cloud.

Azure Arc is planning to have SQL Server- in preview stage now, means things are coming where Azure will take care of all support system.

Azure arc also take care of Serverless( Kubernetes)

Azure Arc also support Other cloud system if require(Hybrid) cloud – planning.

This will add on the bussiness to Azure as they would extend their support and technologes like AI/ML and feature which Azure provides.

This is a great stuff for MS but on the other hand I am little afraid if support of the system is managed by Microsoft themself what will happen to support team.

Things are chaning on support system….

https://azure.microsoft.com/en-us/services/azure-arc/

Posted in AI, Automation, Azure, What I learned today, Whats New | Tagged | Leave a comment

SKLearn – Python

Posted on April 28, 2020 by Vinay

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

    Posted on April 27, 2020 by Vinay

    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

    Posted on April 26, 2020 by Vinay

    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

    Posted on January 1, 2020 by Vinay

    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

    Posted on January 1, 2020 by Vinay

    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

    Posted on December 21, 2019 by Vinay

    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

    Posted on November 19, 2019 by Vinay

    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

    Posted on November 14, 2019 by Vinay

    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