tag:blogger.com,1999:blog-16192663742239643542024-03-28T20:29:45.829-07:00SQL and SQL onlyNagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.comBlogger161125tag:blogger.com,1999:blog-1619266374223964354.post-23316200375783767092018-03-30T07:26:00.000-07:002018-03-30T07:38:58.446-07:00Azure SQL Database – Automatic Index Tuning<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="line-height: 115%; margin: 0px;"><span style="color: black; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Introduction: -</b></span></span></div>
<span style="font-size: large;"></span><b></b><span style="color: black;"></span><br />
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">We
live in an era of “Automation”. Planes run on auto pilot mode, cars will
soon be auto driven, so why not have auto indexed databases? This article will
discuss the automatic index tuning feature available in Azure SQL Database.</span></span></div>
<br />
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="line-height: 115%; margin: 0px;"><span style="color: black; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Problem Statement:</b></span></span></div>
<span style="font-size: large;"></span><b></b><span style="color: black;"></span><br />
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Application
development is moving at a rapid pace and its very common for application teams
to develop a template database design and deliver it to multiple
customers. It is even more common in cloud where solutions are delivered
as “software as a service” and it is way too challenging for application
development team to analyze the workload of individual database, design
specific indexes for them and tune them.</span></span></div>
<br />
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="line-height: 115%; margin: 0px;"><span style="color: black; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Automatic tuning in Azure SQL Database</b></span></span></div>
<span style="font-size: large;"></span><b></b><span style="color: black;"></span><br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Azure
SQL Database, the cloud Platform as a service (PAAS) offering of SQL Server has
attempted to address the above problem by introducing “Automatic tuning”. As of
date, “automatic tuning” has the following features</span></span></div>
<br />
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="color: #2a2a2a; margin: 0px;">· <strong><span style="margin: 0px;">Automatic plan correction</span></strong>
– Plan regressions or sudden change in query plan resulting in performance
degradation are tracked and last good plan available is forced. Automatic plan
correction is already well explained over </span><a href="http://www.sqlservercentral.com/articles/Automatic+Plan+Correction/164002/"><span style="color: #0066cc; margin: 0px;">here</span></a></span></span><span style="color: #2a2a2a; margin: 0px;"></span></div>
<br />
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">· <strong><span style="margin: 0px;">Automatic Index Tuning:</span></strong>
Automatic index tuning feature of Azure SQL Database will study the workload
executed against the database, identify useful indexes and create them
automatically. Automatic index tuning will also drop indexes that are unused
over 93 days or any duplicate indexes present.</span></span></div>
<br />
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="line-height: 115%; margin: 0px;"><span style="color: black; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>How to enable automatic index tuning</b></span></span></div>
<span style="font-size: large;"></span><b></b><span style="color: black;"></span><br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Once
logged in to Azure Portal (portal.azure.com) one can enable the same by
navigating to SQL Databases-> Click on the database -> Automatic tuning</span></span></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
Force plan -> Enables Automatic Plan Correction</span></span></div>
<div style="text-align: left;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> ·
Create / Drop Index -> enables automatic index tuning</span></span></div>
<br />
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Features
can be enabled at server level and can be inherited at the database level as
well.</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia"; font-size: large;"><br /></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjUbfFe6KG-NVSIBz5D6kuUWAa7QZ5Se5c1bzhWoY7MBBJhc6MSNSpNKRZszZBcHnrDyNnjQOwq9yIo3e-yAcMGvFLqVxhg89N03G5PNMO56NH7Knak2J26taVrNpHvX6napeUXKP0R8A/s1600/enable_automatic_tuning.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="407" data-original-width="989" height="260" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjUbfFe6KG-NVSIBz5D6kuUWAa7QZ5Se5c1bzhWoY7MBBJhc6MSNSpNKRZszZBcHnrDyNnjQOwq9yIo3e-yAcMGvFLqVxhg89N03G5PNMO56NH7Knak2J26taVrNpHvX6napeUXKP0R8A/s640/enable_automatic_tuning.jpg" width="640" /></a></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">TSQL
Script for enabling automatic index tuning provided below</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; font-family: "arial" , "sans-serif"; margin: 0px;"><span style="color: #0b5394; font-size: large;">ALTER
DATABASE</span><span style="font-size: large;"> </span><span style="color: magenta; font-size: large;">DBName</span></span></div>
<span style="font-family: "arial" , "sans-serif"; margin: 0px;"><span style="color: #0b5394; font-size: large;">SET
AUTOMATIC_TUNING (CREATE_INDEX = ON, </span></span><br />
<span style="font-family: "arial" , "sans-serif"; margin: 0px;"><span style="color: #0b5394; font-size: large;"> DROP_INDEX = ON)</span></span><br />
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<br /></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="color: #003366; line-height: 115%; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Example:</b></span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="font-size: large;"></span><b></b><br /></div>
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">To
test how Auto Indexing works, following things were performed</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
A sample database with 5 tables with no indexes was created</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
10 to 15 select queries against the tables were executed repeatedly few
thousand times over 3 to 4 hours</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
After one day, automatic indexing found the recommendations and rolled them out
when there was less or no workload on the database. Auto Indexing
requires minimum one day of monitoring to identify recommendations</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="color: #2a2a2a; margin: 0px;"> </span><span style="color: #2a2a2a; margin: 0px;">This
can be verified on Azure Portal by clicking on the “Database name” ->
“Performance Recommendation”</span></span></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="color: #2a2a2a; margin: 0px;"><br /></span></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4A8DfZwd5NIUfz-H29hP8OxgEjTVeKIe4k3bQnQ2bF9rx2HBq2jIXggUJ4403V5-YybFgE2HfMPxvh91eSDd0Skin2UcYI7lJkutPA6W_4SEGdS3_KPVBTInuj8k6u3-xxrhagQwo9sw/s1600/automatic_tuning_recommendations.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="323" data-original-width="1071" height="192" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4A8DfZwd5NIUfz-H29hP8OxgEjTVeKIe4k3bQnQ2bF9rx2HBq2jIXggUJ4403V5-YybFgE2HfMPxvh91eSDd0Skin2UcYI7lJkutPA6W_4SEGdS3_KPVBTInuj8k6u3-xxrhagQwo9sw/s640/automatic_tuning_recommendations.jpg" width="640" /></a></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="color: #2a2a2a; margin: 0px;"><br /></span></span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span>
</div>
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="color: #2a2a2a; margin: 0px;">The
same can also be verified via the DMV sys.dm_db_tuning_recommendations,
documented clearly over </span><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-tuning-recommendations-transact-sql"><span style="color: #0066cc; margin: 0px;">here</span></a></span></span><span style="color: #2a2a2a; margin: 0px;"></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="line-height: 115%; margin: 0px;"><span style="color: black; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Key pointers of Automatic Index Tuning</b></span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
Indexing recommendations are obtained using advanced artificial intelligence
rules applied on the monitored workload. The recommendations are expected to be
lot more accurate than earlier missing index recommendations from SSMS</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
The best part of the feature is it is non-blocking during the rollout of new
indexes. </span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
Index changes wouldn’t be rolled out and will be postponed, if DTU usage was
above 80% anytime in the last 20 minutes</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
Automatically created indexes don’t enforce any dependency on the columns. When
the column with automatically created index is dropped, index is dropped as
well</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
Auto created indexes can be tracked via sys.indexes – auto_created column</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="color: #0b0510; font-family: "georgia"; font-size: large;"><b>Learn – Adapt - Verify</b></span></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">One
of the key features of Automatic Index tuning is the ability to monitor the
impact of the changes done via automatic tuning. Azure SQL Database closely
monitors the automatically created or dropped indexes and verifies if it has
achieved the desired performance improvement. If it doesn’t then the change is
automatically reverted to prevent any negative impact to the database. The
reverted changes can be tracked from sys.dm_db_tuning_recommendations DMV
as well.</span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3GUUPsnVAQaRIjPxG-LB8zBC0XsxmfG1mGpJSOuClXUFuhIa8IK8hiVQDO2URA4qqYIISbce8L96KBUXxAfeFZlFxy8gR0GZ6cAq8Pn1Zjl_ALXGf-TseINMSEo2mElNAjcf_dwYwm5I/s1600/Learn_verify_Adapt.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="321" data-original-width="578" height="354" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3GUUPsnVAQaRIjPxG-LB8zBC0XsxmfG1mGpJSOuClXUFuhIa8IK8hiVQDO2URA4qqYIISbce8L96KBUXxAfeFZlFxy8gR0GZ6cAq8Pn1Zjl_ALXGf-TseINMSEo2mElNAjcf_dwYwm5I/s640/Learn_verify_Adapt.jpg" width="640" /></a></div>
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia"; font-size: large;"></span></span><br />
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
</div>
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia"; font-size: large;">
</span><span style="font-family: "georgia";"></span></span>
<br />
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="margin: 0px;"><span style="font-family: "georgia";">
<span style="line-height: 115%; margin: 0px;"><span style="color: black; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Suggested enhancements</b></span></span></span></span></div>
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia";">
</span></span>
<br />
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia";"><span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><span style="font-size: large;"></span><b></b><span style="color: black;"></span><br /></span></span></div>
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia";">
</span></span>
<br />
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia";"><span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
Automatic indexing is available only as an Azure SQL Database feature and is
not available on SQL Server on premise. If would be wonderful if the feature
could be extended to on premise SQL Server.</span></span></span></span></div>
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia";">
</span></span>
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia";"><span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span></span></span></div>
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia";">
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<span style="color: #2a2a2a; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">·
Automatic Index tuning should be extended to fixing fragmentation issues of the
indexes as well.</span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
</div>
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="line-height: 115%; margin: 0px;"><span style="color: black; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Conclusion</b></span></span></div>
<div style="margin: 19.2px 0px 3.86px; text-align: left;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="color: #2a2a2a; margin: 0px;"> </span><span style="color: #2a2a2a; margin: 0px;">Automatic
Index Tuning does simplify one of the major tasks of a database administrator
and a developer. Automatic Index Tuning may not be 100% perfect to get the
choices correct all the time but it is certainly a step in the right direction.
Expect Microsoft to release more of such features in the days to come not just
in indexing but also all areas of database maintenance</span></span></span></div>
<div style="margin-bottom: 5.75pt; margin-left: 0in; margin-right: 0in; margin-top: 5.75pt;">
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span></div>
</span></span><br />
<div style="margin-bottom: 5.75pt; margin-left: .5in; margin-right: 0in; margin-top: 5.75pt;">
<b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-size: large;"></span></div>
<b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-size: large;"></span><span style="color: #0b5394;"></span><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com44tag:blogger.com,1999:blog-1619266374223964354.post-54984920920186922002018-03-18T05:13:00.002-07:002018-03-18T05:13:56.344-07:00Webinar on Adaptive Query Processing for Data Platform Geeks<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">My session on Adaptive Query Processing has been finding significant interest in Data Platform events :) Delivered 2 webinars for Data Platform Geeks community, one of them at American time and other with Indian time. Indian time webinar gave me massive attendance with 100 enthusiasts joining in. Recording of one of the webinars shared below for people who couldn't join in.</span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;"><br /></span>
<br />
<iframe allow="autoplay; encrypted-media" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/2bFoYHQMeI4" width="560"></iframe><br />
<br />
<br />
<span style="font-size: large;">Data Platform Geeks have quite a few webinars going on. Do check out their website over <a href="http://www.dataplatformgeeks.com/events/">here</a> and join them. </span></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com13tag:blogger.com,1999:blog-1619266374223964354.post-19482662975174903702018-02-09T15:25:00.002-08:002018-02-09T15:25:26.274-08:00Data Channel Interview - 10 - Dinesh Priyankara on Azure Data Lake Analytics <div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Here we go for the 10th Interview of Data Channel. In this interview, Dinesh Priyankara, a 12-time MVP from Sri Lanka, demystifies Azure Data Lake and ADL Analytics. This interview is an excellent starting point if you looking to learn Azure Data Lake Analytics. Personally, Imo, one of my best performances as an interviewer asking the right questions. Happy Learning!!!</span><br />
<div>
<br /></div>
</div>
<br />
<iframe allow="autoplay; encrypted-media" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/9ccf8Cb3kFk" width="560"></iframe><br />
<br /></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com3tag:blogger.com,1999:blog-1619266374223964354.post-48464973404656613632018-01-21T07:48:00.002-08:002018-01-21T07:48:12.094-08:00Configuration Manager Missing - Windows 10<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-size: large;">SQL Server Configuration Manager is one of the fundamental tools for a DBA to do basic tasks like starting or stopping SQL Service, knowing how many instances and what components of SQL Server are been installed on the machine.</span><br />
<span style="font-size: large;"></span><br />
<span style="font-size: large;">Imagine this, suddenly you find your configuration manager missing. Pretty annoying and can be one of those things which can give that 15 minutes of extreme irritating moments. Not sure, why it happens ( Windows update perhaps ? ) but have seen this in couple of machines now. You can find it at "<span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><b>C:\Windows\SysWOW64\" </b>directory</span></span><br />
<b><span style="font-size: large;"></span><br /></b>
<span style="font-size: large;">For SQL Server 2017 it is : <b><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">C:\Windows\SysWOW64\</span>SQLServerManager14.msc </b></span><br />
<span style="font-size: large;"><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">For SQL Server 2016/2014 it is : <span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline; float: none; font-family: Times New Roman; font-size: 24px; font-style: normal; font-variant: normal; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">C:\Windows\SysWOW64<b>\</b></span></span><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">S</span><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">QLServerManager13.msc / <span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"></span><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">SQLServerManager12.msc</span></span></span><br />
<span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="font-size: large;"><br /></span></span></span>
<span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="font-size: large;">To add it back to the start menu, copy it from the original location provided above and paste it to</span></span></span><br />
<span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="font-size: large;"><br /></span></span></span>
<span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="font-size: large;">C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2017\Configuration Tools\</span></span></span><br />
<span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="font-size: large;"><br /></span></span></span>
<span style="font-size: large;"><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">This would put back the configuration manager under start menu. </span></span><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><span style="-webkit-text-stroke-width: 0px; background-color: transparent; color: black; display: inline !important; float: none; font-family: Times New Roman; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">Hope it helps!!!</span></span></span><br />
<span style="font-size: large;"></span><br /></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com5tag:blogger.com,1999:blog-1619266374223964354.post-25844624458120618652017-12-31T08:29:00.000-08:002017-12-31T08:30:37.047-08:00Azure SQL Database - Backup<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Most of you must be knowing that backup in Azure SQL Database fully automatic. After the database is created and the database is automatically backed up by Azure. One can restore to any point in time for a period of 7 days for the basic subscription and 35 days for standard and premium.</span><br />
<span style="font-family: "georgia"; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Most of the above are already known to you. But, what can be little surprising for someone learning Azure SQL Database is, one can never access backup files used for recovering the database. Meaning, you can only restore the database as another azure sql database but you can never download the automatic backup files like you would do when you configure backups on your on-premise database.</span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>So, what do you do when you need the backup file of Azure SQL Database for reference?</b></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b></b></span><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />Create a bacpac file backup (similar to your full back up on - premise database ) of Azure SQL Database</span><br />
<span style="font-family: "georgia"; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">1) Connect to the Azure SQL Database via SSMS<br />2) Right click tasks-> Export -Data-Tier Application. Follow the screenshots below to manually take a backup of Azure SQL Database</span><br />
<div>
<span style="font-family: "georgia"; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi22Z-gwzkGOIo5QJcQolCXkYRZ2Q45te74p5qTCeo6PHxNz8-wXZBiQkJq53-P72pXk-V0i6a2UyH5ZXuC_YoUOh0ujvawd1iZX0wx0vJymI5H-GS1aLfYwy71Eh91EBeEx3uI0bxFU24/s1600/export_as_bacpac.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="554" data-original-width="694" height="508" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi22Z-gwzkGOIo5QJcQolCXkYRZ2Q45te74p5qTCeo6PHxNz8-wXZBiQkJq53-P72pXk-V0i6a2UyH5ZXuC_YoUOh0ujvawd1iZX0wx0vJymI5H-GS1aLfYwy71Eh91EBeEx3uI0bxFU24/s640/export_as_bacpac.jpg" width="640" /></a></div>
<div>
<span style="font-family: "georgia"; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyfmnCV1zpL1FkIOSNfIIHqLv1lruAG8LbUipa_BdBmlDNHHd5EBWfsYzd8KZhrA51GDXX0Aa1tQcepZapYZwJrPJl-bSNxO2x1tUo2zsWAX3cM_YCAD11kjggPBe19thUULmm5dUU85o/s1600/bacpac_bkup.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="749" data-original-width="823" height="582" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyfmnCV1zpL1FkIOSNfIIHqLv1lruAG8LbUipa_BdBmlDNHHd5EBWfsYzd8KZhrA51GDXX0Aa1tQcepZapYZwJrPJl-bSNxO2x1tUo2zsWAX3cM_YCAD11kjggPBe19thUULmm5dUU85o/s640/bacpac_bkup.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2n4NN2gLLXbbtSI89EZYr7SMXOPOwFIqMnGR2huaXFNRXfhCxwuEMQjYOQtLtXDRTW5vUn-J8y2JCA94T1yT_hKxsMBBzQV9Gk1mSPAmbOxyJCSC1WkkAqd7xcpjvpB3KsH_MxPo1Cp4/s1600/bacpac_completion.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="749" data-original-width="823" height="582" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2n4NN2gLLXbbtSI89EZYr7SMXOPOwFIqMnGR2huaXFNRXfhCxwuEMQjYOQtLtXDRTW5vUn-J8y2JCA94T1yT_hKxsMBBzQV9Gk1mSPAmbOxyJCSC1WkkAqd7xcpjvpB3KsH_MxPo1Cp4/s640/bacpac_completion.jpg" width="640" /></a></div>
<br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">So, if you need an older backup of the azure database (yesterday 8:00 am backup), then recover the older version of the database using the Azure portal to a different azure sql database and extract the bacpac file using the steps explained above.</span></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com20tag:blogger.com,1999:blog-1619266374223964354.post-3058102108281895552017-12-23T05:06:00.000-08:002017-12-23T05:06:59.894-08:00My Interview at Data Platform Geeks 2017<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Dear All,</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span> <span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Earlier this year in August, I had the privilege of speaking at Data Platform Summit 2017 at Bangalore. As always a terrific experience to hangout, interact and learn from the best in the business. Here is my interview to DPS Team about my experience at the summit</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span> <br />
<div>
<br /></div>
<br />
<iframe allow="encrypted-media" allowfullscreen="" frameborder="0" gesture="media" height="315" src="https://www.youtube.com/embed/UZwMmza0TiE" width="560"></iframe><br />
<br /></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com2tag:blogger.com,1999:blog-1619266374223964354.post-83536848105652529202017-12-23T03:16:00.000-08:002017-12-23T03:17:09.078-08:00Adaptive Query Processing - Adaptive Joins - Plan resue - 3<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> <br />
</span><br />
<div style="margin: 0px 0px 10.66px;">
<span style="font-family: Georgia, "Times New Roman", serif;"><span style="font-size: medium;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: small;"><span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Continuing on the series on Adaptive Query Processing covered over</span></span></span></span><span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: medium;"><span style="font-family: "calibri"; font-size: x-small;"> </span></span><a href="http://strictlysql.blogspot.sg/search/label/Adaptive%20Query%20Processing">here</a>, this post would cover what happens to adaptive joins when plans are reused. </span></span></div>
</div>
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"></span><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
</span> <span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">To give a <span style="margin: 0px;">little bit</span> of context to the post, the earlier posts in the series explained how SQL Server 2017's Adaptive joins made it possible for SQL Server to switch between nested loop join and hash join in query plan during runtime depending upon the number of rows returned by one of the joining <span style="margin: 0px;">tables</span>. "Adaptive join threshold" is a row count threshold used to decide whether to go for Nested loop join or hash join. </span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">So, the question is what happens when the <span style="margin: 0px;">query plan</span> is reused? Does the Query plan dynamically switch between the <span style="margin: 0px;">nested loop</span> and hash join when the query plan is reused or does it just go with the last choice? What is the <span style="margin: 0px;">role</span> of "Adaptive join threshold" when the <span style="margin: 0px;">plan</span> is reused? To check it out, <span style="margin: 0px;">let's</span> test it </span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<br />
<div style="line-height: normal; margin: 0px;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: blue; margin: 0px;">DBCC</span><span style="background: white; color: black; margin: 0px;"> FREEPROCCACHE</span></span></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="background: white; color: blue; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">GO</span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: blue; margin: 0px;">EXECUTE</span><span style="background: white; color: black; margin: 0px;"> </span><span style="background: white; color: maroon; margin: 0px;">sp_executesql</span><span style="background: white; color: blue; margin: 0px;"> </span></span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: blue; margin: 0px;"><span style="margin: 0px;"> </span></span><span style="background: white; color: red; margin: 0px;">N'SELECT<span style="margin: 0px;"> </span>[fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]</span></span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="background: white; color: red; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">FROM<span style="margin: 0px;"> </span>[Fact].[Order] AS [fo]</span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="background: white; color: red; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">INNER<span style="margin: 0px;"> </span>JOIN [Dimension].[Stock Item] AS [si]</span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="background: white; color: red; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="margin: 0px;"> </span>ON [fo].[Stock Item Key] = [si].[Stock Item Key]</span></span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: red; margin: 0px;">WHERE<span style="margin: 0px;"> </span>[fo].[Quantity] = @quantity'</span><span style="background: white; color: grey; margin: 0px;">,</span><span style="background: white; color: black; margin: 0px;"> </span><span style="background: white; color: red; margin: 0px;">N'@quantity int'</span><span style="background: white; color: grey; margin: 0px;">,</span><span style="background: white; color: black; margin: 0px;">@quantity </span><span style="background: white; color: grey; margin: 0px;">=</span><span style="background: white; color: black; margin: 0px;"> 360</span></span></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<br /></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: blue; margin: 0px;">EXECUTE</span><span style="background: white; color: black; margin: 0px;"> </span><span style="background: white; color: maroon; margin: 0px;">sp_executesql</span><span style="background: white; color: blue; margin: 0px;"> </span></span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: blue; margin: 0px;"><span style="margin: 0px;"> </span></span><span style="background: white; color: red; margin: 0px;">N'SELECT<span style="margin: 0px;"> </span>[fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]</span></span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="background: white; color: red; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">FROM<span style="margin: 0px;"> </span>[Fact].[Order] AS [fo]</span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="background: white; color: red; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">INNER<span style="margin: 0px;"> </span>JOIN [Dimension].[Stock Item] AS [si]</span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="line-height: normal; margin: 0px;">
<span style="background: white; color: red; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="margin: 0px;"> </span>ON [fo].[Stock Item Key] = [si].[Stock Item Key]</span></span></span><span style="background: white; color: black; margin: 0px;"></span></div>
<br />
<div style="margin: 0px 0px 10.66px;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: red; line-height: 107%; margin: 0px;">WHERE<span style="margin: 0px;"> </span>[fo].[Quantity] = @quantity'</span><span style="background: white; color: grey; line-height: 107%; margin: 0px;">,</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> </span><span style="background: white; color: red; line-height: 107%; margin: 0px;">N'@quantity int'</span><span style="background: white; color: grey; line-height: 107%; margin: 0px;">,</span><span style="background: white; color: black; line-height: 107%; margin: 0px;">@quantity </span><span style="background: white; color: grey; line-height: 107%; margin: 0px;">=</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> 130</span></span></span></div>
<span style="font-family: "georgia";"> <b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-family: "times new roman";"></span><span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span></span><span style="font-family: "georgia";"> <span style="font-size: large;"><br />
</span><br />
</span><br />
<div style="margin: 0px 0px 10.66px;">
<span style="font-family: "georgia";"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 1:</b> Clearing the cache</span></span></div>
<span style="font-family: "georgia";"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 2:</b> Run the query using sp_executesql and pass the variable, so that plan is reused</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 3:</b> Run the same query with a <span style="margin: 0px;">different parameter value</span>, so that the plan is reused with a different value.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span> <span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Don't forget to turn on the Show query plan option to see the query plan.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Query plans for both the queries provided below</span></span><span style="font-family: "georgia";"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span> <span style="font-size: large;"></span></span><br />
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia";"><span style="font-size: large;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVDqXsh4n4zwc2qTmc8ZiYdvvMHJmU37b9D3phVOA315VPR1pPTNqyZ7WdVxUoU7N9KaNGMuHMGVYZO5LBZLnjEcrFOTE0eRVAH4OwOite1IQofkXjX4rLMCPm2VZKEo6vEIf-tLPCGA0/s1600/both_plans.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="673" data-original-width="773" height="556" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVDqXsh4n4zwc2qTmc8ZiYdvvMHJmU37b9D3phVOA315VPR1pPTNqyZ7WdVxUoU7N9KaNGMuHMGVYZO5LBZLnjEcrFOTE0eRVAH4OwOite1IQofkXjX4rLMCPm2VZKEo6vEIf-tLPCGA0/s640/both_plans.jpg" width="640" /></a></span></span></div>
<span style="font-family: "georgia";"><span style="font-size: large;"><br />
<br />
</span></span><br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<span style="font-family: "georgia";">
<span style="font-size: large;"><br />
</span><br />
<div style="margin: 0px 0px 10.66px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Both use Adaptive Joins which is a good news.</span></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Now, <span style="margin: 0px;">let's</span> compare the properties of Adaptive Join operator to understand more</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span> <div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWL6TLiRn6vgC9oyUEFkPwUSk2EK52NeFvhOB9T7HZVTlVUhbc7EgPvpptearWXpBcDpSaI5ff7u6X-SbKEEzb31G0MwhsaFhAoLdB7mI3HIDmkfZkRQhonsG2SUorejoIyRIV1SFGpT4/s1600/plan_reuse.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="439" data-original-width="597" height="468" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWL6TLiRn6vgC9oyUEFkPwUSk2EK52NeFvhOB9T7HZVTlVUhbc7EgPvpptearWXpBcDpSaI5ff7u6X-SbKEEzb31G0MwhsaFhAoLdB7mI3HIDmkfZkRQhonsG2SUorejoIyRIV1SFGpT4/s640/plan_reuse.jpg" width="640" /></a></div>
<div>
<span style="font-size: large;"><br />
</span></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span><br />
<div style="margin: 0px 0px 10.66px;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><b>First observation:</b> Actual join type for both executions are different. So, Adaptive Join switches the join operator dynamically even when the plan is reused</span></span></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> <b>Second Observation:</b> Adaptive threshold of rows remains the same and this implies it is fixed at the compile time or when the plan was first generated. Hence "Estimated Join Type" also remains the same.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span><br />
<div style="margin: 0px 0px 10.66px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> So, to summarize, Adaptive Join threshold is computed at the compile time. So, when the plans are reused, the same adaptive join threshold is used for all <span style="margin: 0px;">executions</span>. However, Adaptive join still switches the join operator at each execution, using the adaptive join threshold calculated at the first time the plan was generated.</span></div>
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span></span></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com22tag:blogger.com,1999:blog-1619266374223964354.post-9666207550290850662017-12-02T07:49:00.000-08:002017-12-02T07:50:03.101-08:00Data Channel Interview - 09 - Mr.Madhivanan on Best Practices in TSQL Programming <div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Dear All,</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Very happy to share the 9th interview of #DataChannel. Mr.Madhivanan Ramachandran, 10 time Microsoft MVP Award winner and a Data Platform expert from India discusses "Best Practices for TSQL Programming". Mr.Madhivanan demystifies quite a few concepts with his to the point answers in this interview. Happy Learning!!!</span><br />
<span style="font-family: "georgia"; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/EUnH_0vJLu0" width="560"></iframe><br />
<br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Regards,</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Nagaraj</span> </div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com3tag:blogger.com,1999:blog-1619266374223964354.post-81478572621108538632017-11-29T06:39:00.000-08:002017-11-29T06:39:41.215-08:00Adaptive Query Processing and Automatic Tuning - Adaptive Joins - 2<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Continuing from the introduction written over <a href="http://strictlysql.blogspot.sg/2017/11/adaptive-query-processing-and-automatic.html">here</a>, let’s look at one of the aspects of Adaptive Query Processing which is Adaptive Joins.</span><br />
<span style="font-family: Georgia, "Times New Roman", serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Before we start Adaptive Joins, SQL Server's Query optimizer internally has 3 join types.</span><br />
<span style="font-family: Georgia, "Times New Roman", serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: Georgia, "Times New Roman", serif;"><span style="font-size: large;"><b>Nested Loop Join</b> - Usually picked by optimizer, when tables participating in the joins are small or when one of the table is big and indexed on joining column</span></span><br />
<span style="font-family: Georgia, "Times New Roman", serif;"><span style="font-size: large;"><b>Hash Join:</b> One of the table is big but doesn't have an index </span></span><br />
<span style="font-family: Georgia, "Times New Roman", serif;"><span style="font-size: large;"><b>Merge Join:</b> Both the tables are big, and the joining column is indexed</span></span><br />
<span style="font-family: Georgia, "Times New Roman", serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">So, the size of the table or the rows participating in the join plays a major role SQL's Query optimizer picking the correct join type. Let’s say, SQL Server estimates few rows participating in the join at compile time and picks "nested join" but during runtime, the join extracts larger number of rows and thereby making the choice of plan ineffective. </span><br />
<span style="font-family: Georgia, "Times New Roman", serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">SQL Server 2017's adaptive join addresses this problem. With Adaptive Join, SQL Server comes with plan attribute called "Adaptive Row Threshold" which is a row count threshold estimated by SQL Server. "Adaptive Row Threshold" will help SQL Server dynamically alter the plan choice at run time. Adaptive join in short will work in the following way</span><br />
<span style="font-family: Georgia, "Times New Roman", serif;"></span><span style="font-size: large;"></span><br />
<ul style="text-align: left;">
<li><div style="text-align: left;">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">If the number of rows participating in the join are greater than "Adaptive Row Threshold" rows, then "Hash Join" operator is used for join operation</span></div>
</li>
<li><div style="text-align: left;">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">If the number of rows participating in the join are lesser than "Adaptive Row Threshold" rows, then "Nested loop Join" operator is used for join operation</span></div>
</li>
</ul>
<div style="text-align: left;">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Consider the following query:</span></div>
<div style="text-align: left;">
<span style="font-family: Georgia, "Times New Roman", serif;"></span><span style="font-size: large;"></span><br /></div>
<div style="text-align: left;">
</div>
<div style="line-height: normal; margin: 0px; text-align: left;">
<span style="font-size: large;"><span style="font-family: Georgia, "Times New Roman", serif;"><span style="background: white; color: blue; margin: 0px;">SELECT</span><span style="background: white; color: black; margin: 0px;"><span style="margin: 0px;"> </span>[fo]</span><span style="background: white; color: grey; margin: 0px;">.</span><span style="background: white; color: black; margin: 0px;">[Order Key]</span><span style="background: white; color: grey; margin: 0px;">,</span><span style="background: white; color: black; margin: 0px;"> [si]</span><span style="background: white; color: grey; margin: 0px;">.</span><span style="background: white; color: black; margin: 0px;">[Lead Time Days]</span><span style="background: white; color: grey; margin: 0px;">,</span><span style="background: white; color: black; margin: 0px;"> [fo]</span><span style="background: white; color: grey; margin: 0px;">.</span><span style="background: white; color: black; margin: 0px;">[Quantity]</span></span></span></div>
<div style="text-align: left;">
</div>
<div style="line-height: normal; margin: 0px; text-align: left;">
<span style="font-size: large;"><span style="font-family: Georgia, "Times New Roman", serif;"><span style="background: white; color: blue; margin: 0px;">FROM</span><span style="background: white; color: black; margin: 0px;"><span style="margin: 0px;"> </span>[Fact]</span><span style="background: white; color: grey; margin: 0px;">.</span><span style="background: white; color: black; margin: 0px;">[Order] </span><span style="background: white; color: blue; margin: 0px;">AS</span><span style="background: white; color: black; margin: 0px;"> [fo]</span></span></span></div>
<div style="text-align: left;">
</div>
<div style="line-height: normal; margin: 0px; text-align: left;">
<span style="font-size: large;"><span style="font-family: Georgia, "Times New Roman", serif;"><span style="background: white; color: grey; margin: 0px;">INNER</span><span style="background: white; color: black; margin: 0px;"> </span><span style="background: white; color: grey; margin: 0px;">JOIN</span><span style="background: white; color: black; margin: 0px;"> [Dimension]</span><span style="background: white; color: grey; margin: 0px;">.</span><span style="background: white; color: black; margin: 0px;">[Stock Item] </span><span style="background: white; color: blue; margin: 0px;">AS</span><span style="background: white; color: black; margin: 0px;"> [si]</span></span></span></div>
<div style="text-align: left;">
</div>
<div style="line-height: normal; margin: 0px; text-align: left;">
<span style="font-size: large;"><span style="font-family: Georgia, "Times New Roman", serif;"><span style="background: white; color: black; margin: 0px;"><span style="margin: 0px;">
</span></span><span style="background: white; color: blue; margin: 0px;">ON</span><span style="background: white; color: black; margin: 0px;"> [fo]</span><span style="background: white; color: grey; margin: 0px;">.</span><span style="background: white; color: black; margin: 0px;">[Stock Item Key] </span><span style="background: white; color: grey; margin: 0px;">=</span><span style="background: white; color: black; margin: 0px;"> [si]</span><span style="background: white; color: grey; margin: 0px;">.</span><span style="background: white; color: black; margin: 0px;">[Stock Item Key]</span></span></span></div>
<div style="text-align: left;">
</div>
<div style="margin: 0px 0px 11px; text-align: left;">
<span style="font-size: large;"><span style="font-family: Georgia, "Times New Roman", serif;"><span style="background: white; color: blue; line-height: 107%; margin: 0px;">WHERE</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"><span style="margin: 0px;"> </span>[fo]</span><span style="background: white; color: grey; line-height: 107%; margin: 0px;">.</span><span style="background: white; color: black; line-height: 107%; margin: 0px;">[Quantity] </span><span style="background: white; color: grey; line-height: 107%; margin: 0px;">=</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> 360</span><span style="background: white; color: grey; line-height: 107%; margin: 0px;">;</span></span></span></div>
<div style="margin: 0px 0px 11px; text-align: left;">
<span style="background: white; color: grey; line-height: 107%; margin: 0px;"><span style="font-size: large;"></span><span style="font-family: Georgia, "Times New Roman", serif;"></span><br /></span></div>
<div style="margin: 0px 0px 11px; text-align: left;">
<span style="background: white; color: grey; line-height: 107%; margin: 0px;"><span style="color: black; font-family: Georgia, "Times New Roman", serif; font-size: large;">Observe the picture below:</span></span></div>
<div style="margin: 0px 0px 11px; text-align: left;">
<span style="font-size: large;"></span><span style="font-family: Georgia, "Times New Roman", serif;"></span><br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZQKkQdL99_foJjm0i2001uXDE6ja17gqx77pclJU0qM2swBHcQJ_A_GFOCWS28Mt7VVoeKB5RMmRGz3knkuzvJ6vER5Sbi-98TUSN5ZJVJiTwZd4rTtXv80YGAG-HWdUjJjCtv1e-5Ko/s1600/Adaptive_Join.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="879" data-original-width="1600" height="350" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZQKkQdL99_foJjm0i2001uXDE6ja17gqx77pclJU0qM2swBHcQJ_A_GFOCWS28Mt7VVoeKB5RMmRGz3knkuzvJ6vER5Sbi-98TUSN5ZJVJiTwZd4rTtXv80YGAG-HWdUjJjCtv1e-5Ko/s640/Adaptive_Join.jpg" width="640" /></a></div>
<div style="margin: 0px 0px 11px; text-align: left;">
</div>
<div style="margin: 0px 0px 11px;">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Query Optimizer fixes 68 rows as Adaptive join threshold. </span></div>
<div style="margin: 0px 0px 11px; text-align: left;">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Estimated number of rows for the outer table in the join was
213. As 213 is greater than 68, estimated join type is Hash Match Join. During runtime, 206 rows from outer table participated in
the join and as it was greater than "Adaptive join threshold" of 68,
Hash match join was selected.</span></div>
<div style="margin: 0px 0px 11px; text-align: left;">
<span style="line-height: 107%; margin: 0px;"><span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Sharing
another example where Adaptive Join switches the join type in runtime. Refer to
pic below</span></span></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div style="text-align: left;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi092flC4gImLHOSJFSISxl-FtAqFfcdYcslFesWhV11ATQc7CPqUoo-AiQ9jgoU5BJyhyphenhyphenxG8nq08HL5C3WYvsrR1gn6WsCrn1g0yaZmZwxs5mN24s6LQi1LBruFaJ_lCQvyBojEYlTBWk/s1600/adaptive_join_chg_join_type.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="697" data-original-width="1600" height="278" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi092flC4gImLHOSJFSISxl-FtAqFfcdYcslFesWhV11ATQc7CPqUoo-AiQ9jgoU5BJyhyphenhyphenxG8nq08HL5C3WYvsrR1gn6WsCrn1g0yaZmZwxs5mN24s6LQi1LBruFaJ_lCQvyBojEYlTBWk/s640/adaptive_join_chg_join_type.jpg" width="640" /></a></div>
<div style="margin: 0px 0px 11px;">
<br /></div>
<div style="margin: 0px 0px 11px;">
</div>
<div style="margin: 0px 0px 11px;">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">In this case, threshold was 46 rows and estimate were 87
rows. However, runtime row count was just 36 rows hence switching the join type
to Nested loop join from an estimate of "Hash Join"</span></div>
<div style="margin: 0px 0px 11px;">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;"> The ability to alter execution plan operator in runtime would
make SQL Server's query optimizer weed out most of the incorrect plan choices
during runtime.</span></div>
<div style="margin: 0px 0px 11px;">
<span style="font-size: large;"></span><span style="font-family: Georgia, "Times New Roman", serif;"></span></div>
<b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-family: Calibri;"></span><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-size: large;"></span><span style="font-family: Georgia, "Times New Roman", serif;"></span></div>
</div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-63057907934742976372017-11-25T08:26:00.001-08:002017-11-25T08:28:31.427-08:00Tricks - Copy T SQL Result to Excel<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Sometimes, some of the silliest of tricks save so much of time and effort. Very common scenario is to copy a query result to excel. Most take either of the 2 approaches</span><br />
<span style="font-family: Georgia; font-size: large;"><i><b><br /></b></i></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><i>* Copy the result from result grid of management studio and paste to Excel as shown below</i></span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><b></b><i></i><b></b><br />
<div class="separator" style="clear: both; text-align: center;">
<span style="clear: left; float: left; font-family: "georgia" , "times new roman" , serif; font-size: large; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="753" data-original-width="1543" height="312" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUGprIY1BLZ-BwaggfznHY1if1g7zlQQQe4fB1re1FyBgF8kL-y1IgvOxYV-2LGDzh4SX70zLgrZvk0uduXx5mtMCvX-KLOenolPuAcPeJ7JIEg-l7usziZFj2fR2MVsCfKiy0ZkyeLTM/s640/copy_to_excel.jpg" width="640" /></span></div>
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">The problem with above approach is sometimes, when one pastes the result to excel, the formatting can go haywire, especially if the result contains carriage return (new line) and commas as shown below.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<span style="clear: left; float: left; font-family: "georgia" , "times new roman" , serif; font-size: large; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="588" data-original-width="1403" height="267" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_JoRuPQpyxNV3_RFFXIAI0Wu__bRyMM78BSTwiPEAHwh78Gy1Q6xe91aVmXfu2KzgYiorYyvFSuFeiR-Ra22x_FG80DG-zHcARPksVLReEJS9mZ6gqQ2iPlkymZ1QOE9vz5UgRvEcSrs/s640/formatting_gone_wrong.jpg" width="640" /></span></div>
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><i>* Export to excel via export wizard</i></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b></b><b></b><i></i><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Little tedious and sometimes getting the mapping right can be a pain.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Over the years, the simplest and effective technique, I have used is running T SQL query directly from excel (Yes, you can do that :)). Not just that, it gives a cleanly formatted result all the time!!! Step by step instructions provided below</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<b><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">How to run TSQL Query from Excel:</span></b><br />
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Open Excel and move to data tab and click on the icon "Other Data Sources" and select "SQL Server" as shown below.</span></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="clear: left; float: left; font-family: "georgia" , "times new roman" , serif; font-size: large; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="381" data-original-width="826" height="294" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2ODhZh6SINGZpuMejI4AB94ESXjpf0JjCRoGWhH8eCxlHvkhOf0Kg0l619atnyBnpPf34mcKjk1hJjPghrxDdP-Lssq4qBIkibU596XhofVk3gPGCN76aoszBVI_kGII451NSMdb5-1o/s640/excel_sql_connection.jpg" width="640" /></span></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Enter the server to connect to and the credentials</span></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjspDNOCJUeZiWn7q1NCu_jWvxHs3mStLepfYx9eFwAcWLxzkon1hptgTmWII9tfeu8GVNa55a2D0nTTvaF2iq5iv17dDPe3uYLKBveJRB4OmUMHwCyLHOvX4kuG06RjTdHSjAQ3VksxuI/s1600/connection_details.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><img border="0" data-original-height="358" data-original-width="513" height="278" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjspDNOCJUeZiWn7q1NCu_jWvxHs3mStLepfYx9eFwAcWLxzkon1hptgTmWII9tfeu8GVNa55a2D0nTTvaF2iq5iv17dDPe3uYLKBveJRB4OmUMHwCyLHOvX4kuG06RjTdHSjAQ3VksxuI/s400/connection_details.jpg" width="400" /></span></a></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Select the database against which you want to run the query. Pick a random table (doesn't matter if you are not going to query it. Any table is fine)</span></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTinjAIwDyeU6M_nHxYAcP_PCcn6IB6tXzeRzGdxNXDTU4ytGIgOuz6DZe-goRkp_HtMcgWliLV95gsb6cJvbwIEDQk0EaIFGoeoCHQca_0D7bkqIRflG6K7eUhc7q3Zxhp-Gn-_NXo5c/s1600/db_n_table.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><img border="0" data-original-height="367" data-original-width="513" height="285" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTinjAIwDyeU6M_nHxYAcP_PCcn6IB6tXzeRzGdxNXDTU4ytGIgOuz6DZe-goRkp_HtMcgWliLV95gsb6cJvbwIEDQk0EaIFGoeoCHQca_0D7bkqIRflG6K7eUhc7q3Zxhp-Gn-_NXo5c/s400/db_n_table.jpg" width="400" /></span></a></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Click next and finish (we are not done yet :))</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgI_Oxam-kFdmuPYdtPOaVpMvBTSpwgPOXUTJFIDDCW_boSN59iHRTPyfvCtx7Wr5JMdfp-zNk8SRUypeNzn-_f3nyOfns4L9-6W1WbZdg6uW5R_WhVHGQMTJILI1Fn9l1dbmd4XLTj728/s1600/next_n_finish.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><img border="0" data-original-height="430" data-original-width="513" height="335" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgI_Oxam-kFdmuPYdtPOaVpMvBTSpwgPOXUTJFIDDCW_boSN59iHRTPyfvCtx7Wr5JMdfp-zNk8SRUypeNzn-_f3nyOfns4L9-6W1WbZdg6uW5R_WhVHGQMTJILI1Fn9l1dbmd4XLTj728/s400/next_n_finish.jpg" width="400" /></span></a></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Click on properties</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuNel-6PEzjBtDqU6ooiYsFm_k0hrUVRz5L-4jnEqOqDZ-dk4fAkcpyD1HIzjPohLNJRXALvOk70U2ZuojvsEH5yfEULPWX1quVz2B3yBv8dhWjuWrHS-rKeNdNE56_LR55g63FmUIm1k/s1600/properties.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><img border="0" data-original-height="240" data-original-width="287" height="334" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuNel-6PEzjBtDqU6ooiYsFm_k0hrUVRz5L-4jnEqOqDZ-dk4fAkcpyD1HIzjPohLNJRXALvOk70U2ZuojvsEH5yfEULPWX1quVz2B3yBv8dhWjuWrHS-rKeNdNE56_LR55g63FmUIm1k/s400/properties.jpg" width="400" /></span></a></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Uncheck background refresh check box</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcz-x5e7EPH07Hi3pMv9H_k6KS9UNsQ6tfpQOGiuUNTNIcTeneubP7frSG9Hrg4siw7AcF18djzzF_8fMIg8-V9IKLRV5sl1GQ22R5rUDva3PBEhfCTWe7tFt8O0JMNDmk9sfrqHx8-Ro/s1600/uncheck_backgrund_refresh.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><img border="0" data-original-height="500" data-original-width="405" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcz-x5e7EPH07Hi3pMv9H_k6KS9UNsQ6tfpQOGiuUNTNIcTeneubP7frSG9Hrg4siw7AcF18djzzF_8fMIg8-V9IKLRV5sl1GQ22R5rUDva3PBEhfCTWe7tFt8O0JMNDmk9sfrqHx8-Ro/s400/uncheck_backgrund_refresh.jpg" width="323" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Click on the "Definition" tab and change the command type to "SQL" from "Table"</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipwZypwZicc51cyE_AoTfuVk0obzhCMC3QmfEKeJ8Mg-Zp029nq45CXAcb41EUKwidxh-kytFoBt5-qljupksYn_VlEnd6Pi-YbrNfDpZJf7tsgHgx5-XeuTvckSgBJT5i06PW31hvS_A/s1600/definition_tab.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><img border="0" data-original-height="500" data-original-width="405" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipwZypwZicc51cyE_AoTfuVk0obzhCMC3QmfEKeJ8Mg-Zp029nq45CXAcb41EUKwidxh-kytFoBt5-qljupksYn_VlEnd6Pi-YbrNfDpZJf7tsgHgx5-XeuTvckSgBJT5i06PW31hvS_A/s400/definition_tab.jpg" width="323" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Paste the query you want to run on the "Command Text" box as shown below</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXKBOOn0NyNFSce9VCwZk_J6hx4ZNMovURDE9HUc4973vIkkv5c_r8WtgBHX47uBlltK6AzSZ5eJTNPwuVcLDIGFvlLo6IiVvjNN_d2_8ShTx-X6bcntj60pvMpUzA6m6rChjjoOYvazo/s1600/Query.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><img border="0" data-original-height="500" data-original-width="405" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXKBOOn0NyNFSce9VCwZk_J6hx4ZNMovURDE9HUc4973vIkkv5c_r8WtgBHX47uBlltK6AzSZ5eJTNPwuVcLDIGFvlLo6IiVvjNN_d2_8ShTx-X6bcntj60pvMpUzA6m6rChjjoOYvazo/s320/Query.jpg" width="259" /></span></a></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Result would be a nice formatted excel sheet as shown below</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<span style="clear: left; float: left; font-family: "georgia" , "times new roman" , serif; font-size: large; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="457" data-original-width="1172" height="248" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVmGZnJPb7_FhVQIMYaP8oG238x_NUL2iQF-NCr95Uj8cRmDpNpexiZLXBfrHwMBmFhjt5UnoYxcSxcw5pgHNCvmRZh0HQ1QOtKBjZr9GPtoOfNzrU0g80D-4Bc0WaW8_UZGkQ34tHvUU/s640/formatted_table.jpg" width="640" /></span></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">From excel once can not only get select query results but stored procedures as well. Excel also servers as an excellent tool to query database when one needs to access the database on a machine that doesn't have SQL Server Management Studio. Hope the tip was useful!!!</span></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-3953252270619128892017-11-12T23:27:00.000-08:002017-11-29T00:36:20.303-08:00Adaptive Query Processing and Automatic Tuning - Introduction - 1<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">SQL Server 2017 and Azure SQL Database are being touted as
the self learning, artificial intelligent database. One of the main reasons for
the same is due to release of the following features</span></div>
<br />
<div style="margin: 0px 0px 11px;">
<b><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> 1) Adaptive Query Processing</span></b></div>
<br />
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>2) Automatic Tuning</b></span></div>
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Adaptive Query Processing is SQL Server's first attempt using artificial intelligence to fix poor performing queries due to incorrect cardinality
estimates. Query optimizer produces better plans using "Adaptive Query
Processing" which is enabled by default when one is on SQL 2017's compatibility
mode (CE 140). Queries can benefit via Adaptive Query Processing in the
following scenarios.</span></div>
<br />
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">1) <b>Adaptive Join</b> -> Query optimizer makes a intelligent
choice between Nested Loop and Hash Join in runtime</span></div>
<br />
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">2) <b>Memory Grant Feedback</b> -> Query optimizer adjusts the
memory granted to a query depending upon the previous run's execution results</span></div>
<br />
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">3) <b>Interleaved Execution</b> -> Portion of the query is
executed while generating the query plan to come with the estimates. Applies
currently for Multi Statement Table Valued Functions alone</span></div>
<br />
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> The above scenarios and operators are likely to expand in
upcoming releases or patches</span></div>
<br />
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> Other aspect of auto driven query tuning is "Automatic
Tuning" feature. Automatic Query tuning helps in the following scenarios</span></div>
<div style="margin: 0px 0px 11px;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;"> <b>Automatic Tuning:</b></span></span></div>
<br />
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">1) <b>Force Last Good Plan:</b> Attempts to fix query regression by
detecting any sudden change in query performance due to plan change and forcing
the last good performing plan. one can turn it on using the command below</span></div>
<div style="margin: 0px 0px 11px;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;"><span style="background: white; color: blue; line-height: 107%; margin: 0px;">ALTER</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> </span><span style="background: white; color: blue; line-height: 107%; margin: 0px;">DATABASE</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> DBName </span><span style="background: white; color: blue; line-height: 107%; margin: 0px;">SET</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> </span><span style="background: white; color: blue; line-height: 107%; margin: 0px;">AUTOMATIC_TUNING </span><span style="background: white; color: grey; line-height: 107%; margin: 0px;">(</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> </span><span style="background: white; color: blue; line-height: 107%; margin: 0px;">FORCE_LAST_GOOD_PLAN</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> </span><span style="background: white; color: grey; line-height: 107%; margin: 0px;">=</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> </span><span style="background: white; color: blue; line-height: 107%; margin: 0px;">ON</span><span style="background: white; color: black; line-height: 107%; margin: 0px;"> </span><span style="background: white; color: grey; line-height: 107%; margin: 0px;">);</span></span></span></div>
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">2) <b>Automatic Index creation and deletion: </b>Feature available only in Azure SQL Database. By studying the workload, the database engine automatically deletes and creates indexes!!!</span></div>
<div style="margin: 0px 0px 11px;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">This is just a introduction post. As usual,will be writing a series of posts to cover the individual sections in detail.</span></div>
<b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><b></b><b></b><b></b><span style="font-size: large;"></span><span style="font-family: "georgia" , "times new roman" , serif;"></span></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-21819388619833281642017-10-27T00:58:00.000-07:002017-10-27T01:12:20.649-07:00SQL Azure Databases - Active Secondary - Failover Groups - Part 4<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">150th Blog Post . So, the post involves video demo :)</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Previous posts on this <a href="http://strictlysql.blogspot.sg/search/label/Geo%20Replication">series</a> was about using "Geo Replication" to configure active secondary. However, it comes with few limitations like</span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* No transparent failover capabilities like Always on Listener</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* No availability groups and group failover possible</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">So, Azure Failover Groups provides "Read Only" / "Read Write" connection URLs which allows applications transparent failovers. Read only and Read write workloads get different connection strings and upon failover, applications connect to the appropriate node automatically</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Also, one can combine group of databases as a unit of failover and make them failover together as well, as done in Always on availability groups. </span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Step by step instructions on configuring availability groups provided below</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;">* Configure Primary Server, Secondary Server and Geo Replication as explained in <a href="http://strictlysql.blogspot.sg/2017/05/sql-azure-databases-active-secondary.html">http://strictlysql.blogspot.sg/2017/05/sql-azure-databases-active-secondary.html</a></span></span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* Click on the primary server ( not database ) and select failover groups</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiRfInkd1pEyRyu7onVchXmj4A-A-4_TCiKKi8y6SaltULYYhynySxqNKSUfx5apofw8FAN9Owyt7vKSTHXpp9LlqvWXZowqC5NC9rO_YV0Xgo1NB1n4Yrh5D_U9Owpitf4UJtZp-zE6k/s1600/Failover_Group_creation.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="504" data-original-width="963" height="332" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiRfInkd1pEyRyu7onVchXmj4A-A-4_TCiKKi8y6SaltULYYhynySxqNKSUfx5apofw8FAN9Owyt7vKSTHXpp9LlqvWXZowqC5NC9rO_YV0Xgo1NB1n4Yrh5D_U9Owpitf4UJtZp-zE6k/s640/Failover_Group_creation.jpg" width="640" /></a></div>
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* Click on the add group and </span><br />
<br />
<div style="text-align: left;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> * Provide the failover group a name</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> * Pick the existing database "db1" to be added to the failover group</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> * Pick the existing secondary server</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> * Click on create</span></div>
<div style="text-align: left;">
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyIg1Fym-hlkG3AhQxRCMeQHKbxLjNN5Bpb4IXDcMTzWLjGKFU_qSGZqWOCsHYResOVfJiPdr1Z5c7Q_09hSyqNUJiX5Sdla1uRRM1oKPTiZn-XAwzISidTkFYdJ90mo0wbHXtP6Jh6CM/s1600/Failover_Group_Add_DB.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="409" data-original-width="1149" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyIg1Fym-hlkG3AhQxRCMeQHKbxLjNN5Bpb4IXDcMTzWLjGKFU_qSGZqWOCsHYResOVfJiPdr1Z5c7Q_09hSyqNUJiX5Sdla1uRRM1oKPTiZn-XAwzISidTkFYdJ90mo0wbHXtP6Jh6CM/s640/Failover_Group_Add_DB.jpg" width="640" /></a></div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* Once done, failover group is listed as shown below</span></div>
<div style="text-align: left;">
<span style="font-size: large;"></span><span style="font-family: "georgia" , "times new roman" , serif;"></span><br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhI347XwRtpSRxRinMVfU0PgGT2ysL-Rh8B_nk-MT_aU_NPqqgj783iaJhz9-kSAARDbkCrliLkPFGBdDJaKEnDRcVFkF9Jb_ky-Trrj-t3_BRW6XTYMfi7eajhPC3LBNBamaYcA0OEdCY/s1600/FG_Created.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="585" data-original-width="1570" height="238" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhI347XwRtpSRxRinMVfU0PgGT2ysL-Rh8B_nk-MT_aU_NPqqgj783iaJhz9-kSAARDbkCrliLkPFGBdDJaKEnDRcVFkF9Jb_ky-Trrj-t3_BRW6XTYMfi7eajhPC3LBNBamaYcA0OEdCY/s640/FG_Created.jpg" width="640" /></a></div>
<div style="text-align: left;">
<span style="font-size: large;"></span><span style="font-family: "georgia" , "times new roman" , serif;"></span><br /></div>
<div style="text-align: left;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* Click on the failover group, Read only and Read Write URLs can be seen as shown below</span></div>
<div style="text-align: left;">
<span style="font-size: large;"></span><span style="font-family: "georgia" , "times new roman" , serif;"></span><br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEildRjz3qNuk3JNHt5FRZjFOfrC909UkbbolNva2hAhY7sHNcAI9HRRmV4ba2nyxYHezaOT49vErwxriwc91rdFWCHmR2EXnJtcp1NiKeC6j9Mru8Pn3HbPgLFD_kM20rqAISj2W8WhONI/s1600/Read_Only_Read_Write_URL.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="886" data-original-width="1133" height="500" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEildRjz3qNuk3JNHt5FRZjFOfrC909UkbbolNva2hAhY7sHNcAI9HRRmV4ba2nyxYHezaOT49vErwxriwc91rdFWCHmR2EXnJtcp1NiKeC6j9Mru8Pn3HbPgLFD_kM20rqAISj2W8WhONI/s640/Read_Only_Read_Write_URL.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* To perform failover click on the failover icon. Post failover, failover group looks like the picture below</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_nnTKmIrYoOeGWIXaM5FFB-ogLMi4sg7UYwrra6dnw06CZlgJ1D1P7d30eBqQIrzwtP5shaviFf5TRiMSKYSBmWfjXAh9kOj1ZdGDvq1IPO2qhDpJOrmvf8Z5JWmhMvCrEBPMxIeNt_M/s1600/Post_Failover_FG.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="888" data-original-width="1111" height="510" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_nnTKmIrYoOeGWIXaM5FFB-ogLMi4sg7UYwrra6dnw06CZlgJ1D1P7d30eBqQIrzwtP5shaviFf5TRiMSKYSBmWfjXAh9kOj1ZdGDvq1IPO2qhDpJOrmvf8Z5JWmhMvCrEBPMxIeNt_M/s640/Post_Failover_FG.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">To make the understanding easier, I have recorded the video of the demo. Please take a look. Happy learning</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"></span><span style="font-family: "georgia" , "times new roman" , serif;"></span><br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div style="text-align: left;">
<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/B4Dt3NHQvzM" width="560"></iframe><br /></div>
</div>
</div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com5tag:blogger.com,1999:blog-1619266374223964354.post-36487684689523823012017-09-06T09:56:00.000-07:002017-09-06T10:00:11.114-07:00Dynamic Threshold for Statistic Update - SQL Server 2016<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">By default, on a SQL Server Database, statistics are updated automatically when one hits a threshold of changes. If the Auto Update Statistics setting is turned on by default, the threshold for number of changes to trigger auto statistic update before SQL Server 2016 was<br /><br />For tables with more than 500 rows - 20% of tables row count + 500 changes<br />For tables with less than 500 rows - 500 changes<br /><br />However, in modern day times, the above formula may not be effective. 10 million row tables is pretty much a norm and not typically considered huge. The threshold of changes for 10 million row table would be 2,000,500 changes, which is a huge number of changes required to trigger auto stat update. This effectively implies, on larger tables one would be running without updated statistics almost all the time as the threshold is too high as the tables get bigger. To address this problem, SQL Server 2016 has a behavior called dynamic statistic threshold, which is enabled by default.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">As the table gets bigger, the threshold for statistic update reduces. For tables greater than 25,000 rows dynamic statistics is applicable. For example consider the following table with 1 million rows<br /><br />Let’s update statistic before starting the task</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKn0oBEAyANtKRZUntSZnOFqrUnUqi-MoidG1nQ_WOEk1eWvzmUPDECUa52lzNLoz8bwiMU35uj1HdS8z46ChdUvceEs-IOEnWgjLkcYdaO87HfOTAYaIuMU5NQOO6aUrIlFzS-Efqf9Q/s1600/Check_Stat_time.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="311" data-original-width="844" height="233" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKn0oBEAyANtKRZUntSZnOFqrUnUqi-MoidG1nQ_WOEk1eWvzmUPDECUa52lzNLoz8bwiMU35uj1HdS8z46ChdUvceEs-IOEnWgjLkcYdaO87HfOTAYaIuMU5NQOO6aUrIlFzS-Efqf9Q/s640/Check_Stat_time.jpg" width="640" /></a></div>
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;">Make note the updated stat time <b>(12:12 AM)</b></span></span><br />
<b><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></b>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Now let’s run the following script to make 100,000 changes (10%)</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span><span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: blue; margin: 0px;">SET</span><span style="background: white; color: black; margin: 0px;"> </span><span style="background: white; color: blue; margin: 0px;">ROWCOUNT</span><span style="background: white; color: black; margin: 0px;"> 100000</span></span></span><br />
<span style="background: white; color: blue; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">GO</span></span><span style="background: white; color: black; margin: 0px;"></span><br />
<span style="background: white; color: green; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">/* SET Rowcount so that we can update
just 100K rows */</span></span><br />
<span style="background-color: white; color: #b01200; font-family: Georgia; font-size: large;"></span><span style="background: white; color: black; margin: 0px;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: magenta; margin: 0px;">Update</span><span style="background: white; color: black; margin: 0px;"> Production</span><span style="background: white; color: grey; margin: 0px;">.</span><span style="background: white; color: black; margin: 0px;">product_1M</span></span></span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: blue; margin: 0px;">SET</span><span style="background: white; color: black; margin: 0px;"> StandardCost </span><span style="background: white; color: grey; margin: 0px;">=</span><span style="background: white; color: black; margin: 0px;"> StandardCost </span><span style="background: white; color: grey; margin: 0px;">+</span><span style="background: white; color: black; margin: 0px;"> 5</span></span></span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><span style="background: white; color: blue; margin: 0px;">WHERE</span><span style="background: white; color: black; margin: 0px;"><span style="margin: 0px;"> </span>Productid </span><span style="background: white; color: grey; margin: 0px;">%</span><span style="background: white; color: black; margin: 0px;"> 5 </span><span style="background: white; color: grey; margin: 0px;">=</span><span style="background: white; color: black; margin: 0px;"> 0</span></span></span><br />
<span style="background: white; color: blue; line-height: 107%; margin: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">GO</span></span><br />
<b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />Please note on older versions of SQL Server, by default, 10% changes wouldn't suffice to trigger auto stat update.</span><br />
<span style="font-family: "georgia"; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Now let’s check if statistics are updated. A select is query has to be fired on the table to trigger auto statistic update.</span><br />
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3_UJJqCiGD6FDvPSQ-jptmkBQpan5zlcyt1SUNz6CgenoSnCNEZvBpz7xtDqdgw7onW4Zwb-5kQil99gHj1bsYlcPdF0BdurOaTFAZ2NaXLSGjweLxyRblJJjEWZglWqaJVxrIfn8M-8/s1600/updated_Stat.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="507" data-original-width="906" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3_UJJqCiGD6FDvPSQ-jptmkBQpan5zlcyt1SUNz6CgenoSnCNEZvBpz7xtDqdgw7onW4Zwb-5kQil99gHj1bsYlcPdF0BdurOaTFAZ2NaXLSGjweLxyRblJJjEWZglWqaJVxrIfn8M-8/s640/updated_Stat.jpg" width="640" /></a></div>
<div>
<span style="font-family: "georgia"; font-size: large;"></span><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div>
<span style="font-family: "georgia"; font-size: large;">Surprisingly they are updated as the statistic updated time has changed to <b>12:15 AM</b>.</span></div>
<div>
<span style="font-family: "georgia"; font-size: large;"><br /></span></div>
<div>
<span style="font-family: "georgia"; font-size: large;">Though there is no official exact formula how statistic update has been triggered, following pic shows how the threshold percentage reduces with row count of the table.</span></div>
<div>
<span style="font-family: "georgia"; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4S4EjCRxXxyKPIleOUCqY-g10Afa3pKF8NN8LViHVQih2F_hvzq9FaDCcQ8ha2-PocRE2c42g-DnGhucnMhHtByOlJ_s5YhIDufVddjNOWJHCL2te58edRiNPKoDsbi8yiCddTv24U00/s1600/threshold_percentage.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="361" data-original-width="579" height="396" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4S4EjCRxXxyKPIleOUCqY-g10Afa3pKF8NN8LViHVQih2F_hvzq9FaDCcQ8ha2-PocRE2c42g-DnGhucnMhHtByOlJ_s5YhIDufVddjNOWJHCL2te58edRiNPKoDsbi8yiCddTv24U00/s640/threshold_percentage.bmp" width="640" /></a></div>
<div>
<span style="font-family: "georgia"; font-size: large;"><br /></span></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Picture referred from the following blog: <a href="https://blogs.msdn.microsoft.com/saponsqlserver/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371/">https://blogs.msdn.microsoft.com/saponsqlserver/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371/</a></span><br />
<div>
<span style="font-family: "georgia"; font-size: large;"><br /></span></div>
<div>
<span style="font-family: "georgia"; font-size: large;">Dynamic Statistic update threshold is not a new feature and has been available since SQL Server 2008 via the trace flag 2371. Meaning, one can enable "Dynamic Statistic update threshold" on SQL Server 2008 and after by turning the trace flag 2371 on. On SQL Server 2016 and after, "Dynamic Statistic update threshold" has been made a default behavior.</span></div>
<br /></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-84302558750407377422017-09-03T06:33:00.000-07:002017-09-03T06:36:40.338-07:00DBCC CloneDatabase for SQL Server 2012<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Dear All,</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">In case you are wondering, title of the post is not a typo :) DBCC CloneDatabase is going to be available for SQL Server 2012 :)</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">For folks who don't know, DBCC CloneDatabase is a feature introduced in SQL Server 2014 SP2 for creating a skeleton copy of the database. When I mean skeleton copy, I mean without the data but the exact schema, table structure, index, constraints, file and file groups and the best of all - table and index statistics. Using DBCC CloneDatabase, one can actually get a exact copy of the database ( including actual statistics ) but without the data. This is extremely useful in performance troubleshooting as one can clone a production database using DBCC CloneDatabase and backup the cloned database and restore to DEV / UAT to get estimated plans. SQL Server 2016 onwards Querystore's data and reports are also included to the cloned database. One can read more about DBCC CloneDatabase over <a href="https://support.microsoft.com/en-us/help/3177838/how-to-use-dbcc-clonedatabase-to-generate-a-schema-and-statistics-only">here</a></span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Coming back to the title of the post, Microsoft product team will be extending DBCC CloneDatabase to SQL Server 2012 as a part of SQL Server 2012 Service Pack 4. SQL Server 2012 Service Pack 4 ( SP4 ) is due to be released in September 2017. A excellent move by product team to extend cloned database SQL Server 2012. Check out the official announcement of SP4 over <a href="https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-sql-server-2012-service-pack-4/">here</a>. More importantly, observe the comments section :) </span></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-13942424472884828532017-08-28T06:56:00.000-07:002017-08-28T06:56:52.499-07:00Data Channel Interview - 08 - Leila Etaati on Machine Learning using Microsoft BI<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="text_exposed_root text_exposed" id="id_59a41e9d280082658143192">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Dear All,</span><br />
<span style="font-family: "georgia"; font-size: large;"><br />
</span> <span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> Very happy to share the 8th interview of </span><a class="_58cn" data-ft="{"tn":"*N","type":104}" href="https://www.facebook.com/hashtag/datachannel?source=feed_text"><span class="_5afx"><span aria-label="hashtag" class="_58cl _5afz"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">#</span></span><span class="_58cm"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">DataChannel</span></span></span></a><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">. Dr. Leila Etaati MVP, MCT from New Zealand discusses Machine Learning using Microsoft Business Intelligence tools in this interview. Leila gives us an insight into Machine Learning process and explains how Azure ML, Power BI, R and SQL Server can be used in it. Thanks Leila for the informative interview</span><br />
<span style="font-family: "georgia"; font-size: large;"></span></div>
<div class="text_exposed_root text_exposed">
<b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><br />
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/rNuUqekvkaw" width="560"></iframe><br />
<br /></div>
</div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-37679348341093447872017-06-09T01:00:00.000-07:002017-10-27T01:01:38.175-07:00SQL Azure Databases - Active Secondary - Failover - Part 3 <div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Continuing on the series on <a href="http://strictlysql.blogspot.sg/2017/05/sql-azure-databases-active-secondary_27.html">SQL Azure Databases - Active Secondary</a>,
this post will explore the task of failing over to secondary.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Fairly straightforward task, just one needs to take care of
few items.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 1:</b> Login into portal.azure.com and click on Databases.
The primary and secondary databases are listed</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-bGzz5XzKFelndZ5YALhRSHfLgG0v1sHfsB401pwwNpLdd44EAbNIxP8qv4t0QcziHYe9xEBB7O0_XqNsE07MtjlE2NLBat7OqjpGNemg6-Zik1n_3C1DT_twUspGoMZJ4l5P7EclqUU/s1600/sql_databases.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="488" data-original-width="1245" height="249" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-bGzz5XzKFelndZ5YALhRSHfLgG0v1sHfsB401pwwNpLdd44EAbNIxP8qv4t0QcziHYe9xEBB7O0_XqNsE07MtjlE2NLBat7OqjpGNemg6-Zik1n_3C1DT_twUspGoMZJ4l5P7EclqUU/s640/sql_databases.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 2:</b> Click on the primary and then click on "Geo
Replication"</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihsR3EGRGctmgEBhXVVij7wnPqeGHy9qnWpq5Jxeok0qLIlbnE_poIsz1AMTh20lmNYP0gvJkys9gBt3AizO0-YDbA5D-WqLr9mPcYQ2YmUW_0yLS9cGEarv9klIuY2w0IB50v-ND6yaA/s1600/Geo_replication.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="545" data-original-width="1158" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihsR3EGRGctmgEBhXVVij7wnPqeGHy9qnWpq5Jxeok0qLIlbnE_poIsz1AMTh20lmNYP0gvJkys9gBt3AizO0-YDbA5D-WqLr9mPcYQ2YmUW_0yLS9cGEarv9klIuY2w0IB50v-ND6yaA/s640/Geo_replication.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 3:</b> Scroll down and click on the secondary as shown
below</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIjJc_ErsTLNTvXyDF7MuT8fqBuCc3XVQfJWdTQ99wkBZ19Sd_YV9ajoHj6GkLSAR8uYxdk1vdbEFp0S8YaIfKc-QeifwLtoEeuePtraC9g0dYixqFbV0t3mYa5hv2zcneDjUES0bByi4/s1600/secondaries.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="529" data-original-width="1084" height="312" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIjJc_ErsTLNTvXyDF7MuT8fqBuCc3XVQfJWdTQ99wkBZ19Sd_YV9ajoHj6GkLSAR8uYxdk1vdbEFp0S8YaIfKc-QeifwLtoEeuePtraC9g0dYixqFbV0t3mYa5hv2zcneDjUES0bByi4/s640/secondaries.jpg" width="640" /></a></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 4:</b> Click on Failover as shown below</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8cwy8y2jivZC6ibaNllUSrTyAapb6S0eyuYdc2WyZ754D3y8J8HYREdcytitkbEjbadwjes_nSblZEHonhsNoSadVzwczE8mWZNyU_y-MB8vxtMJ8u_voLQ6Snry55tqhlPNfnY2pXVY/s1600/secondary_failover.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="487" data-original-width="538" height="578" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8cwy8y2jivZC6ibaNllUSrTyAapb6S0eyuYdc2WyZ754D3y8J8HYREdcytitkbEjbadwjes_nSblZEHonhsNoSadVzwczE8mWZNyU_y-MB8vxtMJ8u_voLQ6Snry55tqhlPNfnY2pXVY/s640/secondary_failover.jpg" width="640" /></a></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 5:</b> Click "Ok" to confirm Failover</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBXigERq3Y2LXDoWdvuKNi3u2r6fKHBebbgPNdnzSKRi2Z9IR0Wl4zExL6_mct53QUfOx2iBw-jYegNMVe62bc38NbLjPEWzqF__iw3c5YrGXZ_mzr832LygoB-hA2rZut98YEtWvT6cs/s1600/failover_msg.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="328" data-original-width="312" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBXigERq3Y2LXDoWdvuKNi3u2r6fKHBebbgPNdnzSKRi2Z9IR0Wl4zExL6_mct53QUfOx2iBw-jYegNMVe62bc38NbLjPEWzqF__iw3c5YrGXZ_mzr832LygoB-hA2rZut98YEtWvT6cs/s400/failover_msg.jpg" width="380" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 6:</b> The dotted lines between primary and secondary,
showing the failover progress, turn to solid line upon completion of failover</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidTagE3JMFdbdo83Q2DaJY97EhK97vVzBzPJYesM9lQZZ4aK_WLC9IuQo7R8IQDdQTenk0xlkd8oVuu3yzIFJXxzCo5n_RCYlVnEJ67o7i-FdvPqEH1zv6DcU28ePeWkLNIkZvVkwj5JE/s1600/failover_completion.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="728" data-original-width="1280" height="364" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidTagE3JMFdbdo83Q2DaJY97EhK97vVzBzPJYesM9lQZZ4aK_WLC9IuQo7R8IQDdQTenk0xlkd8oVuu3yzIFJXxzCo5n_RCYlVnEJ67o7i-FdvPqEH1zv6DcU28ePeWkLNIkZvVkwj5JE/s640/failover_completion.jpg" width="640" /></a></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 7:</b> After failover, the replication role changes
confirming the successful failover as shown below</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhb1N-KzQ0IZt84JB0hy9slkOqh-1m3H05RV8wYZ7IhriTbFe-pe1Xpqgm8c3QMRzEn3ezZzENvqAelcGhPL66fAf6wj35xm4IMQjUgsHoKKka_T7f01p4U2SH9YXTFJ31_RoSsr4i8JBo/s1600/post_failover.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="344" data-original-width="1098" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhb1N-KzQ0IZt84JB0hy9slkOqh-1m3H05RV8wYZ7IhriTbFe-pe1Xpqgm8c3QMRzEn3ezZzENvqAelcGhPL66fAf6wj35xm4IMQjUgsHoKKka_T7f01p4U2SH9YXTFJ31_RoSsr4i8JBo/s640/post_failover.jpg" width="640" /></a></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Step 8:</b> Secondary server accepts write operations as shown
below</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcL4UpF2F6vs7f8-qcSyVAzd-K7vFNpEhcHOFHdH-HUylhCzqfte2oFNX9pALBVoXISnxLFDXlBGKmcjxTQvoJ7mmInCZJrD1rRNo7xdPGOyVkIo93AGgstlMWdUjPjANqg-Vr_5pUb2w/s1600/post_failover_verfication.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="736" data-original-width="1280" height="368" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcL4UpF2F6vs7f8-qcSyVAzd-K7vFNpEhcHOFHdH-HUylhCzqfte2oFNX9pALBVoXISnxLFDXlBGKmcjxTQvoJ7mmInCZJrD1rRNo7xdPGOyVkIo93AGgstlMWdUjPjANqg-Vr_5pUb2w/s640/post_failover_verfication.jpg" width="640" /></a></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Few important things to know<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">This failover is like a DB Mirroring failover. This means
application will need to change their connection strings to secondary server
upon failover. Unlike, Always on Availability groups, azure geo replication
explained above, doesn't support transparent application failover.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">However, Azure Failover Groups (right now in preview) allows
transparent failover for applications. More about that on next post :)</span><o:p></o:p></div>
</div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-16837799064495237742017-05-27T09:30:00.002-07:002017-10-27T01:01:51.128-07:00SQL Azure Databases - Active Secondary - How to connect? - Part 2<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;">As a continuation of "SQL Azure Databases - Active Secondary" series started <a href="http://strictlysql.blogspot.sg/2017/05/sql-azure-databases-active-secondary.html">earlier</a>, this post will cover how to connect to a azure SQL Database primary and secondary after configuring "Geo Replication".</span></span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;">To some, it may sound like "what is the big deal in connecting to, after configuring it". If you are new to azure, it can take a while for you figure out how to connect and at sometimes it can turn out to be one of those annoying simple things, which refuse come off in easily :)</span></span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;">As described in the earlier <a href="http://strictlysql.blogspot.sg/2017/05/sql-azure-databases-active-secondary.html">post</a>, we have configured the following</span></span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;">* Database called "rajprimary" on "rajprimarysvr" server<br />* Geo replicated database copy of "rajprimary" on "rajsecondary" server </span></span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;">To connect to the primary or the secondary server, follow the steps provided below<br /><br /><b>Configure the firewall:</b></span></span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;">* Login to your portal.azure.com<br /><br />* Click on SQL Databases on the left </span></span><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOBdcaxH5MfGda9tWUO6CnSzJaKaPOBXdCxU_EbtRWSih1LdfYjmCRLk8get9L9CmJfIBHfsPU3J2vK8SECuJ0LYO-i-ZHV0diGt-P0utX9esT3d8fAldQnf9JhnlczoXnajsH6VaUaJA/s1600/how_to_connect.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="372" data-original-width="1114" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOBdcaxH5MfGda9tWUO6CnSzJaKaPOBXdCxU_EbtRWSih1LdfYjmCRLk8get9L9CmJfIBHfsPU3J2vK8SECuJ0LYO-i-ZHV0diGt-P0utX9esT3d8fAldQnf9JhnlczoXnajsH6VaUaJA/s640/how_to_connect.jpg" width="640" /></a></div>
<br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;"><br />* Click on the primary / secondary databases ( as shown under replication role), depending upon the one you would like to connect to</span></span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-size: large;">* Click on set firewall as shown below</span></span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgdp0_-t7Y4VA-DZiQmfDp6hjUz_4inRE5xaa-eq46FcKBbIjkEeSePrkHuZVMgS6HIOh7oK95xq-iUgpZ5pEWnpcE1BqHsu0ej5P9hb3nmAP464vbwaY50XRvSRqBm6So70umC3zHeow/s1600/set_firewall.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="601" data-original-width="1383" height="278" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgdp0_-t7Y4VA-DZiQmfDp6hjUz_4inRE5xaa-eq46FcKBbIjkEeSePrkHuZVMgS6HIOh7oK95xq-iUgpZ5pEWnpcE1BqHsu0ej5P9hb3nmAP464vbwaY50XRvSRqBm6So70umC3zHeow/s640/set_firewall.jpg" width="640" /> </a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;">* Firewall settings are turned on and IP Addresses that are listed alone are allowed to access. Click on "add client IP" for allowing the machine that you are using, to connect to the azure database. </span></span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9N7CGKjG6aUmfc06ztk-eBuTq-hhhR4SfhEL0N3-zo004l-e5u447NYYTG9-aF1XJBmJx1mlqFoxyf1DxYagF2BcqbRE-3AgFhn5s1OH-QxedvxB1p8i-w4sPU_RjVan81ikhkazVgYo/s1600/add_ip.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="433" data-original-width="729" height="379" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9N7CGKjG6aUmfc06ztk-eBuTq-hhhR4SfhEL0N3-zo004l-e5u447NYYTG9-aF1XJBmJx1mlqFoxyf1DxYagF2BcqbRE-3AgFhn5s1OH-QxedvxB1p8i-w4sPU_RjVan81ikhkazVgYo/s640/add_ip.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;">* You may add other client IP Addresses to, if they are to connect to the database. Click on save after adding.</span></span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbj5YMaW9T-McK6CyNdM4PLZVFHS-P_sxdP0ceCLcfjbqU4kuPmydieAlt48d9dLn2B-Ub0Ni_09WH-8cP8mwLP4cY-aDxn95Kic9od_T6TEdiIYyjfGXK-eNVW_lr-UjybHe7Mo-fW9M/s1600/set_firewall_save.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="448" data-original-width="726" height="393" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbj5YMaW9T-McK6CyNdM4PLZVFHS-P_sxdP0ceCLcfjbqU4kuPmydieAlt48d9dLn2B-Ub0Ni_09WH-8cP8mwLP4cY-aDxn95Kic9od_T6TEdiIYyjfGXK-eNVW_lr-UjybHe7Mo-fW9M/s640/set_firewall_save.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;"></span></span><br /></div>
<div class="separator" style="clear: both; text-align: left;">
<b><span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;">Connect to the database</span></span></b></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;">* Start sql server management studio on the machine you are logged on, and connect to "<servername>.database.windows.net". In this case it would be <i>rajprimarysvr.database.windows.net</i></servername></span></span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnT6aS3Bif9vonsYSkqskC-snTxqhgk6GSZ94fCoauSMOKZWEcU46rEf4ZL98iTh7jWnATRnl36uTIquaD-FMU-wSZqva6QXL9bKxaDS5O50iOjC7OeG_kXyHM8Xb52-7HcPWWzTWkjO8/s1600/connect_to.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="495" data-original-width="696" height="452" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnT6aS3Bif9vonsYSkqskC-snTxqhgk6GSZ94fCoauSMOKZWEcU46rEf4ZL98iTh7jWnATRnl36uTIquaD-FMU-wSZqva6QXL9bKxaDS5O50iOjC7OeG_kXyHM8Xb52-7HcPWWzTWkjO8/s640/connect_to.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;"><i> </i>if you are not sure of your database servername, you may look at by clicking on "sql databases" -> "databasename" -> "Properties"</span></span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;"> * Provide the user account and password used while setting up the server</span></span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;">Upon connecting, one would notice that only database related components are visible. Server related components like SqlAgent, Server Error logs are not seen as we are using "Sql database" service on azure.</span></span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOq9XMssJXEgFw4eNeUh7XWNBD24i80THrfuueZ18AILezOCr0tFSpUMqvfW1NHWNHOJwdBtVxp7rfeg7QLl79mK_DNzYv1YfCs2QEX7zCIfOfnB_Uyfyd6k1qTY0yQnhVvur_fKexpaY/s1600/after_connect.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="420" data-original-width="465" height="578" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOq9XMssJXEgFw4eNeUh7XWNBD24i80THrfuueZ18AILezOCr0tFSpUMqvfW1NHWNHOJwdBtVxp7rfeg7QLl79mK_DNzYv1YfCs2QEX7zCIfOfnB_Uyfyd6k1qTY0yQnhVvur_fKexpaY/s640/after_connect.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;">Same steps can be repeated for secondary server too. More on Azure - Geo replication in upcoming posts. </span></span><b><span style="font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;"> </span></span></b></div>
</div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-69581971925028959712017-05-11T08:13:00.003-07:002017-05-11T08:15:43.281-07:00Find the number of rows for a value, without querying the table<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Dear all,</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Being a DBA, developers approach me with questions like can you run this query for me in production?</span><br />
<br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">Select</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; font-family: "consolas";">*</span><span style="background: white; font-family: "consolas";"> <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">FROM</span><span style="background: white; font-family: "consolas";"> <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; font-family: "consolas";">[dbo]</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; font-family: "consolas";">[FactInternetSales]<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas"; line-height: 115%;">WHERE</span><span style="background: white; font-family: "consolas"; line-height: 115%;"> UnitPrice </span><span style="background: white; font-family: "consolas"; line-height: 115%;">=</span><span style="background: white; font-family: "consolas"; line-height: 115%;"> 32.6</span></span></div>
<div class="MsoNormal">
<span style="background: white; font-family: "consolas"; line-height: 115%;"><span style="font-size: large;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Lets, say the table contains few billion rows and UnitPrice is not an indexed column, then query would take ages to provide the result. Not just that, it causes a massive I/O on a busy production database. <span class="Apple-tab-span" style="white-space: pre;"> </span></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">For the above situation, I would usually ask the developer, "Would you need a accurate value or is it ok if it is approximate?". If approximate numbers are fairly sufficient then, one can answer these type of questions without actually querying the table. How?</span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white; line-height: 115%;"></span></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Simple - STATISTICS is the word :)</span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">To find the statistic that will be useful for the query, please use the following script and provide the table name and column name. The script provides the statistic name we should be checking.</span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">Declare</span><span style="background: white; font-family: "consolas";"> @table_name </span><span style="background: white; color: blue; font-family: "consolas";">varchar</span><span style="background: white; font-family: "consolas";">(</span><span style="background: white; font-family: "consolas";">100</span><span style="background: white; font-family: "consolas";">)</span><span style="background: white; font-family: "consolas";"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">Declare</span><span style="background: white; font-family: "consolas";"> @Column_name </span><span style="background: white; color: blue; font-family: "consolas";">varchar</span><span style="background: white; font-family: "consolas";">(</span><span style="background: white; font-family: "consolas";">100</span><span style="background: white; font-family: "consolas";">)</span><span style="background: white; font-family: "consolas";"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">SET</span><span style="background: white; font-family: "consolas";"> @table_name </span><span style="background: white; font-family: "consolas";">=</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: red; font-family: "consolas";">'FactInternetSales'</span><span style="background: white; font-family: "consolas";"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">SET</span><span style="background: white; font-family: "consolas";"> @Column_name </span><span style="background: white; font-family: "consolas";">=</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: red; font-family: "consolas";">'UnitPrice'</span><span style="background: white; font-family: "consolas";"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">SELECT</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: magenta; font-family: "consolas";">OBJECT_NAME</span><span style="background: white; font-family: "consolas";">(</span><span style="background: white; font-family: "consolas";">s</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; color: magenta; font-family: "consolas";">object_id</span><span style="background: white; font-family: "consolas";">)</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: blue; font-family: "consolas";">AS</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: magenta; font-family: "consolas";">object_name</span><span style="background: white; font-family: "consolas";">,</span><span style="background: white; font-family: "consolas";"> <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: magenta; font-family: "consolas";">COL_NAME</span><span style="background: white; font-family: "consolas";">(</span><span style="background: white; font-family: "consolas";">sc</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; color: magenta; font-family: "consolas";">object_id</span><span style="background: white; font-family: "consolas";">,</span><span style="background: white; font-family: "consolas";"> sc</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; font-family: "consolas";">column_id</span><span style="background: white; font-family: "consolas";">)</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: blue; font-family: "consolas";">AS</span><span style="background: white; font-family: "consolas";"> column_name</span><span style="background: white; font-family: "consolas";">,</span><span style="background: white; font-family: "consolas";"> <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; font-family: "consolas";"> s</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; color: blue; font-family: "consolas";">name</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: blue; font-family: "consolas";">AS</span><span style="background: white; font-family: "consolas";"> statistics_name <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">FROM</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: lime; font-family: "consolas";">sys</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; color: lime; font-family: "consolas";">stats</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: blue; font-family: "consolas";">AS</span><span style="background: white; font-family: "consolas";"> s </span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; font-family: "consolas";">JOIN</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: lime; font-family: "consolas";">sys</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; color: lime; font-family: "consolas";">stats_columns</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: blue; font-family: "consolas";">AS</span><span style="background: white; font-family: "consolas";"> sc <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: blue; font-family: "consolas";">ON</span><span style="background: white; font-family: "consolas";"> s</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; font-family: "consolas";">stats_id </span><span style="background: white; font-family: "consolas";">=</span><span style="background: white; font-family: "consolas";"> sc</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; font-family: "consolas";">stats_id </span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; font-family: "consolas";">AND</span><span style="background: white; font-family: "consolas";"> s</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; color: magenta; font-family: "consolas";">object_id</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; font-family: "consolas";">=</span><span style="background: white; font-family: "consolas";"> sc</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; color: magenta; font-family: "consolas";">object_id</span><span style="background: white; font-family: "consolas";"> <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">WHERE</span><span style="background: white; font-family: "consolas";"> </span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: magenta; font-family: "consolas";">OBJECT_NAME</span><span style="background: white; font-family: "consolas";">(</span><span style="background: white; font-family: "consolas";">s</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; color: magenta; font-family: "consolas";">object_id</span><span style="background: white; font-family: "consolas";">)</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; font-family: "consolas";">like</span><span style="background: white; font-family: "consolas";"> @table_name <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; font-family: "consolas";">and</span><span style="background: white; font-family: "consolas";"> </span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: magenta; font-family: "consolas";">COL_NAME</span><span style="background: white; font-family: "consolas";">(</span><span style="background: white; font-family: "consolas";">sc</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; color: magenta; font-family: "consolas";">object_id</span><span style="background: white; font-family: "consolas";">,</span><span style="background: white; font-family: "consolas";"> sc</span><span style="background: white; font-family: "consolas";">.</span><span style="background: white; font-family: "consolas";">column_id</span><span style="background: white; font-family: "consolas";">)</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; font-family: "consolas";">like</span><span style="background: white; font-family: "consolas";"> @Column_name <o:p></o:p></span></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas"; line-height: 115%;">ORDER</span><span style="background: white; font-family: "consolas"; line-height: 115%;"> </span><span style="background: white; color: blue; font-family: "consolas"; line-height: 115%;">BY</span><span style="background: white; font-family: "consolas"; line-height: 115%;"> s</span><span style="background: white; font-family: "consolas"; line-height: 115%;">.</span><span style="background: white; color: blue; font-family: "consolas"; line-height: 115%;">name</span><span style="background: white; font-family: "consolas"; line-height: 115%;">;</span></span></div>
<div class="MsoNormal">
<span style="font-size: large;"><span style="background: white; font-family: "consolas"; line-height: 115%;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">After finding the statistic name, just go to the table, expand "Statistics" and double click on the stat that you are interested in. The click on the details section, find the value interested in on "Range_HI_Key" Column and observe the "EQ_Rows" section to get the approximate number of rows. </span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjub-Fgcvv3Xh-cqNYiDX_GibbCCEZs0Px0ieL5F-3RGPwPjSoGz5M01oPdurHiLDO2dqYvSxwIlHpNbPDktPfvoA3Yl90uQdhJGcfEvblpzXBgY_MNTw5TFbMYyfTcGGhAF3gLZkDus7w/s1600/Histogram.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="281" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjub-Fgcvv3Xh-cqNYiDX_GibbCCEZs0Px0ieL5F-3RGPwPjSoGz5M01oPdurHiLDO2dqYvSxwIlHpNbPDktPfvoA3Yl90uQdhJGcfEvblpzXBgY_MNTw5TFbMYyfTcGGhAF3gLZkDus7w/s640/Histogram.jpg" width="640" /></a></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white; line-height: 115%;"></span></span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Most of you would know that statistics can be used to find query estimates but few (I guess) would use it operationally for these kind of requests. One can also use them to estimate for queries of this type too</span></div>
<div class="MsoNormal">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">Select</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: magenta; font-family: "consolas";">count</span><span style="background: white; font-family: "consolas";">(*),</span><span style="background: white; font-family: "consolas";"> </span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; font-family: "consolas";">ProductKey </span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">from </span></span><span style="background-color: white; font-family: "consolas"; font-size: large;">[FactInternetSales]</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">Group</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: blue; font-family: "consolas";">by</span><span style="background: white; font-family: "consolas";"> ProductKey<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">Select</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: magenta; font-family: "consolas";">count</span><span style="background: white; font-family: "consolas";">(*)</span><span style="background: white; font-family: "consolas";"> </span><span style="background: white; color: blue; font-family: "consolas";">from</span><span style="background: white; font-family: "consolas";"> <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="background: white; font-family: "consolas";"><span style="font-size: large;">[FactInternetSales]<o:p></o:p></span></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: blue; font-family: "consolas";">WHERE</span><span style="background: white; font-family: "consolas";"> UnitPrice </span><span style="background: white; font-family: "consolas";">></span><span style="background: white; font-family: "consolas";"> 40 </span><span style="background: white; color: green; font-family: "consolas";">-- ( Possible but can get little tricky at times, more of
it on upcoming posts :) )</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-size: large;"><span style="background: white; color: green; font-family: "consolas";"><br /></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<b><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Few quick pointers:</span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">The above method relies on the following prerequisites</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* Either Auto create Stats should be turned on or the column should be the leading column of any index for the statistic to be present. Auto Create Stats are on by default</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* For the stat to be reasonably accurate, one needs to have Auto Update Statistics on ( which is also "on" by default )</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* Automatically created statistics are named like '_WA%'. For a column to have a auto created stat, the column should have been queried at least once since database creation. Indexed column would not need auto created statistics</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white;"></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">* To figure out when was the statistic last updated, use the following query </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white; color: blue;">SELECT</span><span style="background: white;"> <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white;">sp</span><span style="background: white; color: grey;">.</span><span style="background: white;">stats_id</span><span style="background: white; color: grey;">,</span><span style="background: white;"> </span><span style="background: white; color: blue;">name</span><span style="background: white; color: grey;">,</span><span style="background: white;"> filter_definition</span><span style="background: white; color: grey;">,</span><span style="background: white;"> last_updated</span><span style="background: white; color: grey;">,</span><span style="background: white;"> </span><span style="background: white; color: blue;">rows</span><span style="background: white; color: grey;">,</span><span style="background: white;"> <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white;">rows_sampled</span><span style="background: white; color: grey;">,</span><span style="background: white;"> steps</span><span style="background: white; color: grey;">,</span><span style="background: white;"> unfiltered_rows</span><span style="background: white; color: grey;">,</span><span style="background: white;"> modification_counter <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white; color: blue;">FROM</span><span style="background: white;"> </span><span style="background: white; color: lime;">sys</span><span style="background: white; color: grey;">.</span><span style="background: white; color: lime;">stats</span><span style="background: white;"> </span><span style="background: white; color: blue;">AS</span><span style="background: white;"> stat <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white; color: grey;">CROSS</span><span style="background: white;"> </span><span style="background: white; color: grey;">APPLY</span><span style="background: white;"> </span><span style="background: white; color: lime;">sys</span><span style="background: white; color: grey;">.</span><span style="background: white;">dm_db_stats_properties</span><span style="background: white; color: grey;">(</span><span style="background: white;">stat</span><span style="background: white; color: grey;">.</span><span style="background: white; color: magenta;">object_id</span><span style="background: white; color: grey;">,</span><span style="background: white;"> stat</span><span style="background: white; color: grey;">.</span><span style="background: white;">stats_id</span><span style="background: white; color: grey;">)</span><span style="background: white;"> </span><span style="background: white; color: blue;">AS</span><span style="background: white;"> sp <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white; color: blue;">WHERE</span><span style="background: white;"> stat</span><span style="background: white; color: grey;">.</span><span style="background: white; color: magenta;">object_id</span><span style="background: white;"> </span><span style="background: white; color: grey;">=</span><span style="background: white;"> </span><span style="background: white; color: magenta;">object_id</span><span style="background: white; color: grey;">(</span><span style="background: white; color: red;">'people_Data'</span><span style="background: white; color: grey;">)</span><span style="background: white;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
</div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white; color: grey;">and</span><span style="background: white;"> </span><span style="background: white; color: blue;">name</span><span style="background: white;"> </span><span style="background: white; color: grey;">like</span><span style="background: white;"> </span><span style="background: white; color: red;">'_WA_Sys_0000000D_1273C1CD'</span><span style="background: white; color: grey;">;</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white; color: grey;"><br /></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="background: white;">* </span></span><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">If the value you are looking for doesn't appear as a "Range_HI_Key" on the histogram then refer to the next biggest value on the "Range_Hi_Key" and observe "Avg_Range_Rows" for the estimates. Please refer to this post <a href="http://strictlysql.blogspot.sg/2016/01/sql-server-statistics-histograms.html">http://strictlysql.blogspot.sg/2016/01/sql-server-statistics-histograms.html</a> for interpreting histograms.</span></div>
<div>
<br /></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">There is always more to statistics. Will cover them in upcoming posts.</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
</div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com4tag:blogger.com,1999:blog-1619266374223964354.post-39807261620326902652017-05-07T10:00:00.001-07:002017-10-27T01:02:06.301-07:00SQL Azure Databases - Active Secondary - How to configure? - Part 1<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Dear all,</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">I have taken the azure dive at last :) </span><br />
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">In case if you are new to azure, azure is the Microsoft cloud platform service, offering lots of services for handling data platform tasks. There are tons and tons of material on it and </span><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">perhaps you can get a good introduction on the following links - </span><a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-technical-overview"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">here</span></a><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> and </span><a href="https://docs.microsoft.com/en-us/azure/fundamentals-introduction-to-azure"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">here</span></a></div>
<br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">This post is about SQL Azure Database - a service (a PAAS service ) which allows one to host just a database in cloud and a feature of it called "active secondary". "Geo Replication" - Active Secondary is a feature in SQL Azure in premium service tier ( similar to our enterprise edition but certainly not as costly :) ), which synchronizes data asynchronously to another server (either in the same geographical location or different ) and allows one to run read only queries to it.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">In my opinion, the below specifications are perhaps the most common requirement of most modern day mid size applications.</span><br />
<br />
<ul style="text-align: left;">
<li> <span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">A database with good performance to store and retrieve data </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">A simple disaster recovery / high availability solution</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Readable secondary if possible</span></li>
</ul>
<span style="font-family: "georgia"; font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">All these with a really affordable cost ( will discuss that part soon ) , just in few clicks is what makes SQL Azure tick. This post will run thro the details on how to configure it.</span><br />
<span style="font-family: "georgia"; font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><strong>Pre requisite:</strong></span><br />
<span style="font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">1) Grab a Microsoft Azure subscription :)</span><br />
<span style="font-family: "georgia"; font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"></span><strong> <span style="font-family: "georgia"; font-size: large;">Demo :</span></strong><br />
<span style="font-family: "georgia"; font-size: large;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Now for the step by step "how to do" screenshots :) </span><br />
<span style="font-family: "georgia"; font-size: large;"><br />
1) Login to portal.azure.com and <span style="font-family: "georgia" , "times new roman" , serif;">Click on SQL Databases -> Click on Create SQL Database</span><br />
<br />
<span style="font-family: "times new roman"; font-size: small;">
</span><br />
</span><br />
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia"; font-size: large;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-W2npNaMZdzREYI_061mGdCzcDVEkrj0QcHMWswh9On-RjDP-T1yIh79Hebq6Cjvnv-9yf5LTFMTRwp5g-8z3nA8cY9mJohb7rOOQ-aAmueS5milZoAGSCftQhbEtil7Mtt1tNjVQ-CQ/s1600/create_database.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="316" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-W2npNaMZdzREYI_061mGdCzcDVEkrj0QcHMWswh9On-RjDP-T1yIh79Hebq6Cjvnv-9yf5LTFMTRwp5g-8z3nA8cY9mJohb7rOOQ-aAmueS5milZoAGSCftQhbEtil7Mtt1tNjVQ-CQ/s640/create_database.jpg" width="640" /></a></span></div>
<span style="font-family: "georgia"; font-size: large;">
<br />
2) <span style="font-family: "georgia" , "times new roman" , serif;">Provide the server name. Though one pays only for the
database, one just needs to create a server to host the database. Server will
be maintained by azure and no charges are applicable for the server while using “sql azure database”
service.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;">
</span><br />
</span><br />
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia"; font-size: large;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3NWi_5Sgjpjq1-hzxQUmi-ILRUOqTI95EBGTpwTvmWbuHZLoq12gCU77doKOysYZuAqyEHmDtsO-LYtLbh95o_8CaknqMlll6v_jBWuvgcqOHMSBLBJJpjByEkX6bjEmdmafElGnyuT4/s1600/create_database_name.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3NWi_5Sgjpjq1-hzxQUmi-ILRUOqTI95EBGTpwTvmWbuHZLoq12gCU77doKOysYZuAqyEHmDtsO-LYtLbh95o_8CaknqMlll6v_jBWuvgcqOHMSBLBJJpjByEkX6bjEmdmafElGnyuT4/s640/create_database_name.jpg" width="620" /></a></span></div>
<span style="font-family: "georgia"; font-size: large;">
</span>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<span style="font-family: "georgia"; font-size: large;">
</span>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia"; font-size: large;"><span style="font-family: "times new roman"; font-size: small;">
</span></span></div>
<span style="font-family: "georgia"; font-size: large;">
</span>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia"; font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;">Provide unique names for </span></span></div>
<span style="font-family: "georgia"; font-size: large;">
</span>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia"; font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;">
</span></span></div>
<span style="font-family: "georgia"; font-size: large;">
</span>
<div class="MsoListParagraphCxSpFirst" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "georgia"; font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-family: "symbol"; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "times new roman"; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]-->Database name</span></span></div>
<span style="font-family: "georgia"; font-size: large;">
</span>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia"; font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;">
</span></span></div>
<span style="font-family: "georgia"; font-size: large;">
</span>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "georgia"; font-size: large;"><span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-family: "symbol"; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "times new roman"; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]-->Resource group ( more about it later )</span></span></div>
<span style="font-family: "georgia"; font-size: large;">
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia" , "times new roman" , serif;">
</span></div>
<div class="MsoListParagraphCxSpLast" style="margin: 0in 0in 10pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: "georgia" , "times new roman" , serif;"><span style="font-family: "symbol"; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "times new roman"; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]-->Server name</span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "times new roman"; font-size: small;">
</span></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif;"><o:p> 3) Click on "Configure required settings" and provide S</o:p>erver Details to be provided as shown below</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcnOQ4R2MEGMkav4HY3GR7Sq-KWqkhEXrSek73BsCpluinQEy-1KOM-Rs97VvfjXkAwStRDCFU_-mwmhiVM1KHAkYbt0bZwyuSdm49RyU1m0o6VNjf6HpCEPdGh3lHvDr-jB5OaOKUzJM/s1600/3_server_Details.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcnOQ4R2MEGMkav4HY3GR7Sq-KWqkhEXrSek73BsCpluinQEy-1KOM-Rs97VvfjXkAwStRDCFU_-mwmhiVM1KHAkYbt0bZwyuSdm49RyU1m0o6VNjf6HpCEPdGh3lHvDr-jB5OaOKUzJM/s640/3_server_Details.jpg" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
4) Set the service level to "Premium"</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGIJaUZGX7e65k8WeTjKcwoTY3sQ-QCOC5ZrG46lYc_Z1-qZ6Afp7JTFhjEOuSy0UGsOYk7wmvEKDCvXvhLpUwRZhLdYBX1gDeFLls22kraEZINFw6Cu7fNZS1K5hF6xfjSYJ2xqKIMeU/s1600/4_set_pricing_Tier.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="288" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGIJaUZGX7e65k8WeTjKcwoTY3sQ-QCOC5ZrG46lYc_Z1-qZ6Afp7JTFhjEOuSy0UGsOYk7wmvEKDCvXvhLpUwRZhLdYBX1gDeFLls22kraEZINFw6Cu7fNZS1K5hF6xfjSYJ2xqKIMeU/s640/4_set_pricing_Tier.jpg" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "calibri";"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">One needs to pick Premium to have readable secondary</span></span></div>
<span style="font-family: "times new roman"; font-size: small;"><span style="font-family: "georgia"; font-size: large;"><div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif;">5) Click on the create database to complete primary setup.
Creation takes few minutes</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "times new roman"; font-size: small;">
</span><br />
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "times new roman"; font-size: small;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLe-RpMXz0FUZwFGVK8S4Xb22PObwToZHOCx3RGY4ewNxSn67uwKfH6T6lvf3Vj3IAig-x0_Pgxjn_q2B8ruMiVxCSs3BMOPUyzFK3BhvOdHc8nTUHvEjTZRq_UPnBn0VzB9btvxkMogU/s1600/5_Create_PRimary.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLe-RpMXz0FUZwFGVK8S4Xb22PObwToZHOCx3RGY4ewNxSn67uwKfH6T6lvf3Vj3IAig-x0_Pgxjn_q2B8ruMiVxCSs3BMOPUyzFK3BhvOdHc8nTUHvEjTZRq_UPnBn0VzB9btvxkMogU/s640/5_Create_PRimary.jpg" width="264" /></a></span></div>
</div>
</span><div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
</span><div class="separator" style="clear: both; text-align: center;">
<br /></div>
6) Configure GEO replication - secondary <br />
<span style="font-family: "times new roman"; font-size: small;"></span><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif;">Once the notification bar indicates that the primary has been
created, click on sql databases to find the primary database created. </span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif;">Pick “Geo
Replication” as shown below</span></div>
<span style="font-family: "georgia" , "times new roman" , serif;">
</span><div class="separator" style="clear: both; text-align: center;">
<span style="font-family: "georgia" , "times new roman" , serif;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRqiVgGhmlKtBwRnHcU4QdJncDvs0vNod-vdVzdq8GtrtoOt2acU6PTTNGXpLzGh7K3J_-cwR-nrBk8ej8NZptiP7F8Eart0Nh465QDmTuyMm_DQYmrkYL5lpum3zDqE7cm_OvbEvpSpQ/s1600/6_geo_replication.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRqiVgGhmlKtBwRnHcU4QdJncDvs0vNod-vdVzdq8GtrtoOt2acU6PTTNGXpLzGh7K3J_-cwR-nrBk8ej8NZptiP7F8Eart0Nh465QDmTuyMm_DQYmrkYL5lpum3zDqE7cm_OvbEvpSpQ/s640/6_geo_replication.jpg" width="536" /></a></span></div>
<br />
</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">7) Configure GEO Replication</span><br />
<span style="font-family: "georgia"; font-size: large;"></span><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "calibri";"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">The screen below allows one to pick the geographical location of the
secondary server. If one needs lesser latencies, closer locations to primary
are preferred</span> </span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNzEOfuTu5I3ww1fmWKzRFcbt7emmeoGwC_5mmgxNmCjsRMYjOMp2phr05VBQ0jnxDbE7ImC601MY3qqoYggIr4jFc5yErulmKVX-oXco5vH0TxLf_6tQjbjDgPybe2-cTvbRe502JCJc/s1600/7_geo_replication_ocation.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="512" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNzEOfuTu5I3ww1fmWKzRFcbt7emmeoGwC_5mmgxNmCjsRMYjOMp2phr05VBQ0jnxDbE7ImC601MY3qqoYggIr4jFc5yErulmKVX-oXco5vH0TxLf_6tQjbjDgPybe2-cTvbRe502JCJc/s640/7_geo_replication_ocation.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia"; font-size: large;">8) Once location is picked, one is prompted to fill in the rest of the details of secondary server</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCO09i9K_tBduvOWkn8SGRalqPKq4kc7Snxd7s6NHAPRUA_7iib6JL84Rj6rsjOal2UDrdpaZvOEuv2pixshaTT6IZqfUcK0HbY9hV35OjUbApgmuY9QUc6LCaKAiqfsnz5hUl4RJOs74/s1600/8_Configure_Secondary_Settings.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCO09i9K_tBduvOWkn8SGRalqPKq4kc7Snxd7s6NHAPRUA_7iib6JL84Rj6rsjOal2UDrdpaZvOEuv2pixshaTT6IZqfUcK0HbY9hV35OjUbApgmuY9QUc6LCaKAiqfsnz5hUl4RJOs74/s640/8_Configure_Secondary_Settings.jpg" width="265" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> 9) </span><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Create a secondary server similar to primary, by providing
details like servername and admin login name</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCQiIu5PNLRMiO6BgVHi1rFZCr_7qUeK1BA6DUb7Kf9y8yPxayFMkXF4U2ceXHctxd_Q8wzE3veH62qmUGCCae2mZL_xWcD7oZ3qGHOhMUBG-0CGbAQPc7mTNtUrbpxn5z4gebfvgKd48/s1600/9_Configure_Secondary_Server.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCQiIu5PNLRMiO6BgVHi1rFZCr_7qUeK1BA6DUb7Kf9y8yPxayFMkXF4U2ceXHctxd_Q8wzE3veH62qmUGCCae2mZL_xWcD7oZ3qGHOhMUBG-0CGbAQPc7mTNtUrbpxn5z4gebfvgKd48/s640/9_Configure_Secondary_Server.jpg" width="504" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">10) Create the secondary server</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqN_wyH5bkzqpvE4GVa6wMHGNROxrtlRawi6JVfzT33vQMrNBdUZb-ipyBZux9l1Wi7GeStXtcL1tHNsSIdBuXohUM4OFD_K34LvyP5C1n72QmgGfqtOtsLUdATnvqpHNMP1nDETuqQOU/s1600/10_create_secondary.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqN_wyH5bkzqpvE4GVa6wMHGNROxrtlRawi6JVfzT33vQMrNBdUZb-ipyBZux9l1Wi7GeStXtcL1tHNsSIdBuXohUM4OFD_K34LvyP5C1n72QmgGfqtOtsLUdATnvqpHNMP1nDETuqQOU/s640/10_create_secondary.jpg" width="243" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Upon clicking ok, Primary and secondary starts to
synchronize as shown below</span></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaI7j2uHye9C-aIDr5_gFPFxuhyphenhyphenZ0yxaPO3eM-wUGdfby7bwq7Dib1zAuZJvisAGHtick7XpIEsnlmG71lxLrKjvi9lKR1emMFPsRb__X4YXF3-CKkaQyJR4aL8lPXHmIBKxyhIA4yz2w/s1600/11_sync_progress.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="460" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaI7j2uHye9C-aIDr5_gFPFxuhyphenhyphenZ0yxaPO3eM-wUGdfby7bwq7Dib1zAuZJvisAGHtick7XpIEsnlmG71lxLrKjvi9lKR1emMFPsRb__X4YXF3-CKkaQyJR4aL8lPXHmIBKxyhIA4yz2w/s640/11_sync_progress.jpg" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">11) Synchronization completion</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia"; font-size: large;">The completed tick marks will indicate synchronization completion as shown below</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpOpdMGCGzNfqWHs6UruV6sbCB14FbbGkXgLvVlZoz6BXL6nEaUk7FTS_clp7wquYd8QiXXNdm1Dg3_yzJA6JCHYczEPYRFZnfr7ua2rO5dLsxsEj2xi24CduEFUl3ggx4RalLNDnCqWU/s1600/12_sync_completion.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpOpdMGCGzNfqWHs6UruV6sbCB14FbbGkXgLvVlZoz6BXL6nEaUk7FTS_clp7wquYd8QiXXNdm1Dg3_yzJA6JCHYczEPYRFZnfr7ua2rO5dLsxsEj2xi24CduEFUl3ggx4RalLNDnCqWU/s640/12_sync_completion.jpg" width="587" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia"; font-size: large;">Synchronization can take few minutes or longer depending upon data size. Once complete, click on SQL Databases on Azure Portal to verify as shown below</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbxvh9ephqDV8ZeK0qA91bRi3ozRUslz0mdj0vrmZtULiQjRGCVy12KH8N4xixgYUsTSZVpxnmO_efpUicj6AsE8FvN73Oa-BIqgGbm09jQeiCKuGscBi3Qn9bBHuF4mtZamEdNl1uDq4/s1600/13_Final.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="171" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbxvh9ephqDV8ZeK0qA91bRi3ozRUslz0mdj0vrmZtULiQjRGCVy12KH8N4xixgYUsTSZVpxnmO_efpUicj6AsE8FvN73Oa-BIqgGbm09jQeiCKuGscBi3Qn9bBHuF4mtZamEdNl1uDq4/s640/13_Final.jpg" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">With that the setup of active secondary on sql azure database is complete. Pretty simple as it takes a few clicks and needs almost zero pre requisites. A similar setup on "on Premises" would take days to months in most places.</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-size: large;"><span style="font-family: "georgia";">That's a lengthy first post on SQL Azure - Active Secondary. </span><span style="font-family: "georgia" , "times new roman" , serif;"> On subsequent posts, I will cover how to connect to primary and secondary, failover , monitoring and how applications can benefit via readable secondary and of course the cost involved as well.</span></span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
</div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com1tag:blogger.com,1999:blog-1619266374223964354.post-71726919407503856152017-05-04T18:10:00.002-07:002017-05-04T18:10:50.547-07:00Data Channel Interview - 07 - Steve Knutson on Managing Sharepoint Databases <div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="text_exposed_root text_exposed" id="id_590bd00335dba7796418445">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">7th Interview of </span><a class="_58cn" data-ft="{"tn":"*N","type":104}" href="https://www.facebook.com/hashtag/datachannel?source=feed_text&story_id=10154922677493113"><span class="_5afx"><span style="font-family: Georgia, "Times New Roman", serif;"><span style="font-size: large;"><span aria-label="hashtag" class="_58cl _5afz">#</span><span class="_58cm">DataChannel</span></span></span></span></a><span style="font-family: Georgia, "Times New Roman", serif; font-size: large;"> is out <span class="_47e3 _5mfr" title="smile emoticon"><img alt="" aria-hidden="1" class="img" height="16" src="https://static.xx.fbcdn.net/images/emoji.php/v8/fa5/1.5/16/1f642.png" width="16" /><span aria-hidden="1" class="_7oe">:)</span></span> </span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;"></span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Very happy to have Mr.Steve Knutson, office servers and SharePoint MVP, share his knowledge on "Managing SharePoint Dat</span><span class="text_exposed_show"><span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">abases" on #DataChannel. Steve, demystifies quite a few items for SQL Server DBAs managing SharePoint databases like "what are the do's and don’ts for SharePoint databases", "Why are SharePoint DBs huge", "How to Size SharePoint Databases", "What are RBS / File Streams / Blobs on SharePoint Databases" and many more. </span></span><br />
<span style="font-family: Georgia; font-size: large;"></span><br />
<span style="font-family: Georgia; font-size: large;">Steve is to the point on his answers and in my opinion, the interview is very useful for database administrators managing SharePoint Databases. </span><span class="text_exposed_show"><span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Happy Watching!!!</span> </span><br />
<span class="text_exposed_show"></span><br />
<span class="text_exposed_show"></span> </div>
</div>
<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/RUNACMhM9AM" width="560"></iframe><br /></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-16200130611787108142017-04-30T09:32:00.000-07:002017-04-30T09:32:00.389-07:00Data Channel Interview - 06 - Manohar Punna on SQL Azure Migrations<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Here we go for the 6th interview on </span><a class="_58cn" data-ft="{"tn":"*N","type":104}" href="https://www.facebook.com/hashtag/datachannel?source=feed_text"><span class="highlightNode"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">#DataChannel</span></span></a><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">. Manohar Punna, MVP and <span class="highlightNode">Data</span> Platform Geeks President discusses "Migrations to SQL Azure" on <span class="highlightNode">#DataChannel</span>. Manohar, in this interview provides valuable inputs on migrating to SQL Azure, challenges involved and methods to monitor and size SQL Azure databases. Happy watching !!!</span><br />
</div>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/NGGss2jZjpM" width="560"></iframe><br /></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-23171484077259017082017-04-13T18:24:00.000-07:002017-04-13T19:49:34.701-07:00SQL Saturday Christchurch 2017<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Dear All,</span></div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> Last week, I had travelled to the beautiful city of
Christchurch, for speaking in SQL Saturday South Island. This is by far the
farthest I have travelled for a SQL Saturday, nearly 8500KMs from Singapore. I
am so glad, I made it as it turned out to be a excellent event organized by my
dear friend / brother Hamish Watson, Martin Catherall and rest of the SQL
Saturday South Island team. </span></div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">My talk was on "Cardinality estimator - A ride from SQL
7.0 to SQL Server 2016", going thru the various changes on CE 120 and CE
130. Personally, I enjoyed delivering this one as the audience was fairly well
informed and it gave me the license to get lot more detailed on the subject. No
one left the room since I started the talk and it was a very interactive
session, which gave me a feel that the talk was well received too.</span></div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">After my session, I managed to interview couple of wonderful
MVPs, Mr. Manohar Punna and Mr. Steve Knutson for #DataChannel. Both the
interviews were on my editing table and will be out in few weeks’ time :)</span></div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">The Oz/NZ SQL family is so warm, welcoming and take so much
of care, that it is always a pleasure being part of their events. I always have
a ball of a time in company of Hamish, Martin Catherall, Manu, Warwick, Martin
Cairney, Rob Douglas, Patrick Flynn, Reza and Leila (though they had to leave a
little early this time around). Thanks for making me feel part of the family as
always.</span><br />
<span style="font-family: "georgia"; font-size: large;"></span> </div>
<div style="margin: 0in 0in 10pt;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg25aJcTeji-O8LkmSLBtIMrd7K0mYaRznEx8Yef8zcnYLyNy0frGqj4DMRwmN1mYxPHokH1XSziAYAwMHeb0ZvjmsEfMIrYbf3NiAAfnQsqBuSTNOa8bwkwk3VgzttLnAUzk9ak8qD_Qs/s1600/bbq.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg25aJcTeji-O8LkmSLBtIMrd7K0mYaRznEx8Yef8zcnYLyNy0frGqj4DMRwmN1mYxPHokH1XSziAYAwMHeb0ZvjmsEfMIrYbf3NiAAfnQsqBuSTNOa8bwkwk3VgzttLnAUzk9ak8qD_Qs/s400/bbq.jpg" width="400" /></a></div>
</div>
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">One of the main reasons for me deciding to take part in this
event was beacuse of Mr.Hamish Watson, who invited me way back in Aug 2016
itself. While he calls me "SQL Brother", he took care of me
like his real brother, taking me around the beautiful city of Christchurch,
taking me to his home, showing me his farm as well. I got a chance to meet his
wonderful family, his wife and 4 lovely kids and I really wish them all the
happiness in this world. Hamish also cooked delightful vegetarian dinner for
the entire family, making the overall stay at his home memorable. I really
don't think I could play such a good host as him and I had lots to nice things
to learn from him and his family. </span></div>
<br />
<div style="margin: 0in 0in 10pt;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisaHgpnw1TmQx7VQ88up0bOSQq9Dn_IRihHDAVkFezqPRPfFjKJKgy-ig8X5Cr9knRJ4NC-6BYu2hfLukCkrQFsVhxdQCU8CF2epco1CzdELz7G3uubqgJNW4TBUdSrzMkXEsEqQqIEXk/s1600/20170409_185442.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisaHgpnw1TmQx7VQ88up0bOSQq9Dn_IRihHDAVkFezqPRPfFjKJKgy-ig8X5Cr9knRJ4NC-6BYu2hfLukCkrQFsVhxdQCU8CF2epco1CzdELz7G3uubqgJNW4TBUdSrzMkXEsEqQqIEXk/s400/20170409_185442.jpg" width="400" /></a></div>
<br /></div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Finally, I also did the tourist thing of sightseeing and
shopping in Christchurch. After travelling around a few countries and a number of
cities, I can certainly say, Christchurch is one of the most beautiful cities I
have ever seen. The mountains, the ocean, the beach and the country side are so
scenic. Sadly, multiple earthquakes in last few years have caused loss of
several lives and properties but nothing stops the New Zealanders from being
happy and keeping the city neat, clean and beautiful. </span><br />
<br />
<span style="font-family: "georgia"; font-size: large;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv71oFanaMFUJxUrMwd7c8VCuyIIwJvRiCHOeMW4Khyphenhyphen66H2vSmBK4_S326A8L9DGCa7dkscrVkXoU6H9rUi4I2edcjX_GjN924SAI9l1FWl3W_tXETcjR4nnj4e4nJ7QlconIqTd3pIqk/s1600/20170410_134532.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv71oFanaMFUJxUrMwd7c8VCuyIIwJvRiCHOeMW4Khyphenhyphen66H2vSmBK4_S326A8L9DGCa7dkscrVkXoU6H9rUi4I2edcjX_GjN924SAI9l1FWl3W_tXETcjR4nnj4e4nJ7QlconIqTd3pIqk/s400/20170410_134532.jpg" width="400" /></a></div>
<span style="font-family: "georgia"; font-size: large;"></span> </div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> I managed to visit a place called Gondula which is a
location on Mount cavendish, at about thousand feet above sea level, from where
one gets an aerial view to the city & harbor at one side and calm
endless pacific ocean at the other side. Needless to say, the views were simply
stunning and I was awestruck to see how nature has blessed this place with so
much of beauty. Gondula is one of the two stunning locations I have ever been
too (the other being the great oceans road / 12 apostles in Australia). </span><br />
<span style="font-family: "georgia"; font-size: large;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglk5cZiZC_ecgFGEk9dI9LbEFKA6hrhxkP0Ql6SnNpDT1qogr9-ImVD0uyxcTm2sUtgWHKFbr_gvQPlemvYCiEYM5kQl1qBP4EnA_j4UnkYuZncT9NqhIT12abub6h5730I_MEhD2l3Tg/s1600/20170410_144133.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglk5cZiZC_ecgFGEk9dI9LbEFKA6hrhxkP0Ql6SnNpDT1qogr9-ImVD0uyxcTm2sUtgWHKFbr_gvQPlemvYCiEYM5kQl1qBP4EnA_j4UnkYuZncT9NqhIT12abub6h5730I_MEhD2l3Tg/s400/20170410_144133.jpg" width="400" /></a></div>
</div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"> Overall, a memorable trip both professionally and
personally, thanks again to Hamish Watson for giving me an opportunity to be
part of this excellent event!!! </span></div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">if interested, please check out more photos <a href="https://www.facebook.com/nagaraj.venkatesan.3/media_set?set=a.10154863885698113.639453112&type=3">here</a></span></div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Cheers,</span></div>
<br />
<div style="margin: 0in 0in 10pt;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Nagaraj</span></div>
<b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><span style="font-family: "georgia" , "times new roman" , serif;"></span><span style="font-size: large;"></span><br /></div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com6tag:blogger.com,1999:blog-1619266374223964354.post-24839515985247382522017-04-12T16:17:00.000-07:002017-04-12T16:17:02.779-07:00Data Channel Interview - 05 - Edwin Sarimento on Availablity Groups & High Availability<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Dear all,</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span> <span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">On the 5th interview of #DataChannel, Mr.Edwin Sarimento, MVP, MCM and a popular high availability expert discusses SQL Server Always on Availability Groups and High Availability options in SQL Server. In this interview, Edwin gives a totally new dimension to common perceptions on high availability. </span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span> <span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span> <span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Sharing a few top lines from Edwin in the interview!!!</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br />
</span> <br />
<ol style="text-align: left;"><li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Keeping it simple is the most difficult thing to do!!!</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Don't do something just because its the latest. Always ensure it solves the business problem</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Deprecated doesn't mean not supported</span><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike></li>
</ol><div><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Watch the full interview below and enjoy!!!</span></div><div><span style="font-family: "georgia"; font-size: large;"><br />
</span></div><div><span style="font-family: "georgia"; font-size: large;"><br />
</span></div><div><span style="font-size: large;"></span><span style="font-family: "georgia" , "times new roman" , serif;"></span><br />
</div></div><iframe width="560" height="315" src="https://www.youtube.com/embed/_r6YKu_8tyo" frameborder="0" allowfullscreen></iframe><br />
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com0tag:blogger.com,1999:blog-1619266374223964354.post-60161305031099760412017-03-27T21:10:00.000-07:002017-03-27T21:10:24.920-07:00Data Channel - Interview 04 - Martin Catherall on In Memory OLTP<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Welcome to the 4th Interview of Data Channel. </span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">We have Mr.Martin Catherall, 3 time Data Platform MVP from Australia / New Zealand discussing "In Memory OLTP" tables. Interview was shot at his wonderful home in Melbourne. I would like to thank Martin for sharing his knowledge for the SQL Community. Check out the interview below.</span><br />
<span style="font-family: "georgia"; font-size: large;"></span><br />
</div>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/py1NC8iRrzM" width="560"></iframe> </div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com1tag:blogger.com,1999:blog-1619266374223964354.post-24885286958566571682017-01-22T08:32:00.002-08:002017-01-22T08:34:10.760-08:00MVP 2016!!!<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Dear All,</span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;"></span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">Very happy to share the news that I have been awarded Microsoft Most Valuable Professional award for my contributions to SQL Server Community in 2016!!!. This is an honor I have dreamt of for long needless to say that the award motivates me to contribute more for the SQL Server Community.</span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;"></span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">In case if you don't know what MVP award is all about, you may read about it </span><a href="https://en.wikipedia.org/wiki/Microsoft_Most_Valuable_Professional"><span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">here</span></a><span style="font-family: Georgia, "Times New Roman", serif; font-size: large;"> and </span><a href="http://www.wikihow.com/Become-a-Microsoft-MVP"><span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">here</span></a><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;"></span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">No big achievement is possible without a team. This one is no different. My family, Singapore Microsoft Data Platform Team, MVPs who nominated me, Microsoft DX Team, my friends at Singapore SQL Server User Group, colleagues at work and several experts from whom I have learnt the tricks of the trade..it is a long list of people who have helped me get here!!! I have just collected the award on behalf of everyone :)</span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;"></span><br />
<span style="font-family: Georgia, "Times New Roman", serif; font-size: large;">I received the award few days ago shipped from Microsoft. Sharing the pictures of the same below. Thanks to Microsoft again for giving me something I would cherish forever!!!!</span> <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzfHUNIlsYn_z4suAEezxrbejaXktlnj6EowsT07Kqp7v3GfgxnCGm9ishSEZYHa3ntCVY7uOVBQbiULF0Z05lOhMpQREdI1Xi_zo-FMLREoI0Mv-5gmhHkoQ3J5dTR59XGrG0ifdogSA/s1600/20170118_220334.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzfHUNIlsYn_z4suAEezxrbejaXktlnj6EowsT07Kqp7v3GfgxnCGm9ishSEZYHa3ntCVY7uOVBQbiULF0Z05lOhMpQREdI1Xi_zo-FMLREoI0Mv-5gmhHkoQ3J5dTR59XGrG0ifdogSA/s400/20170118_220334.jpg" width="300" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivEN0ll5FnWDuiLFLa4H7dj6mnbB3QDxK6iL442coVl6pnnVXdyltHVDMg2mD-v6btQzasNzS4JSOFjc2Q11gikkRHPs3qQiuBT1oLZo14Av1GXcCYEwxgXc2UAX5R7PBM4A6AGvlAmdQ/s1600/20170118_220817_001.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivEN0ll5FnWDuiLFLa4H7dj6mnbB3QDxK6iL442coVl6pnnVXdyltHVDMg2mD-v6btQzasNzS4JSOFjc2Q11gikkRHPs3qQiuBT1oLZo14Av1GXcCYEwxgXc2UAX5R7PBM4A6AGvlAmdQ/s400/20170118_220817_001.jpg" width="400" /></a></div>
</div>
Nagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.com3