Press "Enter" to skip to content

Month: March 2017

Using Azure Data Factory With Biml

Published 2017-03-06 by Kevin Feasel

Meagan Longoria has a multi-part series on using Biml to script Azure Data Factory tasks to migrate data from an on-prem SQL Server instance to Azure Data Lake Store.  Here’s part 1:

My Azure Data Factory is made up of the following components:

  • Gateway – Allows ADF to retrieve data from an on premises data source

  • Linked Services – define the connection string and other connection properties for each source and destination

  • Datasets – Define a pointer to the data you want to process, sometimes defining the schema of the input and output data

  • Pipelines – combine the data sets and activities and define an execution schedule

Click through for the Biml.

Comments closed

Test User Generation With Powershell

Published 2017-03-06 by Kevin Feasel

Rob Sewell shows how to use dbatools to create test user accounts quickly:

Of course we can use any source for our users – a database, an excel file, Active Directory or even just type them in.

We can use the Add-SQLLogin command from the sqlserver module to add our users as SQL Logins, but at present we cannot add them as database users and assign them to a role.

Rob includes a demo script as well, thereby making it even easier.

Comments closed

Automated Database Restoration And CHECKDB

Published 2017-03-06 by Kevin Feasel

Anthony Nocentino shows how to use dbatools to automate testing restoration of database backups and running DBCC CHECKDB against these restored backups:

Requirements

  1. Automation – Complete autopilot, no human interaction.
  2. Report job status – Accurate reporting in the event the job failed, the CHECKDB failed or the restore failed.

Solution

  1. Use dbaltools cmdlets for restore and CHECKDB operations
  2. Use SQL Agent Job automation, logging and alerting

So let’s walk through this implementation together.

You won’t get a turnkey solution from this blog post, but you will get a good process to follow.

Comments closed

Using DAX For Dynamic Grouping

Published 2017-03-03 by Kevin Feasel

Jason Thomas shows how to include an “All Others” member in a Power BI visual:

Requirement: The user wants a report with a column chart. The X axis will have Subcategory Name and the value will be the sum of Internet Sales. Along with this chart, the user will have a slicer where they can select the Subcategory Names. The column chart should “update” showing one column for each selected subcategory, and another column named “Others” with the summed amount of the rest of the unselected categories.

Basically, they wanted a dynamic group called “Others” and the members within this group should change based on what is selected on the slicer.

This would be a good time to show a visual representation of what the requirement means.

Click through for that visualization, as well as the solution.

Comments closed

Error Log Column Size

Published 2017-03-03 by Kevin Feasel

Cody Konior tests how long messages in the error log can be:

Stupid question… what’s the schema of a table with sys.sp_readerrorlog output? Well you might be surprised if you’re used to using nvarchar(max) or nvarchar(2048).

There’s a datetime (modern datetime2(3)) obviously. ProcessInfo is either “Server” or “spidxxxy” where xxx is an int (max of 11 characters including minus) and y is an optional single character suffix. But as for the text…

Let’s try to max it out!

Moral of the story:  keep those error messages as short as possible while still being meaningful.

Comments closed

HBase Compaction

Published 2017-03-03 by Kevin Feasel

Jitendra Bafna explains how HBase compaction works:

Compaction is a process by which HBase cleans itself. It comes in two flavors: minor compaction and major compaction.

Minor compaction is the process of combining the configurable number of smaller HFiles into one Large HFile. Minor compaction is very important because without it, reading particular rows requires many disk reads and can reduce overall performance.

Major compaction is a process of combining the StoreFiles of regions into a single StoreFile. It also deletes remove and expired versions. By default, major compaction runs every 24 hours and merges all StoreFiles into single StoreFile. After compaction, if the new larger StoreFile is greater than a certain size (defined by property), the region will split into new regions.

Read on for more information about compaction and data locality, which is a totally different topic.

Comments closed

The Value Of SQL Browser

Published 2017-03-03 by Kevin Feasel

Chris Sommer points out that the SQL Browser does something useful:

Bingo. The application server could not connect to the SQL Browser service on UDP 1434. So maybe now you’re asking why, and that’s kinda the gist of this post. The SQL Browser provides a valuable service when an application tries to connect to a SQL Server named instance. The SQL Browser listens on UDP 1434 and provides information about all SQL Server instances that are installed on the server. One of those pieces of information is the TCP port number that SQL is listening on. Without that info, the application has no idea how to reach to your SQL Server, and will fail to connect. This was our exact issue.

Do read this, though my preference is to shut off the SQL Browser because it’s a mechanism attackers can use for gathering intel on where SQL Server instances live.

Comments closed

Understanding Database Role Permissions

Published 2017-03-03 by Kevin Feasel

Jason Brimhall shows what happens when you make a user a member of every database role at the same time:

A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. In a previous article, I outlined the different flavors of principals while focusing primarily on the users and logins. You can brush up on that article here. While I touched lightly, in that article, on the concept of roles, I will expound on the roles a bit more here – but primarily in the scope of the effects on user permissions due to membership in various default roles.

Let’s reset back to the driving issue in the introduction. Frequently, I see what I would call a gross misunderstanding of permissions by way of how people assign permissions and role membership within SQL Server. The assignment of role membership does not stop with database roles. Rather it is usually combined with a mis-configuration of the server role memberships as well. This misunderstanding can really be broken down into one of the following errors:

  • The belief that a login cannot access a database unless added specifically to the database.

  • The belief that a login must be added to every database role.

  • The belief that a login must be added to the sysadmin role to access resources in a database.

Worth reading.  Spoilers:  database roles are not like Voltron; they don’t get stronger when you put them all together.

Comments closed

SQL Authentication Accounts Without Password Policy

Published 2017-03-03 by Kevin Feasel

Chris Bell shows how to find accounts using SQL authentication and which do not have the “enforce password policy” flag set:

Recently I was performing a security audit for a client. One of the many things I had to check was the enforcement of password policies for any SQL Server created accounts.

You know, that policy that says you must have some combination of 6 or more characters, upper and lower case, a number, and special characters, etc.

These policies are controlled by the server policy settings and were something easy to check. The actual passwords and that they were safe, not so much.

Click through for the script.

Comments closed

AG Secondary Stats Overwritten With Sample

Published 2017-03-03 by Kevin Feasel

Taiob Ali has run into an interesting issue:

Once I update my statistics with fullscan, with in 10~20 seconds some of the statistics on the same table are getting update on secondary with a sample pecent of rows. Meaning my best statistics are being overwritten with good (full vs sample) statistics. On primary node once I run “Update statistics Tablename with fullscan” . I see following about statistics status.

After 10~20 seconds of updating statistics in primary node if I check the status of the same on my secondary nodes, I see fullscan statistics is replaced by sample statistics. Look at the rows_sampled and last_updated column, you will see the sample row number and last_updated column time is within few seconds of update in primary. RowsModified column still showing zero records.

It’s happening on an Availability Group secondary.  Taiob has a workaround, so read on for that.

Comments closed