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. 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
  2. it intern uses self signed certificate for reading the data.
  3. must required .NET FRAMEWORK 4.6
  4. need to create COLUMN MASTER KEY and that has to be registered with COLUMN ENCRYPTION KEY.
  5. presently Microsoft supports only ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’.
  6. encryption type can be DETERMINISTIC can support index,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 …WOW!!!

Hey there…. I write on SQL Server 2016 in my earlier blog Lets See and Whats new, during that time i was aware 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 was 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 scale 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 StrechDB more usage of cloud will be coming to empowerit … more detail 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 of lot things 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

Index Rebuild VS Reorganize

What is the difference between Index rebuild(alter index .. Rebuild) and index reorganize(alter index .. reorganize)- earlier version of SQL Server 2000 or earlier called it as defrag… it’s a basic blog and will not cover online rebuild and column store index.

Index Rebuild index re-organize
leaf level data pages gets re-create so data page number will change it re-arranges data in data pages so page number of leaf level will not change
Its all or Non so when you start it it has to complete or has to rollback, so be careful while it runs as you cannot stop it.and pages will be locked so please do not run it in production hrs. Stop and go, if you stop it it will start next time where it was stopped.
it will take same amount time irrespective of fragmentation at every run Time of execution will vary depends upon the fragmentation that’s the one of the reason Microsoft has suggested the bench mark of 30%.
update statistics with FULL SCAN whenever rebuild runs it will not update statistics. you have to run statistics manually after reorg
Index Rebuild is the not so fully logged(it will minimally logged in bulked-log recovery model) Index re-organize is fully logged irrespective of recovery model, so please make sure that you monitor/manage tlog size carefully when you reorganize the index
Multi threading process so faster its single threading process

This is what I can think of at this time but would like to keep this blog updated.

Could see good blog by saurabh Sinha on net.

http://saurabhsinhainblogs.blogspot.in/2013/05/difference-between-reindexing-and.html

 

 

 

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

SQL Server 2016 – 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 know I am not getting time to write much blog and that’s the reason could not finish whats new in 2014 in detail. But now when 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 studding 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 carnality 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 then 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. StrechDB :to cloud
  3. Temporal table (view historical data)
  4. In Memory enhancements
  5. support of R Language
  6. Always encrepted (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(https://thakurvinay.wordpress.com/category/installation/)

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

log reader uses single CPU

For replication even if you have multi core CPU it uses only one as log reader agent has single threaded operation it will use only one threaded only.

Posted in Replication, What I learned today | 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 cp2 has been released to public and 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 will try to write blog a day for whats new is 2014 as well. I am going through its new features and the more I am reading it it impresses me a lot. and I am feeling that this is it. I really like it.

As you know, I am continue to writing blog a day series for and Whats new series from 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 wellJ).

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 evlauation 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 backup 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 what to generate snapshot now, if you say “yes” it will create a metadata of the articles accotiated with that publisher at snapshot specified location(if its at distributor -would be good). and snapshot agent runs and done. starts appling that snapshot at subsriber which inturns delete the tables and re-create.

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

so what will you do, as we are re-initializing the subsriber so we are safe to delete the subsriber and add the new one which inturns 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