9 Reasons Why Experience Analytics and SQL are buddies again in Sitecore 9

Couple of years back Sitecore launched No-SQL platform for analytics by collaboration with MongoDB. Which drastically increased scalability for websites struggling with performance issues using Sitecore Analytics on SQL with heavy traffic.

Sitecore mandated to use Mongo for Analytics hence whoever was upgrading to the latest versions left with NO options other than implementing MongoDB. Which was good for the companies/clients who needed that badly but on the other hand we also had organizations/projects/clients which were either:

  1. Hardcore Microsoft shops OR
  2. Happy with SQL as analytics repository cause not expecting much traffic OR
  3. Hesitate to afford/adopt/maintain entirely new technology, setting up MongoDB, training employee etc.

One of the best quality of a product company is – Listen to your customer. Off course Sitecore is always good at it.

giphy-1

Hence Sitecore 9 comes with an awesome flexibility to choose between SQL or Mongo for Experience Analytics. After all its “Your Digital Journey” hence its “Your Choice” to choose per “Your Needs”.

As Kamruz Jaman says Hello SQL Server (again)

With xConnect now running as a separate module from the CMS this has allowed Sitecore to move xDB to a provider model, and this abstraction will allow different backend databases to be used. The initial release only supports SQL Server 2016. Support for MongoDB and CosmosDB (formerly known as DocumentDB on Azure) will be added some time in the future, but I think for most of us this will simplify things a lot.

We all know the best features to have MongoDB as a collection DB for Experience Analytics. Meanwhile I also got chance to explore what made it promising to go back to SQL for xConnect. Came up with following powerful features of SQL Server 2016:

  1. JSON Support – SQL Server is joining the club with MySQL (5.7 release) and Oracle (MariaDB) supporting JSON. JSON data can be parsed and stored in relational format. Functions are included which allow for JSON to be queried and output results from queries can also be formatted as JSON.11
  2. Stretch Database –The basics of Stretch Database are that some part of your tables (configurable or automated) will be moved into an Azure SQL Database in the cloud in a secure fashion. When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly. The query processing on the Azure rows takes place in Azure so the only latency is for the return of the rows over the network. As an additional enhancement, you are only charged for the SQL Database in Azure when it is used for queries. You do, however, pay for the Azure Blob storage, which is much cheaper than enterprise storage.22
  3. Backup to Azure – on premise backups can be made to Azure blob storage. Also automated managed backup of your on-premise backups to Azure.33
  4. Always Encrypted – With the Always Encrypted feature enabled your SQL Server data will always be encrypted within SQL Server. Access to encrypted data will only be available to the applications calling SQL Server. Always Encrypted enables client application owners to control who gets access to see their applications confidential data. It does this by allowing the client application to be the one that has the encryption key. That encryption key is never passed to SQL Server. By doing this you can keep those nosey Database or Windows Administrators from poking around sensitive client application data In-Flight or At-Rest. This feature will now allow you to sleep at night knowing your confidential data stored in a cloud managed database is always encrypted and out of the eyes of your cloud provider.44
  5. Row Level Security – access to row data can be restricted based on specified SQL logins. This will be done by filter predicates defined inside of inline table value functions. Security policies will ensure that the filters are executed.55
  6. Multiple TempDB Files –With SQL Server 2016 you can now configure the number of tempdb files you need while you are installing SQL Server. Having this new feature means you will no longer need to manually add additional tempdb files after installing SQL Server.
  7. Polybase – allows the querying of Hadoop or Azure blob storage data sets using T-SQL. It’s possible to write queries which join the relational data in SQL Server with the semi-structured data in Hadoop.
  8. Query Store – One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. Without comprehensive testing, this has traditionally been a hard problem to identify and then resolve. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed. The Query Store is configured at the individual database level.
  9. Temporal Tables – helps to facilitate automatic row versioning. Every time a row is updated in the base table, a copy of the older version is made inside the temporal table. The temporal table is physical object inside of the database. It is separate to but linked to the base table.

Hope it helps.

References:

http://dbadiaries.com/sql-server-2016-what-are-the-new-features

https://www.databasejournal.com/features/mssql/slideshows/10-new-features-worth-exploring-in-sql-server-2016.html

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016

2 thoughts on “9 Reasons Why Experience Analytics and SQL are buddies again in Sitecore 9

Add yours

  1. Hi there would you mind stating which blog platform you’re working with? I’m going to start my own blog in the near future but I’m having a hard time choosing between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I’m looking for something completely unique. P.S Apologies for being off-topic but I had to ask!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: