Microsoft: Top 8 Features of SQL Server 2016

Fascinating technical materials from Microsoft on the latest SQL Server.

“SQL Server 2016 will deliver breakthrough, mission-critical capabilities with improved performance, security, scale, and operational analytics. The new and improved features in SQL Server 2016 will enable Microsoft IT and the ESBI team to create BI solutions that support advanced analytics directly within databases and help users gain deeper insights into their data. “

Summary:

  • In-Memory OLTP helps ESBI meet their users’ business requirements for increased agility.
  • Columnstore Indexes reduce the amount of time it takes to run and render SRSS reporting data.
  • Temporal data reduces the amount of support tickets received from the field due to inaccurate data.
  • Row-Level Security provides a more reliable and standardized method to easily control which users can access data.
  • Dynamic Data Masking helps limit exposure of sensitive data, preventing users who should not have access to the data from viewing it.
  • Query Store provides better insight into the performance differences caused by changes in query plans.
  • Active Query Statistics allows a view of active query execution plans and helps identify and fix blocking issues while queries are running.
  • SQL Stretch Database helps improve performance to frequently used data while preserving access to archived data.

Read The Full Article

 

SQL Server 2008 Database Mirror Setup Fails

Sometimes setting up Database Mirroring or Replication from one database to another for disaster recovery or fault tolerance etc. can be tricky.  I ran into an issue where the mirror kept failing, even after the full backup [from the Principal] and restore [to the Mirror] of the database and most recent transaction logs completed successfully. I encountered ‘handshake failed’ and encryption algorithm errors.

It was indicated in Event ID’s 1474 and 1433.

Fascinating! I elaborate on a resolution here.

 

Azure SQL Database Naming

I just love this note on creating and accessing an Azure SQL Database! It may take a little getting used to for some, because gone are the days of bland Database names such as, “PRODUCTION_DB__1”.  Lol.

“Notice that you did not specify a server name. Because the SQL Database server must be accessible worldwide, SQL Database configures the appropriate DNS entries when the server is created. The generated name ensures that there are no name collisions with other DNS entries. You cannot change the name of your SQL Database server.”

Azure SQL Database

As I have a few days off from the job (hey, vacation time can add up quickly!), what else am I to do, but to set up an Azure SQL Database, of course!

I host this site in Azure already, mainly for fun, although I do earn a few dollars from ads here and there. But really I am using Azure as a learning platform and my next adventure is within the SQL world. I do support the SQL environment at work, being the solo Systems Administrator: server infrastructure for our SQL needs, backups, resource monitoring, data mirroring (replication), troubleshooting and problem solving, working with our primary application vendor, light queries etc.

I admit, I am more fascinated than ever with Microsoft SQL, now called simply ‘Azure SQL Database’. As it is fully cloud based nowadays, it is super exciting that a SQL Database can be created within a matter of minutes! Obviously, I am not referring to the actual table, table views, stored procedures – these take time to develop. However, I am referring to the platform – organizations no longer need to get 3 quotes from hardware vendors (CDW, Dell, HP etc.), nor project into the future re: needs, nor purchase, then rack, and then obtain proper warranties on  … local server room or Datacenter HARDWARE.

With an Azure SQL Database, using modern IaaS (infrastructure as a Service), along with PaaS (Platform as a Service), or maybe even SaaS (Software as a Service) for good measure, some of the steps of the past can be handled more efficiently. An organization’s I.T. Department or Developer or external Consultant simply has to log into the Azure account, and click the ‘Add (+) New’ to add a Database.

Also the need to look into Disaster Recovery or failover takes on a whole new meaning once in the Cloud – but that is a topic for the future, not here!

Note, I already added a Database, simply named it ‘prod’ …

SqlAz