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:

YOU WILL NEVER GET/READ THE REAL DATA FROM “SQL SERVER MANAGEMENT STUDIO”. IT WILL ALWAYS BEEN ENCRYPTED. THOSE COLUMNS HAS BE READ/WRITE USING APPLICATION CODE ONLY(having higher privilege)

  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.

 

ref:

https://msdn.microsoft.com/en-us/library/mt147923.aspx

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.

Reference:

Twitter: MyBest Friend….

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

https://www.microsoft.com/en-in/server-cloud/products/sql-server-2016/

 

 

 

 

 

 

 

 

 

 

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:

http://www.databasejournal.com/features/mssql/installing-sql-server-2016.html

Posted in Installation | Tagged | Leave a comment

Whats new in SQL Server 2016

While learning SQL Server 2014. looks like we need to swing on it as SQL Server 2016 is coming with great new advance features and stable SQL Server(just like after 2005 to SQL Server 2008).

To learn more about SQL Server 2016 whats new Microsoft has launch a new site call docs.com which will have quite a great videos and information.

https://docs.com

which will contains all the videos about whats new in SQL Server 2016.

http://sqlblog.com/blogs/sergio_govoni/archive/2015/07/28/sql-server-2016-video-pills.aspx

just like Microsoft videos channel 9.

Happy Learning.

reference:

http://sqlblog.com/blogs/sergio_govoni/archive/2015/07/28/sql-server-2016-video-pills.aspx

http://www.sqlservercentral.com/articles/SQL+Server+2016/127518/

 

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

hey its time for 2016!!!

Hi all

Yeah its true microsoft announced SQL server 2016 and CP2 has been released to public also we can read books online for it.

http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/default.aspx

First blog from mobile …..happy leaning…

 

 

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

Whats New 2014

Hey all,

Continue to my whats New series,  I am going through its new features and the more I am reading it impresses me a lot. and I am feeling that this is it. I really like it.

Other what’s new series are SQL Server 7, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. Now its time for SQL Server 2014(I know 2016 is coming on its way). You can get all my Whats New blog @ What’s New Tag(hope this will goes well).

Following are some of the features which I may go much dipper on but I think I need some time to do so.

Initially When I heart about 2014 I though it has on in-Memory as a new feature later realize that it has much more(will try to add more during my learning):

sql server 2014:”
1 >> Memory-optimized tables (formerly known as Hekaton) :are available in Enterprise editions, as well as evaluation and developer editions. In-Memory OLTP is a high performance, memory-optimized engine
http://msdn.microsoft.com/en-us/library/dn673538.aspx
http://blogs.msdn.com/b/saponsqlserver/archive/2014/02/09/new-functionality-in-sql-server-2014-part5-in-memory-oltp.aspx
2 >>Memory-Optimized Table checkpoint:The automatic checkpoint for memory-optimized tables runs after the log increases 512MB after the previous checkpoint.

3 >>Buffer Pool Extension :Enterprise/ Business Intelligence/ Standard(64 bit)
Buffer Pool Extension Edition:The Buffer Pool Extension feature is supported in Enterprise, standard, Evaluation , and Developer editions only.
The Buffer Pool Extension (BPE) feature of SQL Server 2014 allows SSDs to be used as additional buffer pool memory.
4>> Database Backup Encryption :
Starting in SQL Server 2014, SQL Server has the ability to encrypt the data while creating a backup.

http://msdn.microsoft.com/en-us/library/dn756429.aspx

5 >> Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator  (trace flag 9481 )setting trace flag 9481
The first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0.  The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process,

XML showplan text
http://msdn.microsoft.com/en-us/library/dn673537.aspx

Select TOP (tie/percent) gives worse performance :resolves using new Cardinality Estimator
http://sqlblog.com/blogs/hugo_kornelis/archive/2014/11/06/how-top-wrecks-performance-part-1.aspx

6 >>SQL Server Backup to URL

SQL Server Backup to URL was introduced in SQL Server 2012 SP1 CU2 supported only by Transact-SQL, PowerShell and SMO. In SQL Server 2014 you can use SQL Server Management Studio to back up to or restore from Windows Azure Blob storage service

7>> The 90 compatibility level is not valid in SQL Server 2014(120)

8>>We can define an Availability Group replica that resides in Azure.

9 >> Updateable columnstore indexes: This means you no longer have to drop and re-create columnstore indexes every time you need to load your warehouse tables

10>> Resource Governor for I/O : set the minimum and maximum reads or writes per second allowed by a process in a disk volume.

11 >>Delayed durability : Delayed durability allows you to return control back to the application before the log is hardened
Under delayed durability, log blocks are only flushed to disk when they reach their maximum size of 60KB.
the facility exists to make the delayed durability choice per transaction too
http://www.sqlskills.com/blogs/paul/delayed-durability-sql-server-2014/

12 >>Incremental statistics: Incremental statistics in SQL Server 2014 allow you to update just those rows that have changed and merge them with what’s already there.

13 >>  Low Priority Wait /Low priority of online operations :you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over. Will you have it follow traditional behavior and wait indefinitely? Will you have it terminate and move to the next table? Or will you kill the blocking query, so your re-indexing can complete? It’s your choice.

14>>Parallel insert functionality of SELECT INTO :

http://msdn.microsoft.com/en-us/library/cc645993.aspx#High_availability
http://www.infoworld.com/article/2606990/database/147960-10-best-new-features-in-SQL-Server-2014.html
http://blogs.msdn.com/b/saponsqlserver/
http://blogs.msdn.com/b/saponsqlserver/archive/2014/01/16/new-functionality-in-sql-server-2014-part-2-new-cardinality-estimation.aspx

Posted in Sql server 2014, Whats New | Tagged , | Leave a comment

Re-initialize replication not working

For someone re-initialization is very simple, just go to publisher and right click and select re-initiallize and it will ask for- do you want to generate snapshot now, if you say “yes” it will create a metadata of the articles associated with that publisher at snapshot specified location(if its at distributor -would be good). and snapshot agent runs and done. starts applying that snapshot at subscriber which in turns delete the tables and re-create.

but what if you run it several time but nothing happens and you don’t know whats going on, as it don’t provide you the error any where and also it will not show where is it going and what it is doing. for you it’s just like nothing happening.

so what will you do, as we are re-initializing the subscriber so we are safe to delete the subscriber and add the new one which in turns does the same of initialization. so I did so, guess what…. it started working and surprisingly I get error at the replication stating

cannot drop the table ‘xxx’ as its it is being referenced by object ‘yyy’

so now what to do, if you see the object ‘yyy’ its a view with schema binding option enable, due to which table cannot be drop.

this could be also because of function is using that articles(objects). so if  you see re-initialize is not working first check this dependent /reference objects with respect to replicated articles.

This may save some time for some one…

 

 

Posted in Add/remove, High Avaliability, Replication, Troubleshooting | 1 Comment

Sql Cluster Resource not comming online

when you check on the sql server services and sql agent services it’s getting started on windows cluster but on cluster its going down again. on error log you may get the cluster service

Login failed for user ‘cluster service account’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

first thing you might think would be check the account password and see if its working– yeah its working now what next.

Read the error log carefully and try to understand what is happening. here you could see sql server is started but when it tried to attach – make available to cluster with cluster service account it’s getting failed. means the given service account might not have privilege to sql server.

So what we should do, we should make cluster/sql fool and do a work around as failed over the cluster and as soon as sql server started, we open the sql server and checked is the given service account has access to it, and we were right, that account were not existed on to sql server, we have added that account and granted sysadmin role and you know what…. it WORKS !!!

The possibility of this issue could be:

“The cluster service account has been changed and that has not been updated at sql level”

 

Posted in Add/remove, Bug, Disaster Recovery, Error Log, High Avaliability | Tagged , , , , | Leave a comment