Press "Enter" to skip to content

Month: March 2017

JSON Dates In SQL Server

Published 2017-03-29 by Kevin Feasel

Bert Wagner explains how to handle JSON datetime strings in SQL Server:

In SQL Server, datetime2’s format is defined as follows:

YYYY-MM-DD hh:mm:ss[.fractional seconds]

JSON date time strings are defined like:

YYYY-MM-DDTHH:mm:ss.sssZ

Honestly, they look pretty similar. However, there are few key differences:

  • JSON separates the date and time portion of the string with the letter T

  • The Z is optional and indicates that the datetime is in UTC (if the Z is left off, JavaScript defaults to UTC). You can also specify a different timezone by replacing the Z with a + or — along with HH:mm (ie. -05:00 for Eastern Standard Time)

  • The precision of SQL’s datetime2 goes out to 7 decimal places, in JSON and JavaScript it only goes out to 3 places, so truncation may occur.

Read on for a few scripts handling datetime conversions between these types.

Comments closed

Get RTVS To Stop Opening Notepad

Published 2017-03-28 by Kevin Feasel

Sarah Dutkiewicz figures out how to get R Tools for Visual Studio to stop having R files open in Notepad:

As I have been going through my courses – which use swirl() – I have been looking at how things work, comparing RStudio to RTVS.  One of the things that was maddening for me was going through one of the courses in RTVS and having R files open in Notepad.  Notepad?!?  RStudio wasn’t doing this, so I was even more frustrated.  I could also open R files with Visual Studio right from the file system, so the file association was already in place.  This didn’t make sense.  However… RTVS is an open source project, as is swirl().  So I spent tonight looking at code in GitHub.

Read on for the answer.

Comments closed

Grafana On Elasticsearch

Published 2017-03-28 by Kevin Feasel

Daniel Berman shows how to replace Kibana with Grafana:

While very similar in terms of what can be done with the data itself within the two tools. The main differences between Kibana and Grafana lie in configuring how the data is displayed. Grafana has richer display features and more options for playing around with how the data is represented in the graphs.

While it takes some time getting accustomed to building graphs in Grafana — especially if you’re coming from Kibana — the data displayed in Grafana dashboards can be read and analyzed more easily.

I prefer Grafana over Kibana for a few reasons, so I’m happy to see Grafana articles popping up.

Comments closed

Troubleshooting Login Failures

Published 2017-03-28 by Kevin Feasel

Jana Sattainathan explains why the default login failure message is so unhelpful:

At first glance, it appears to be utterly useless. The only useful message seems to be “Login failed for user ‘myuser‘.” but what we really need to know is “why?”.

That information is somewhat buried in this section of the message:

1
2
3
4
Server Name: mysqlserverinstance
Error Number: 18456
Severity: 14
State: 1

Based on the documentation for this error, the severity and state actually mean something. I have copied and pasted the same from the link.

Read on to find out how to get the actual error.

Comments closed

Azure Networking

Published 2017-03-28 by Kevin Feasel

Joshua Feierman has an article on how Azure Networking works, particularly from the viewpoint of a DBA:

The connecting thread between an Azure virtual machine and a virtual network is a Virtual Network Interface Card, or VNic for short. These are resources that are separate and distinct from the virtual machine and network itself, which can be assigned to a given virtual machine.

If you go to the “All Resources” screen and sort by the “Type” column, you will find a number of network interface resources.

There’s some good information in here.

Comments closed

Cloned Database Security

Published 2017-03-28 by Kevin Feasel

Parikshit Savjani explains what happens when you run DBCC CLONEDATABASE on databases with various security measures activated:

Transparent Data Encryption (TDE)

If you use TDE to encrypt data at rest on the source database, DBCC CLONEDATABASE supports cloning of the source database but the cloned database is not encrypted by TDE. Thus, the backup of the cloned database will be unencrypted. If it is desired to encrypt and protect cloned database backup, you can enable TDE on cloned database before it is backed up as shown below

It’s a good read which covers several technologies.

Comments closed

Partitioning Basics

Published 2017-03-28 by Kevin Feasel

Kim Tripp explains partitioning:

So, you’d still need to determine if this is the right approach. But, the main point – partitioning really isn’t designed to give incredible gains to your queries. It’s meant to be better for data management and maintenance. However, some partitioning designs can lead to query performance benefits too.

This is a nice introduction and makes a good point:  performance benefits to partitioning are incidental to the real benefit, which is simplicity of administration.

Comments closed

Testing SQL Server On Linux Backups

Published 2017-03-28 by Kevin Feasel

Rob Sewell confirms that Test-DbaLastBackup in the dbatools kit works for Linux:

I have written about Test-DbaLastBackup in posts here, here and here. They have been Windows only posts.

With SQL Server vNext CTP 1.4 now available and providing SQL Agent capability on Linux, I wrote here about using Ola Hallengrens scripts on Linux SQL Servers so can Test-DbaLastBackup work with Linux?

It’s a short post but good to know.

Comments closed

Connecting To Linux SQL Agent Using Powershell

Published 2017-03-28 by Kevin Feasel

Slava Murygin shows how to connect to a SQL Agent running on Linux using the SqlServer Powershell module:

From this point we will work directly with SQL Server.
In order to establish connection you have to run following script.
The most important are 2nd and third lines:
– In second line you have to provide your SQL Server Instance address, by replacing “<your_server_instance>” by something like “192.168.58.11” or “192.168.58.11\MSSQLSERVER,1433”
– When second line runs it will ask you for SQL Server credentials !!! So, you have to enter SQL user name and it’s password.

Slava does note some limitations at present, but a lot of the functionality seems to be there.

Comments closed