https://youtu.be/SAzmOEqVoIQ Another one of the database options which doesn’t serve any real purpose. As shown in this video the setting is overridden by session level settings so it doesn’t make sense to set it at Database level. This option default the NULLAbility of columns when creating the table or altering it. When set to off…
Category: Azure
A list of Blogs containing tips and Tricks for Microsoft Azure.
DB Options: – ANSI NULL DEFAULT ON
https://youtu.be/SAzmOEqVoIQ Another one of the database options which doesn’t serve any real purpose. As shown in this video the setting is overridden by session level settings so it doesn’t make sense to set it at Database level. This option default the NULLAbility of columns when creating the table or altering it. When set to off…
DB Options: Auto Create Incremental Statistics ON
In this video we cover the next database option which is AUTO CREATE INCREMENTAL STATISTICS. This option is useful when the database has partitioned tables. It allows the DBA to update stats by merging the updated stats for the partition in question with existing stats. In this way DBA are able to reduce the overhead…
DB Options: Auto Create Stats and why it should be ON
Auto create stats is one of those database options that every DBA knows they should set to ON. In this video we explore what happens when you set if OFF and why having good stats is important for query optimizations. We explore the impact of not having statistics on a simple query and then cover…
Database Options – Auto Close and why should set it to OFF
Auto close is one of those features that should have been removed long back. The setting needs to stay at default of OFF for reasons that will be made obvious in this video. In this series of videos we cover each of the database options what they do and why they should be set to…
Using Bookmarks in PowerBI
https://www.youtube.com/watch?v=SftUYP6nchk PowerBI is a robust reporting tool that has a number of easy to use and configurable options when it comes to reporting. One of the most frequently used features in PowerBI is the bookmarks capability. A bookmark as its name suggest provides way to snapshot a report page keeping in mind the filters and…
SQL 2019 – Christmasql
Early November I had posted I would be conducting a series of sessions on MS SQL server. The response was really great and while I intended to do a classroom session the interest was primarily for online delivery. So I will be arranging the first half of it this in the coming 10 days. Here…
SQL 2019-Data Classification and Vulnerability assessment
Data Classification With GDPR security has become a high priority for a large number of organizations the cost of not implementing robust security has been significantly increased and therefore companies that previously used to think security wasn’t a key part of their architecture now has to revisit that approach. But complying with GDPR and all…
Generating TPC H data for testing
A common requirement I have is to generate test data. Sometimes for trainings and sometimes for consulting work. As you may have seen from many previous video and blogs typically use Airlines Ontime dataset for large databases and Mockaroo for smaller datasets. However recently I wanted to try and see if I could achieve comparable…
Using Bookmarks in PowerBI
PowerBI is a robust reporting tool that has a number of easy to use and configurable options when it comes to reporting. One of the most frequently used features in PowerBI is the bookmarks capability. A bookmark as its name suggest provides way to snapshot a report page keeping in mind the filters and customizations…
Error on WebAPI after deployment to Azure App Services
Encountered this below error today while deploying a WebApI to Azure App Services. Initially it wasn’t very clear what the error was about so we checked the bin folder for the below assembly but couldn’t find it. Since the API was working locally the answer had to be a configuration change from Dev to Prod…
Online Indexes Failures in Azure for VLDBs
Recently we had a requirement that was caused by a very unique situation. A table with a column of datatype nvarchar(max) was inadvertently populated with some text in excess of 20MB of data/row. This resulted in the table blowing up from around 3GB to over 250GB and later 350GB. Naturally the sudden surge in size…
Query to find size of tables in Azure database
Quick and dirty way to find the size of all tables in a azure database. DROP TABLE #list DROP TABLE #data GO CREATE TABLE #data ( name VARCHAR(100) ,rows VARCHAR(100) ,reserved VARCHAR(100) ,datasize VARCHAR(100) ,indexsize VARCHAR(100) ,unused VARCHAR(100) ) SELECT ‘ insert into #data exec sp_spaceused ”’ + table_schema + ‘.’ + table_name + ””…
The-certificate-chain-was-issued-by-an-authority-that-is-not-trusted-when-conn -FIXED
Interesting little problem came up today when migrating a database from Azure SQL database to local server. The connections sting in the web app had the following property set Encrypt=True; TrustServerCertificate=False The combination of the two keywords ensures that data transfer from Website to Database server is encrypted using a certificate generated by the…
Azure Blob Storage usage scenarios for Archival
Azure blob storage is a great way to store large amounts of data. It provides cheap highly available access to data anytime, anywhere. However the nature of data often changes and as a result there are three common methods or access tiers under which the data is stored in Azure. Developers are encouraged to consider…