Distributed AG

going though Allan Hirt’s 24 SQLPASS  recording, he has explained the new feature of SQL Server Availability group you can get it here.


he is expert in Clustering /AG/ and published several blogs and books.

Distributed AG is advance and extended to AG its like AG to AG

one AG which will be on WSFC between two instances.


WSFC 1 :Instance A is AG with Instance B and

WSFC 2 :Instance C is AG with Instance D

so we can perform Distributed AG from Instance A to Instance C

then all four instances are in sync with each other. but it has some issues if we make it sync. so its recommendation to make WSFC 1 to WSFC 2 with async so that things will be working efficiently else Instance A will be quite slow to get sync all.

It has its limitations:

important limitation is we can configure it only with T-SQL queries. not with GUI and Powershell(this may overcome in future release)

supported in Enterprise edition only.

Automatic fail over to secondary AG is not supported for now

Thanks Allan 🙂



Posted in High Avaliability, Others, sql 2016 | Tagged , , , , | Leave a comment

Future DBA part 2

I do not say you should be expert in other technologies but you should be aware of what it is and whats the purpose and whats its characteristics and basics.

when we talk of RDBMS (DBMS) like SQL most of the things are common unlike that for NoSQL has different categories.

Its important to understand the different types of NoSQL and its purpose if you want to learn it.

From wiki (https://en.wikipedia.org/wiki/NoSQL)

more on NoSQL



A funny picture.


More coming next

Posted in Future DBA, Others | Tagged | Leave a comment

Future DBA Part 1

As I stated in my earlier blog on future DBA, we should learn many things as we can to be stand strong in the market.. Being DBA from last around 15 Years. and still learning… so cool. So far I blog only on SQL Server but now I am learning other technologies as well and I will be blogging about it. initially i was thinking to have a separate site for it but later realize that this blog has my name and not specific to MS SQL so decided to continue blogging in here. I have been blogging from last 8 Years…(July 2008) canot believe it,  and I love it very much. Thanks all..

I will be blogging more about data now with continue on Microsoft SQL Server(My first love). includes SQL Standard, NoSQL,

Wiki has a very nice comparison about RDBMS is available :


and here is the NOSQL


sky is the limit… so its important to understand that things are huge.





Posted in Future DBA, Others | Tagged | Leave a comment

World is changing so DBA should

I was going through SQLPASS recording and on first time I could see a session as “MongoDB for the SQL Server Professional” which make me think that even community is also realizing that things are changing and DBA has to be more than DBA (should having knowledge of other technologies) and and to learn new things. So do I.

Yes, I am exploring new things, I have to , to stand in the market, because things are not the same. I could see that now a days if you are expert like me SQL DBA. it is not enough you have to know so many other things a well how it works and what all it has and related, and if you learn you may realize that the similar thing is already exists in your and other technology as well.

for example, if you observed Microsoft has invested many $(Dollar)  in SQL Server 2016 and when you explore it all are their in the marking and to stand Microsoft has to be capable of delivering what market demands. like in-memory, support of R, JSON, Polybase, Azure. StretchDB, SSDT, PowerBI…. SQL on Linux, open Powershell and so on… that makes SQL Server stand one of the best in market.

I blog on SQL 2016 here … https://thakurvinay.wordpress.com/category/sql-2016/

BUT….. as stated earlier things are not same. we have to explore more to be stand strong. when you learn other technologies you will realize it.

I would like to make this as a series in category “Future DBA” which includes NOSQL, BIGDATA, Oper source, …. Stay tuned…

Posted in Future DBA, Others | Tagged | Leave a comment

24 Hours of SQLPASS

Like every years before PASS summit, this years also SQLpass has 24 hrs sessions… it has great sessions every time, and this time as well. During this 24 hrs SQLPASS invites 24 expertise to present a audio session for an hr with their related topic(s) for DBA, BI, Developer and Others…

Now this years 24hrs sessions recording is available. its awesome please use it . here.


I am also going through it.this site is having wonderful information. Thanks SQL PASS I learned a lot from you. appreciate. I wish I will attend SQLPASS one day and meet all those expertise in person.

Happy Learning.


Posted in Free Stuff, Others, Training, What I learned today | Tagged | Leave a comment

SQL 2016-Always Encrypted

As you know this has been introduced in sql server 2016, I am not going in details on it but would like to highlights some points which I learned today:


  1. It intern uses self signed certificate for reading the data.
  2. Must required .NET FRAMEWORK 4.6
  3. Need to create COLUMN MASTER KEY and that has to be registered with COLUMN ENCRYPTION KEY.
  4. Presently Microsoft supports only 1 ALGORITHM as ‘AEAD_AES_256_CBC_HMAC_SHA_256’.
  5. Encryption type can be DETERMINISTIC support indexes,RANDOMIZED not supported/good for indexing.

Happy Learning.




limitations and errors related to always encryption:

T-SQL Tuesday #69 : Always Encrypted Limitations




Posted in Limitation, sql 2016, What I learned today, Whats New | Tagged , , , | Leave a comment

SQL Server 2016 …whats new…WOW!!!

Hey there…. I wrote on SQL Server 2016 in my earlier blog Lets See and Whats new, during that time I knew that SQL Server 2016 will be good. but when I explore and see it now looks like it is a REVOLUTION for RDBMS and Microsoft has spent a lot more on this to be the TOP on Database industry.

I have gone though the Deep Dive sessions and it was amazing the vision about SQL Server is quite clear here that it would no longer a small servers, it is now becomes the enterprise system and can be the leader on RDBMS world. It has consider all the expects of market requirement and make sure that SQL Server is fulfilling all the requirement with up to the expectations and more.

Considering this looks like SQL Server 2014 was just a add-on on (in-memory and some other stuff) of the SQL Server 2012 and the real product is SQL Server 2016.

It would be too early to decide but the deep dive sessions are so impressive, if it goes this way then we could see Microsoft will reach to new high.

Microsoft has consider all the aspects of Database on this as you know CLOUD is happening things and things are moving to cloud and Microsoft is already having AZURE. so to extend the Cloud usage StretchDB more usage of cloud will be coming to empower … more details later.

For Always Available with High availability and reliable with Always On.

For Performance improvement to make SQL per industry standard usage of using in-memory and column store and more advance query tuning stuff … more detail later.

SQL Server is achieving awards to be most secure RDBMS from last 6 years…  it become more secure now with always encrypted, row encryption, dynamic data masking and more.

On OLAP it has extended its arms and now could see a different SQL server on this with SSDT and great performance with in-memory and column store and POWER BI provides you an excellent analytic skill where decision and reporting will be fantastic at the same time quick… more to come

Now its out of the box… as you know when you say Microsoft you say the integration with Microsoft OS and restricted to Microsoft product… now with this release Microsoft has explore the world and would like to be up to customer requirement so this year they have done an acquire more than 16 vendor support product/companies which is one of the biggest investment they have made.

As you know now the market is open and data is increasing like never before and industry is looking for granular/micro analysis of the system BIG DATA is the future but we cannot achieve everything with BIG DATA it has its own limitation with respect to OLTP but for large size of data BIG DATA is happening in the market… with HADOOP (I am learning:)). so to accommodate it Microsoft has introduced several new things like POLYBASE, JSON and more to come.

Finally as I said its a customer prospect if industry demands to keep their OS same (LINUX/UNIX) and would like to make SQL Server on their OS Microsoft has announces SQL Server would compatible with LINUX a great and surprising announcement happened last month which shock everyone … but to see the acquisition of R Language and other stuff it could be quite possible. lets see by mid 2017…

*I know this is one of the longest blog I have written and I would like to explore more on this and write lots about SQL Server 2016 as I cannot keep away myself of writing…


Happy learning.


Twitter: MyBest Friend….

You Tube: Deep drive sessions: Becoming another best friend.












Posted in Basic, Others, sql 2016, Whats New | Tagged , | Leave a comment

Index Rebuild VS Reorganize

Posted in Basic, Index, Others, Troubleshooting | Tagged | Leave a comment

SQL Server 2016 – whats new…lets see

Hey friends.

I am here again with what’s new blog … Things are changing quite frequently and requirement is also changing. To fulfill the requirement system has to catch-up to the mark.

As you know it’s a BI world and with lots of lots of data around and we would like to access or speed up the system accordingly and globalization is so high has to integrated with other technology/vendors is also important.

I am learning sql 2016 looks like things are changing dynamically and have so much new coming in with the add-on and enhancement of the existing one.

Initially when I was studying 2014 was which was having much focus on in-memory (Hekaton) which was the major new feature for it, and other features like

Buffer Pool expansion and major change in cardinality estimation, Delay durability, incremental statistics priority waits for online operations.

Now on SQL Server 2016 it another great product with lots of improved and stabilized product I would say and I believe as things has come so quickly and system is let to migrate to new system it would be possible that you could see management would like to adopt SQL 2016 rather than 2014 as it includes all feature of 2014 with more stability (my view).when we think about to move out of SQL 2005(premier support will expire in Aug 2016(hope so this time)).

When talk about SQL Server 2016 it’s another great product of Microsoft team and great achievement by experts. We could see some of the great new features and add-on enhancement of the existing features to make product more powerful.

Following are some of the new features SQL Serve 2016 offers includes:

  1. QUERY store
  2. StretchDB :to cloud
  3. Temporal table (view historical data)
  4. In Memory enhancements
  5. support of R Language
  6. Always encrypted (SSIS)
  7. PolyBase (connection to HADOOP)
  8. Row-level security

I would explore each in coming blogs


Posted in Others, sql 2016, What I learned today, Whats New | Tagged , | Leave a comment

Installation SQL Server 2016

Here is some of the installation processes(https://thakurvinay.wordpress.com/category/installation/)

Below is very nice step-by -step installation  process for SQL Server 2016:


Posted in Installation | Tagged | Leave a comment