Database diagramming in SQL Server 2016

Database diagram is a diagram that consists of a number of tables displayed on it with or without showing the relationships between the tables. It can also be edited for logical relationships; get a print out of database structures, etc.

Here are steps to create database diagram in SQL Server 2016 for the AdventureWorks2014 database.

Click Database Diagrams under AdventureWorks 2014 which is presently empty as shown:

InstallDBDiagSup_00

The top two items in the drop-down menu are

New Database Diagram
Install Database Diagram Support

Click on Install Database Diagram support. You get a message about objects missing that support diagram support.

InstallDBDiagSup_01
Click Yes to go ahead and create objects to support diagramming. Nothing happens after you click Yes.

Click the Database Diagram node one more time. Now you get only one option - New Database Diagram.

InstallDBDiagSup_02

Click New Database Diagram and a designer gets created bringing up a pick list of tables in the database as shown.

InstallDBDiagSup_03

With CTRL key held down you can select multiple tables as shown.

InstallDBDiagSup_04

After selecting table or tables click Add to add them to the diagram as shown.

InstallDBDiagSup_05

If you try to close the diagram you will be asked to save it as shown.

InstallDBDiagSup_06

Clicking Yes asks you for the name you want for this diagram. Provide a name and click OK.

InstallDBDiagSup_07

You get a Save message a shown. You can save it in SSMS or save it to a text tile.

InstallDBDiagSup_08

If you click Yes it gets saved to the server as shown in the Database Diagram node.

InstallDBDiagSup_09

Microsoft Message Analyzer

Microsoft Network Monitor has been replaced by Microsoft Message Analyzer that works with Windows 10.

This paragraph from Microsoft Documentation:
Microsoft Message Analyzer is a new tool for capturing, displaying, and analyzing protocol messaging traffic, events, and other system or application messages in network troubleshooting and other diagnostic scenarios. Message Analyzer also enables you to load, aggregate, and analyze data from log and saved trace files. It is the successor to Microsoft Network Monitor 3.4 and is a key component in the Protocol Engineering Framework (PEF) that was created by Microsoft to improve protocol design, development, implementation testing and verification, documentation, and support. With Message Analyzer, you can choose to capture local and remote traffic live or load archived message collections from multiple data sources simultaneously.

You can down load it from here:
After download run this file in the download
MessageAnaalyzer64.msi

MicrosoftMsgAnalyzer_02.jpg

Parsing a nested JSON object using PowerShell

Parsing a JSON object using PowerShell can give quick and fast results without too much coding and navigating objects and this is especially true in the case of nested JSON objects.

For example consider this example:
{
  "name": "donut",
  "image":
    {
    "fname": "donut.jpg",
    "w": 200,
    "h": 200
    },
  "thumbnail":
    {
    "fname": "donutThumb.jpg",
    "w": 32,
    "h": 32
    }
}
In order to parse this using PowerShell you formulate this in a Here string and use the ConverstFrom-JSON cmdlet. The rest is easy as you see in this:

JSONNested_01

Compare with this method using a stored procedure in SQL Server.

This next one appeared in StackOverflow.com. The question was, how to get to the value item of slots (which is req1) in the following JSON formmated string.

{"request": {
  "locale": "en-US",
  "timestamp": "2016-09-25T00:36:14Z",
  "type": {
    "name": "request",
    "slots": {
      "RequestTypeItem": {
        "name": "RequestTypeItem",
        "value": "req1"
      }
    }
  }
}
}

One of the answers using JavaScript can be found here. It is correct and gets you to the value as 'req1'.

However, PowerShell provides quite an elegant answer using the ConvertFrom-JSON cmdlet as shown here:

NestedJson_2.png

Read many more related PowerShell posts here.

Progress DataDirect delivers open connectivity to cater to all of your needs

Progress DataDirect uses leading industry standards ODBC, ADO.NET, OLE DB, JDBC or OData. It connects to database; to an application or to your cloud data.

DataDirect_01

Here is a schematic of DataDirect Connectivity.

DataDirect_02

In its own words,
"Deliver your own fully branded open connectors built on a codebase that is certified across third party applications such as Tableau, Excel, Qlik, Spotfire, Alteryx, SSIS, IBM Cognos, SAP Business Objects, Informatica PowerCenter, Salesforce and more. You can also get run-time installers with our SDK for easy distribution to your customers
"
Looks like nothing is left out!

These are in more details for each of connectivity categories:
Connectivity to databases:
NoSQL, NewSQL, Graph, Object, Document
Multi-dimensional, OLAP
Data Pipelines
Modelling Multi tenant databases
Private DB clouds

Connectivity with Applications/Business Logic:
logic layer for ERP, CRM, marketing or finance systems
Data service layers (expose a common data model)
4GL languages such as ABL
Web Services (SOAP/REST)
In-memory analytics model

File based data stores:
C-ISAM, COBOL, BTree, CTree, XML, JSON
Data archives
Financial transaction message files
Log files
EDI messages

Download a trial and experience the thrill from here:
https://www.progress.com/trial-datadirect

Using R in Windows 10 creates too many Local User Accounts

R programs were integrated with SQL Server 2016 for the first time. The R Server was installed when the SQL Server 2016 Developer Edition was installed. The shortcut in Windows 10 All apps show the details:

RWindows10

R GUI (x64) in the above was used (sometimes from R Server and sometimes from R Client)  to launch R. It has been used quite a number of times and what is intriguing is that each time it was used, the program has made an entry into the USERs node of the Local Users and Groups in the Computer Management window. If left without some sort of maintenance this could grow quite large.

RWindows10_1

One of the Local User Account OHANA01 created has the following property:

RWindows10_2

I am not usre if it is by design or a bug that creates these accounts. I will try to troubleshoot.

Looks like the above is by design according to an answer from SQL Server Forum:

I quote the answer here:
Looks like that is by design it self, please refer the BOL for sql R not for windows point of view.

As part of the installation process for R Services (In-database), a new Windows user account pool is created to support execution of tasks by the SQL Server Trusted Launchpad service. The purpose of these worker accounts is to isolate concurrent execution of R Scripts by different SQL users.

The number of user accounts in this pool determines how many R sessions can be active simultaneously. If the same user executes multiple R scripts concurrently, all the sessions run by that user will use the same worker account. As a consequence, a single user might have 100 different R scripts running concurrently as long as resources permit.

Starting SQL Server with minimum configuration

The option to start up Microsoft SQL Server when you have configuration problems is to use the -f start up option. With this option SQL Server start with a minimum configuration placing the server in single-user mode.

Be aware of the following in this mode:

• Only a single user can connect, and the CHECKPOINT process is not executed.
• Remote access and read-ahead are disabled.
• Startup stored procedures do not run.

Use the sqlcmd utility and the dedicated administrator connection to connect to SQL Server.

You can use the SC command line utility as you can use the start up parameters. SC is a command line program for communicating with the Service Control (SC) Manager and Services.

If you do not know how to use SC (Services Control Manager) review this post.

If you now start the SQL Server with a - f option the server with minimum configuration as shown:

I have a named instance of SQL Server 2016 called OHANA on my computer (Hodentek8) and I will now start it with minimum configuration by running the following. I have stopped this server before running the code:

 StartWithMin.png

 Verify with the Task Manager (there are other ways as well, Task Manager is open now).

StartWithMin_2

Starting and stopping a SQL Server using Services Control Manager

SC is a command line program for communicating with the Service Control (SC) Manager and Services. This can be used to start and stop a service including SQL Server.

Typing SC at command line (preferably with administrative privileges) will give you the details of this command.

SC_00

The syntax for using SC is:
        Service Control Manager and services.
USAGE:
        sc <server> [command] [service name] <option1> <option2>...

In this post we will just start and stop the SQL Server 2016 instance  and monitor it on the Task Manager.

The Service Name for the SQL Server Instance (OHANA) can be found by looking at the  details of SQL Server Instance (OHANA) in the Services window in Control Panel shown here:

SC_01

To start the SQL Server instance just type in the command

SC Start MSSQL$OHANA

SC_02

The Server has started. Bring up the Task Manager as shown and verify it has started. Note that the PID is the same.

SC_03

Now that the Server is running we can stop it by running the following command:

SC Stop MSSQL$OHANA

SC_04

Verify it has stopped in Task Manager.

SC_05

Starting and stopping using SC does not produce windows messages that you get while using Control Panel|..|Services. Using Task Manager you do not need to refresh window to see changes.

It is possible to use parameters to start a service as well.

Creating a simple DML trigger

An action is taken when a trigger is fired. DML stands for Data Manipulation Language and DML Triggers are initiated automatically whenever a DML event occurs.

DML events include events that takes place in a Table or View and they are:

INSERT
DELETE
UPDATE statements.

Why do you need a DML Trigger?

In order to enforce business rules in the following cases/situations:

Enforce Data Integrity
Querying other tables
Auditing data changes

These can include complex Transact SQL statements.

As triggers are created for (on) a table or view, a 'Triggers' node exist for all tables in a database.

Presently there are no triggers set up for the Person.PersonPhone table in the AdventureWorks 2014 database as its Triggers node is empty as shown.

DML_00

In order to create a trigger you need to provide a name, and the SQL Statement that starts the trigger; and the table on which the trigger will fire. Execute the following statement which creates a trigger called PlNotify that triggers whenever a Insert or an Update statement is run:

CREATE TRIGGER PlNotify 
ON Person.PersonPhone
AFTER INSERT, UPDATE  
AS RAISERROR ('Notify Customer Relations', 16, 10); 
GO

When the trigger is created it gets into the Triggers node as shown.

DML_01

Let us see what happens when we try to update a phone number. Here are some of the phone numbers (only 5 from top is shown):

DML_02

Now let us update a phone number in the above table using the following:

UPDATE dbo.Person.PersonPhone
SET PhoneNumer='808-722-6655'
WHERE BusinessEntityID=1
--Print 'After update trigger fired'

The PhoneNumber of Business Entity with BusinessEntityID was 697-555-0142 before the trigger fired.

And after the above update statement is run it becomes 808-722-6655 and the trigger is completed.
With the following response:

"Msg 50000, Level 16, State 10, Procedure PlNotify,
Line 4 [Batch Start Line 8]
Notify Customer Relations(1 row(s) affected)
After update trigger fired"

The trigger fires after the update and the changed table is as shown:

DML_03

While this is a very simple example of creating a data manipulation trigger there is a lot things happening in the database. Two temporary, memory resident tables- inserted and deleted are created to take stock of the changes being made.

Also only the After Insert ,Update trigger was considered and there are other types of triggers as well.

Starting and stopping a SQL Server instance using PowerShell

Firstly let us see the status of the SQL Server 2016 on this computer. There are three SQL Servers 2012, 2014 and 2016. Let us take a look at the SQL Server named instance OHANA. It appears to be stopped as seen in the Services window of the Control Panel.

StoppedOHana

Keep the Services screen open.

It is possible to start the SQL Server (OHANA) from the Services screen, but we want to start it using PowerShell.

Launch Windows PowerShelll ISE with Administrator Permission. Type in Start-Service to see the intellisense drop-down as shown.

StartService_00.jpg

Access the parameters available by just typing a hyphen (-) and waiting,

StartService_01.jpg

Pick the DisplayName parameter.

Now you need to provide a display name for SQL Server (OHANA).

Start-Service -DisplayName 'SQL Server (OHANA) '

Run the code or hit F8.

This will produce an error as shown:
PS C:\WINDOWS\system32> start-Service -DisplayName 'SQL Server (OHANA) 'start-Service : Cannot find any service with display name 'SQL Server (OHANA)
'.
At line:1 char:1
+ start-Service -DisplayName 'SQL Server (OHANA) '
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (SQL Server (OHANA) :String) [St
   art-Service], ServiceCommandException
    + FullyQualifiedErrorId : NoServiceFoundForGivenDisplayName,Microsoft.Powe
   rShell.Commands.StartServiceCommand

You notice that there is an extra space between (OHANA) and the single quote.

Now, remove that white space and hit F8.

You get the following response:

StartService_02.jpg

In the ControlPanel |..|Services window click Action | Refresh.

You will notice that the SQL Server (OHANA) has started.

You can also stop the SQL Server with the following statement:

PS C:\WINDOWS\system32> Stop-Service -DisplayName  'SQL Server (OHANA)'

WARNING: Waiting for service 'SQL Server (OHANA) (MSSQL$OHANA)' to stop...

Refresh the Services windows and verify that the server has stopped.

SQL Server Configuration Manager shortcut is missing in my Windows 10

Under Microsoft SQL Server 2016 desktop apps I should have found the SQL Server Configuration Manager. I see the Reporting Services Configuration Manager but not the SQL Server Configuration Manager.

When you search for it specifically also you do not find it.

Where is it?

The following is the reason for this is given here: TechNet.

Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an application in newer versions of Windows (8,10)

In Windows version 10  if you have to open SQL Server Configuration Manager for SQL Server 2016  the following item should be searched in  the  search charm of Windows 10:

SQLServerManager13.msc

When this is found you just click and display the SQL Server Configuration Manager as shown:

From the Help menu you can find the version.