The Bit Bucket

Damon Edwards had a session recently where he said claimed that Without Self-Service Operations, the Cloud is Just Expensive Hosting 2.0. There is much in his session that I completely agree with, and have been concerned about for quite a while. I see it more in terms of the adoption of Platform as a Service (PaaS) offerings.

I spend most of my consulting/mentoring time in larger organizations, many are large financial organizations. In every one now, there is a person heading up a “Cloud Transformation” project, but none of these companies mean the same thing when they talk about these types of projects.

2018-11-23

SQL Server 2008 added the ability to work with spatial data by the additional of the geometry and geography data types. When they first were added, there was no tools support for working with them, and all we had was direct manipulation of their internal binary storage.

Here’s an example:

I’ve defined a variable named @Shape of type GEOMETRY. I’ve then assigned a shape to it, based on a polygon formed by a set of points. If you look carefully, you’ll notice that it’s a square.

2018-11-22

I’m often checking out what the primary keys are for tables that I’m working on. One of our free SDU Tools for developers and DBAs does just that. it’s ListPrimaryKeyColumns.

You can see how to execute it in the main image above. The procedure takes these parameters:

@DatabaseName sysname - This is the database to process @SchemasToList nvarchar(max) - a comma-delimited list of schemas to include (ie: ‘Sales,Purchasing’) or the word ‘ALL’ @TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: ‘Customers,Orders’) or the word ‘ALL’

2018-11-21

Banks, Councils, and Government Departments are often lecturing customers about protecting against identity theft, yet they often a indirect potential cause of that threat.

Sending to Old Addresses

This one really frustrates me. When we change the postal address for one of our accounts, they almost always send a letter to our old street address. I can imagine why they think that’s a sensible idea, but if we’ve already left that address, what they are doing is sending our private details to whoever now occupies the house.

2018-11-20

Betteridge’s law of headlines says that any headline that asks a question can be answered “no”. Well, contrary to that law, the TLDR answer to this is “yes”. I’m endlessly reading information that says that if your referential integrity is being checked in your OLTP application that’s providing data for your data warehouse, that you don’t need to then check it within the data warehouse. Sadly, this is often naïve thinking, for a number of reasons.

2018-11-19

I’m trying a number of different categories of books lately, because I’m getting through far more than previously. One category I thought it would be interesting to pursue where the conversation/influence areas. One that caught my eye was Exactly what to say: The Magic Words for Influence and Impact by Phil M Jones.

This was quite interesting but I’m not sure if I liked it or not.

In this book, Jones takes you through a number of situations, where people often say the wrong thing and make things worse, or where they don’t even know what to say, and he gives formulas for how to structure responses that turn the situation back around the way you want, to keep heading to the right outcome.

2018-11-16

SQLCMD mode changes how queries are executed in SQL Server Management Studio (SSMS). When using this mode, you can work with options that aren’t normally part of SQL Server T-SQL scripts.

Some installation scripts also require SQLCMD mode and will fail if it’s not enabled.

Here’s an example of running a query against 3 servers within the same script:

First we open a new query window, then on the Query menu, we choose SQLCMD Mode.

2018-11-15

Anyone who’s worked with SQL Server for any length of time has probably run into collation issues. They will have seen something like this:

SQL Server Error: Cannot resolve the collation conflict

In many cases, it will be a difference between the collation in tempdb and their own database, they’ve created temporary tables, and tried to join them to tables in their database. And it goes bang !

While you can fix it in the query by specifying a COLLATE clause, it’s a pain in the neck. If you use temp tables, you really should have this when working with them so your application isn’t dependent upon the server’s collation.

2018-11-14

I spend a lot of time mentoring on client sites, and many of the clients are large organizations. Often these organizations require me to attend “training” on a regular basis, to satisfy their corporate compliance goals.

I don’t mind doing this at all, even though the course on conflicts of interest, or handling private or sensitive data, at company A is invariably almost word for word the equivalent course that I do at company B, and company C.

2018-11-13

I spend a lot of time working with teams of DBAs and developers who need to fix performance problems. One of the real issues that I see time and again though, is people fixing the symptom, not the problem.

Many DBAs spend their time tracing query activity, working out which queries the system is spending a lot of time on, and then working out how to make those important queries run faster.

2018-11-12