Category Archives: Tech Notes

Microsoft SQL Server Developer Edition is now free

https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

“Exciting news! Starting today, SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.”

“SQL Server 2016 Developer Edition, when released later this year, will also be free. To learn more about the exciting new features in SQL Server 2016, read the datasheet.”

Create Read Only User in Azure DB

I have been struggling with creating a user who has read only access to an azure database and finally figured it out.  The key is connecting to the right database in a new session when executing the t-sql commands.  This is because you can not execute a “use” statement, change the database in the current session via the GUI, and you can not make named references to databases in your t-sql.

server_name.database_name.schema_name.object_name

Validate user has read only permissions by connecting to database using these new credentials and executing something like “CREATE TABLE dbo.test (test datetime);”, which should return an error message indicating permission denied.

More information on managing database access and login security in azure: https://azure.microsoft.com/en-us/documentation/articles/sql-database-manage-logins/

WiFi Range Heat Mapping

I wanted to compare WiFi coverage of two different wireless access points and their various configurations in my house, and also check for dead spots.  One quick and cheap way to do this that I have come across is using a free product called Ekahau HeatMapper.

Ekahau HeatMapper allows you to walk around your home or office and capture information about WiFi signals in various spots, and then the software aggregates this information into a banded topographical map.

Continue reading WiFi Range Heat Mapping

Publish Google Analytics Reports to the Web using Power BI

There is a feature in Power BI which allows you to connect to your google analytics data, and there is a new preview feature which allows publishing content to the web in an iframe.  In this post I have combined both features together to display my website statistics to the web.

  1. Connect to Google Analytics: https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-google-analytics/
  2. Publish to Web: https://powerbi.microsoft.com/en-us/blog/announcing-power-bi-publish-to-web/
Here is the result:
(View Larger)


Note: PowerBI has trouble rendering in Chrome in some cases.  If you are using Chrome and can not see the dashboard then try another browser.  Hopefully Microsoft will resolve this problem.

Video: Business Intelligence with Power BI

I’ve been catching myself up on what Microsoft is doing lately regarding Business Intelligence.  After spending days reading Microsoft documentation and testing some various configurations of Power BI, Gateway,Azure, and SSRS, I came across this video that goes over some history and then gets right into Power BI and some competing tools.  I suspect Microsoft is working toward merging Power BI and SSRS based on some of their SQL Server public previews.

Get Database Sizes in PestgreSQL

Returns the database name, owner, and size on a given server.  Tested in PostgrSQL 9.2 and 9.3.

Source: https://wiki.postgresql.org/wiki/Disk_Usage

SQL Server Job to Capture Reads

We needed to capture physical reads over time from our SQL server.  One method we decided to deploy was to check sys.dm_exec_query_stats periodically and capture the differences between each check.  The way I am doing it below is not 100% accurate because sys.dm_exec_query_stats is based on cached plans and the server will let clear the plan data over time.  This means that when the differences are calculated any plan that was cleared from cache would not be included in the current check, but would still be in the comparison record.  This could be solved by storing and comparing at the plan level but for my purposes I prefer to store summary information and do not mind this issue because I am running my comparison every hour and then will be averaging the results over time, and the result does not need to be exactly precise, just close enough to give us a general understanding.

This is the query inside the job:

This is what the table looks like with a few records.  Note that this is a result of running the job multiple times with a few seconds between each interval.

SQL Server Job to Capture Reads result

Disable then Enable SQL Agent Jobs

This script has three actions:
1) Put Current Job Information Into Temp Table
2) DISABLE All SQL Server Agent Jobs
3) ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
Note: If you need to disconnect from the server after step 1 then step 3 will not be able to read from the temp table. Instead, you would have to feed a list of job IDs to the query. I have created a place for this just in case this is what you have to do.

Test Results:
Put Current Job Information Into Temp Table: 47 row(s) affected
DISABLE All SQL Server Agent Jobs: 33 row(s) affected
ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: 33 row(s) affected