Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Redgate Data Masker
The Voice of the DBA
 

The First Rule

A long time ago I read a book called The First Law. It's a legal thriller, and I'd recommend it. It has nothing much to do with databases, but I was reminded of the book and the rules of any particular process when I saw a post called The First Database Rule.

The post is from Seth Godin, who is really a marketing individual, but I do think his rule makes a lot of sense for clients. I also think that as both developers and database professionals, we forget that most of our clients would prefer to have our systems follow this rule: it should be as simple to fix an error as it is to make one.

I've worked in many systems, and with many people, that expect data entry or loads to go perfectly. They expect things to work and want to blame the process, the person, the file, or something else when something doesn't work well. What might be worse sometimes is that we expect some data in the database to be immutable, like a PK, assuming that clients will have and enter all the correct data at once. Most of us do allow changes in our databases, but we don't make it easy, especially if it's a piece of data like a primary key.

This is one reason I dislike many natural keys as PKs. We make mistakes when we enter them. Heck, I've probably typed my name a million times on a computer keyboard, and I still make mistakes. Even in data entry forms. What's worse, I have some auto-fill selections in my browser that are incorrect and I'll select the wrong one at times.

Fixing mistakes ought to be easy. We ought to expect that we will get data wrong. We'll load it wrong, we'll transform it wrong, and our DBAs will type corrections wrong. Design the system to account for mistakes and ensure that problems can be quickly fixed. Whether by a client or a sysadmin.

Agility ought not to be just how quickly we can change the software. It needs to include the ability to change the data, which is the most important part.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Redgate SQL Prompt
 
 Featured Contents
Stairway to SQL Server Security

Stairway to SQL Server Security Level 3: Principals and Securables

Don Kiely from SQLServerCentral.com

What is a SQL Server principal? And what does it get a permission on? In this stairway level, you’ll learn about the various principals that can be authorized through permissions to perform actions and access securable objects in the SQL Server instance.

Integrating Redgate SQL Code Guard with SonarQube

Additional Articles from Redgate

Greta Rudžioniene provides a step-by-step guide to running SQL code analysis checks, as defined by SQL Code Guard, in SonarQube, a general-purpose code quality tool that can perform continuous code analysis on a variety of languages.

Free eBook: SQL Server Execution Plans, Third Edition

Press Release from Redgate

If a query is performing poorly, and you can't understand why, then that query's execution plan will tell you not only what data set is coming back, but also what SQL Server did, and in what order, to get that data. It will reveal how the data was retrieved, and from which tables and indexes, what types of joins were used, at what point filtering, sorting and aggregation occurred, and a whole lot more. These details will often highlight the likely source of any problem.

From the SQL Server Central Blogs - Temporary stored procedures

Kenneth.Fisher from SQLStudies

tl;dr; Temporary stored procedures are excellent for testing pieces of code that are going to go into (or come from) ... Continue reading

From the SQL Server Central Blogs - Expose Multiple Docker Ports

Steve Jones - SSC Editor from The Voice of the DBA

I was working with containers recently with Jenkins. I didn’t want the server process running on my machine all the time, but I did need to allow some communication....

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Reading Part of a Dataframe

I am tracking some of my flights in this csv file: 
Flight,Origin,Destination,FlightTime
UA9,IAH,LHR,"2019-04-30 17:34"
UA58,LHR,ORD,"2019-05-01 09:12"
UA99,LAX,LHR,"2019-06-17 20:18"
UA25,LHR,IAH, "2019-06-22 07:23"
I want to read this into a dataframe in R, but only the first two rows. How do I do this?

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Security Design

In the SQL Server 2005 era and the Trustworthy Computing Initiative, Microsoft embraced the development of SQL Server by following a few pillars of security design. They were secure what?

Answer: by design, by default, in deployment, through communication

Explanation: The four pillars were:

  • secure by design - using analysis and auditing of code
  • secure by default - out of the box, the installation should be secure
  • secure in deployment - with tools and regular patches
  • secure through communication - sharing knowledge with customers.

Ref:

 

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration
How do I encrypt a DB on SUBSCRIBER server to an encrypted DB on the PUB server? - I have a PUB on a SQL 2014 Ent server where the database is encrypted w/ TDE (Key algorithm=AES, Key Length=128) I have a SUB to that PUB residing on a SQL 2017 Ent server. When I check the PUB DB, it is encrypted. When I check the SUB DB is is NOT encrypted. I […]
Ports to be opened - Hi All, We have an on-prem to Azure IaaS migration going on, we have to copy the files between the on-prem windows DB server to Azure VM DB server.   Can someone help me how can I achieve this by opening the ports in the firewall. I know using storage explorer I can upload and […]
SQL Server 2017 - Development
T SQL Advance Report Month Range on the 16th - Looking For More Effect. approach - Greetings All, I'm looking for suggestions on how to improve the script below. Requirements: From the 1st of the month to the 15th, report on the current month plus 4 additional months ( total of five months). From the 16th of the month to the end of the month, advance the month range and report […]
New login doesn't appear in Security > Logins folder - I think my original login was DESKTOP-8J18OSS\micha which appears in the folder Security > Logins.  Then I changed the computer name to Virginia.  The top most value of the Object Explorer says: VIRGINIA\SQLEXPRESS2017 (SQL Server 14.0.2027 - VIRGINIA\Michael) Isn’t VIRGINIA\Michael the login name?  If so why doesn’t it appear in the Security > Logins folder? […]
sqlcmd sql agent job - I have a sqlcmd script, it runs fine if I open the command line to run it. sqlcmd -S (local)  -d mydb -E -s, -W -i D:\_ssisfiles\scripts\ProductMap.sql | findstr /V /C:"-" /B  > D:\_SSISFiles\Scripts\xtest.csv But if I makes it to a sql agent job, the job failed and it got the following error: Sqlcmd: Error: Error occurred […]
SQL Server 2016 - Administration
Query store is set to read only - I have the database and it says that the querystore is set to read only. Do i just run the following commands? --Clean it up ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR; --Set it back to read write ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE); GO The threshold i have are 367 days- reduce it […]
SQL Server 2016 - Development and T-SQL
How to get the list of login accounts logged in over specific period of time - Hi, Can any one help me to give me the sql script for following requirement 1: List of login account stored in DB table for last 3 months against a specific DB like TestDB 2: The script should insert the records to table with following details Login name, Login DateAndTime, Database Name,Host name etc for […]
using trigger for tracing - Is it possible to create a trigger on a table for a specific column that will log the user who modified column and the sql text used to update the column?  I'm trying to track down where changes to a field is coming from. Or can SQL Server profiler do what I'm looking for?
Get an Output from a Stored Procedure - Hi Forum, I have an SP developed by Pinal Dave which is great for removing all text from a given table value. I want to apply the same logic via a Stored Proc that loops down a Column of a table, removes the text characters & then outputs the results to a temp table, external […]
Development - SQL Server 2014
Using Try Catch in Procedure with Multiple Update queries - All, I am trying to implement a Try Catch in a stored procedure that contains multiple Update routines (see below code). Is the below the correct way to do this? Any help is appreciated.   BEGIN TRY /*This routine sets any SOAP Note where there is no matched claims that has an encounter date >= […]
Customize a datetime field - Hi All, Looking to get advice on how to create a datetime field by combining int year and month fields with a '05' for the date. I'm trying this in my query for the field: Cast(Convert(nvarchar,(ib.InvoiceAccountingYear + '-' + ib.InvoiceAccountingMonth + '-' + '05'))AS smalldatetime) AS FirstInvDt but it's returning dates such as these, with […]
SQL 2012 - General
Update Stats or Code - Hi, Could you please let me know if the 'below script/code' OR..'Auto Update statistics'= True ( DB - Properties-Options )  does the SAME work for the specific DB. I see the below code running for some databases in SQL agent job. Now my questions is if we just set the Auto Update statistics = True […]
Amazon AWS and other cloud vendors
AWS RDS SQL Server Licensing - SSIS - We are planning on using an SQL Server Standard RDS instance on AWS to host a number of databases. For to load the database the intention is to a Windows EC2 Instance on which we would like run SSIS packages to fetch and load data onto the databases hosted on RDS instance. Does the Windows […]
SQL Server Newbies
Using SQL for multiple pulldowns - So, I need to be able to use SQL to provide options in a series of pulldowns related to an employee's space/location. They are: Pulldown #1: Building Pulldown #2: Floor Pullown #3: Room Number   The user would select an option in each pulldown but do them in sequential order (Building pulldown first, then Floor […]
Certification
Questions on Degree programs - Hey all, Junior DBA here in USA. There are several community colleges near me that offer Database management degree programs. I'm wanting to get into DB development, and i'm not sure how to make the leap since im coming from IT background. Does anyone have any experience with such programs? or recommendations in general?   […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

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