Happy New Year 2019

Wishing you all a Happy New Year 2019.

Thank you for reading my blog. See you in 2019

Get the November update of PowerBI Desktop

You can easily download PowerBI Desktop from Microsoft Store and launch it within minutes.

If you never had PowerBI, you may need to fill up this form.

 You have two options.

 This comes up first but you can ignore it. It has lots of good information and links to videos if you are new.

Here is the working screen of the app.

 You can get the version number from here using About.

This is the November version.

New feature list from Microsoft Site:

Reporting

• Expand & collapse matrix row headers
• Copy & paste between Desktop files
• Updated filtering experience (preview)
• Report accessibility improvements

Analytics

• Color saturation on visuals upgraded to use conditional formatting
• Related questions in the Q&A explorer

Modeling

• New modeling view (preview)
• Composite models now generally available
• Modeling accessibility improvements
• New DAX functions
  • Optional DrilldownFilter argument for the RollupAddIsSubtotal function
  • NonVisual function
  • IsInScope function

Custom visuals

• Calendar by MAQ Software
• Ratings by MAQ Software
• Hourglass Chart by MAQ Software
• Forecast Using Multiple Models by MAQ Software
• Pie Charts Tree

Data connectivity

• Azure DevOps Server connector
• PDF Connector support for Start Page and End Page parameters
• Improved Azure Consumption Insights connector

Here is a video summarizing features:

SQL Server 2019 is in Public Preview now

Users as well as enterprises will be looking more and more at security and compliance features and SQL Server 2019 in Public Preview is just the thing they would love. Of course, it has the usual features of mission-critical availability; industry-leading performance and advanced analytics. Of course, you have to keep in mind that Power BI has become a part and parcel of SQL Server.

This is how Microsoft introduces SQL Server 2019:
"
Transform your business with a unified data platform. SQL Server 2019 comes with Apache Spark and Hadoop Distributed File System (HDFS) for intelligence over all your data.
"

Here are some highlighted description:

Intelligence over any data:
'SQL Server is a hub for data integration. Deliver transformational insights over structured and unstructured data with the power of SQL Server and Spark.'

Choice of language and platform:
'Build modern applications with innovative features using your choice of language and platform. Now on Windows, Linux, and containers.
'
Industry-leading performance:
'Take advantage of breakthrough scalability, performance, and availability for mission-critical, intelligent applications, data warehouses, and data lakes.
'
Advanced security features:
'Protect data at rest and in use. SQL Server has been the least vulnerable database over the last 8 years in the NIST vulnerabilities database.
'
Make faster, better decisions:
'Power BI Report Server gives your users access to rich, interactive Power BI reports, and the enterprise reporting capabilities of SQL Server Reporting Services.
'

You can read more about it here:
https://www.microsoft.com/en-us/sql-server/sql-server-2019

Tracking Report Servers with rskeymgmt tool

You could use the command rskeymgmt

Here are the things you can find out about Report Servers from command lineusing HELP:

-----------------------

C:\Users\TEMP.HODENTEK9.000>rskeymgmt -?
Microsoft (R) Reporting Services Key Manager
Version 13.0.1601.5 x86

Performs key management operations on a local report server.

  -e  extract           Extracts a key from a report server instance
  -a  apply             Applies a key to a report server instance
  -s  reencrypt         Generates a new key and reencrypts all encrypted
                        content
  -d  delete content    Deletes all encrypted content from a report server
                        database
  -l  list              Lists the report servers announced in the report server
                        database
  -r  installation ID   Remove the key for the specified installation ID
  -j  join              Join a remote instance of report server to the
                        scale-out deployment of the local instance
  -i  instance          Server instance to which operation is applied;
                        default is MSSQLSERVER
  -f  file              Full path and file name to read/write key.
  -p  password          Password used to encrypt or decrypt key.
  -m  machine name      Name of the remote machine to join to the
                        scale-out deployment
  -n  instance name     Name of the remote machine instance to join to the
                        scale-out deployment
  -u  user name         User name of an administrator on the machine to join to
                        the scale-out deployment.  If not supplied, the current
                        user is used.
  -v  password          Password of an administrator on the machine to join to
                        the scale-out deployment
  -t  trace             Include trace information in error message

To create a back-up copy of the report server encryption key:
RSKeyMgmt -e [-i ] -f -p

To restore a back-up copy of the report server encryption key:
RSKeyMgmt -a [-i ] -f -p

To reencrypt secure information using a new key:
RSKeyMgmt -s [-i ]

To reset the report server encryption key and delete all encrypted content:
RSKeyMgmt -d [-i ]

To list the announced report servers in the report server database:
RSKeyMgmt -l [-i ]

To remove a specific installation from a scale-out deployment:
RSKeyMgmt -r [-i ]

To join a remote machine to the same scale-out deployment as the local machine:
RSKeyMgmt -j [-i ] -m
          [-n ] [-u -v ]

C:\Users\TEMP.HODENTEK9.000>

------------------------------

I have three report servers but two of them have a problem.

If you want to use rskeymgmt you should start the CMD program with elevated privileges as shown:

Once CMD screen is displayed you can find the report servers as shown. I am using the l and i flags in rskeymgmt. The SQL Server Report Server 2012 PCATT has no problem, while the 2016 and 2017 SQL Server instances OHANA and SSRS are showing the same exception.

C:\>rskeymgmt -l -i PCATT   ---SQL Server 2012
HODENTEK9\PCATT - 879ea471-47cf-4386-b7f1-6eb213a5fff6
The command completed successfully

C:\>rskeymgmt -l -i OHANA     ---SQL Server 2016
The profile for the user is a temporary profile. (Exception from HRESULT: 0x80090024)

C:\>rskeymgmt -l -i SSRS      --default instance of SQL Server 2017
The profile for the user is a temporary profile. (Exception from HRESULT: 0x80090024)

The previous result was obtained using a Preview Build of OS that was not working well.

Recently 9/14/2018 there was an update to the OS and things have improved. Here are some new results for the same. 

Two of them are not displaying results, in one case the Database Engine is not running and in the other the Report Server is not running.

Install the latest version of SQL Operations Studio (0.31.4)

I was using SQL Operations Studio (SQLOPS) Ver 0.26.7 and I am upgrading to the July 2018 Public Preview version 0.31.4.

It is Available on Windows, macOS and Linux and you can download from here:
https://docs.microsoft.com/en-us/sql/sql-operations-studio/download?view=sql-server-2017


Installation procedute for all the OS's available on the above site.

This is the file for Windows OS:
sqlops-windows-setup-0.31.4.exe (74.3 MB)

Double click the executable to launch the application.
Here are the installation screen shots.

Accept license terms.

 The program is added to the PATH and you can launch soon after installing.

I connected to the SQL Server 2016 named instance after launching.

In the Preview you can see a number extensions that you will be able to access.

SQL Server Reporting Services 2016 - Modes of Operation

The choices have not changed from previous versions. Your choice would depend on what you want to do. The resources needed will also influence your decision. For Example, the SharePoint mode would imply that you are running SharePoint Server or Services.

During SQL Server 2016 Installation you can choose to specify Reporting Services Configuration mode (review the screen shot).

There are two modes that you can specify Reporting Services Configuration:

Reporting Services Native Mode
Reporting Services SharePoint Integrated Mode

You can choose both modes or just one mode.

If you choose to install the Native mode there are two options:

Install and Configure
Install Only

Install and Configure option installs and configures the report server in Native Mode. However you need to do further configuration for your specific case. The Report Server is operaitonal with the Windows Service and the databases needed by Reporting Services in your named instance.

Install only installs the report server files. You need to use the RS Configuration Manager to compele configuration.

If you choose to install the SharePoint Integrated Mode you can only install the Report Server files but you will need SharePoint Central adminsitration to complete configuration.

Details for both modes of installation for SQL Server 2012 were described in my book:

ISBN 139781849689922

Paperback566 pages

Querying SQL Server 2017 Version 17.3 using SQLOPS

 There are already system databases. Make sure that the server has started. Start it if it has not started in Control Panel, or otherwise.

I have a default instance of SQL Server 2017 Verion 17.3 and I connect to it as described here.

Launch SQLOPS from its shortcut and click New Connection as shown

SQLServer17SQLOPS_0

The New Connection window opens as shown.

SQLServer17SQLOPS_1

Enter the items as shown.

SQLServer17SQLOPS_3

Click Connect.

You are connected as shown.

Click New Query to open a new query window as shown.

SQLServer17SQLOPS_6

Type S and a drop-down list appears.

SQLServer17SQLOPS_7

Complete a query as shown:

SELECT @@version

SQLServer17SQLOPS_8

Click Run and review the result as shown:

SQLServer17SQLOPS_9

Right click the result, copy and paste it in Notepad as shown:

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)  Aug 22 2017 17:04:49  Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro Insider Preview 10.0 (Build 17025: ) (Hypervisor)

Note: It was mentioned that you type in msdb as a database. In fact just localhost is all that is neccessary to connect.