The good news is that Power Query in Power BI is flexible enough for us to take advantage of the Bing Maps API so that we can lookup the missing pieces of information we need. In this blog post, I’m going to show you how you can use the Bing Maps API to look up an address based on a latitude and longitude or use a street address to find a latitude and longitude for the location.
I love how map visualizations nowadays are so easy.
In SQL Server 2016 there are a lot of goodies under Compatibility Level 130, such as performance improvements with Columnstore and In-Memory OLTP and all the Query Optimizer enhancements done over the years under Trace Flag 4199. So we are faced with a situation where there are workloads that can benefit from some enhancements that come with Compatibility Level 130, but that cannot use the new CE.
I’ve had a very positive experience with the new cardinality estimator, but I certainly appreciate the option being there just in case.
While it was understandable that initially Power BI wanted to focus on visualizations first and foremost, the display for pure tabular data in Power BI was lackluster. There was no control over any of the following:
Colors –of any element in a table, font, background color, title, totals. Nada
I am simultaneously impressed that the Power BI team can so consistently push out crazy numbers of updates and glad that I don’t personally need to keep up on all of the changes…
Apparently, in PBI the domain is used, not the email. Good to know if row level security is implemented in PBI someday (note: currently it’s only available in the Power BI Service).
OK, now let’s upload this report to the Power BI Service so we can implement the RLS. When the report is opened, it seems that my carefully crafted measures are ignored. Maybe Power BI ignores the USERNAME() function as long as there is no RLS defined.
I like the fact that you can test by role, making it a lot easier to see what others would see under those circumstances and finding bugs with security implementation. If you have any non-public information and you use Power BI, this is a must-read.
There are three tokens within the T-SQL (highlighted in yellow above): A-MSG, DATE, and TIME. SQL server replaces these three tokens as follows:
- A-MSG: Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.
- DATE: Current date (in YYYYMMDD format).
- TIME: Current time (in HHMMSS format).
See the MSDN documentation for a list of tokens and their descriptions.
This is a great way of being smarter with alerts. Your SQL Server instance has a lot of information at the ready, so get familiar with what’s up for offer.
If “shrink that database” makes you cringe, that’s a good sign. Nevertheless, sometimes it has to happen. On that rare occasion in which it needs to happen, Andy Mallon gives you a how-to guide:
Perhaps you just implemented data compression, and have 60% free space in your database.
Perhaps you just deleted a bunch of old data, and have significant free space in your database.
There are a handful of scenarios where you have free space in the database, you’ll never use it, and you need to shrink. Just don’t make a habit of it.
Shrink should not be part of your normal routine, but it does come in handy sometimes. Auto-shrink, though? Burn that to the ground.
Install the cumulative update into a non-production environment (prod test or staging) and run application activity against it.
Ideally, you want this to be representative activity for your real, production applications, run from multiple application servers. You also want the hardware and storage to be as similar as possible.
If that’s not possible for you to set up, you need some sort of tool to generate concurrent activity. There are a lot of options — Michael Swart gives a great run down here.
This is some very good advice, and boils down to “test everything that you do in production.” It’s easy to forget some of these things, though, so I’m glad Kendra enumerated them.
Slava Murygin walks through running SQL Server on Windows Server core. First, the steps for installing SQL Server at the command line:
There are three reasons why Windows Server with Core is better than Server with a GUI:
1. Security: Core has less features. That means less surface for intruders to attack.
2. Management: No unnecessary updates and planned reboots.
3. Resource consumption: Less memory usage and less CPU overhead
Even if you aren’t using Windows Server Core, it’s a good idea to be able to script out your SQL Server installation so you can automate it later (or at least so you don’t forget options and have some servers look different than others).
The following error occurred:
You have selected a feature that is not supported on Windows Server Core.
The supported features on Windows Server Core are:
Database Engine Services,
SQL Server Replication,
Full-Text and Semantic Extractions for Search,
Client Tools Connectivity,
and SQL Client Connectivity SDK.
That list is a bit more limiting than I’d like, to be honest.
Scripting SQL Server objects manually through the SQL Server Management Studio can be a tedious task. Last week I published a script to export database objects with PowerShell. I wanted to take this a little further and create a solution to export SQL Server objects as well.
Because this would be a nice addition to the PSSQLLibmodule, this function is also included in the library from today.
I haven’t tried using this cmdlet yet, but it does look handy.
There are purposes for credentials other than a proxy, but for our purposes you are just going to enter an AD username and password. Just to be even more clear, this is an AD/Windows user. Not a sql server login.
In Object Explorer: ServerName -> Security -> Right click on Credentials and select New Credential -> Fill in the Name, Identity and Password fields.
Kenneth’s getting fancy with animated GIFs, and gives us a good walkthrough of this aspect of SQL Agent security.