Product learning

Posts by

Phil Factor

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

5 October 2018

5 October 2018

Checking for Database Drift using Extended Events and SQL Monitor

Phil Factor uses Extended Events and a SQL Monitor custom metric to detect when the metadata of a database has 'drifted', meaning that a database object has been created, deleted or modified outside of the official change management process. Read more

database drift

audit and compliance

database intrusion detection

30 September 2018

30 September 2018

Spotting Unauthorized Configuration Settings in SQL Server

If someone makes unauthorized changes to SQL Server configuration settings, it could compromise the availability, performance or security of your servers. Using using Extended Events, and a custom metric, in SQL Monitor, Phil Factor offers a way to get an immediate notification of such changes, and investigate their cause. Read more

Redgate Monitor

audit and compliance

database intrusion detection

17 September 2018

17 September 2018

Testing the Performance of Individual SQL Statements within a Batch using SQL Prompt

With SQL Prompt and Phil Factor's chk code snippet, you can, with a few clicks, get a list of all the SQL statements executed within a batch, in SSMS, their execution plans, and their execution statistics, such as duration, CPU, logical reads and so on. Read more

10 September 2018

10 September 2018

How to Detect SQL Injection Attacks using Extended Events and SQL Monitor

Phil Factor shows how to monitor for the errors indicative of a possible SQL Injection attack on one of your SQL Server databases, using a SQL Monitor custom metric that uses diagnostic data from Extended Events. Read more

sql server monitoring

SQL Injection

Phil Factor

31 August 2018

Phil Factor

31 August 2018

Pseudonymizing a Database with Realistic Data for Development Work

How to use SQL Data Generator, and PowerShell to obfuscate personal data (names), while retaining the same distribution of data, so that the test database behaves like the original. Read more

Compliant database DevOps

24 August 2018

24 August 2018

SQL Prompt Code Analysis: Avoid using the ISNUMERIC Function (E1029)

Avoid using the IsNumeric() function, because it can often lead to data type conversion errors, when importing data. On SQL Server 2012 or later, use the Try_Convert() or Try_Cast() function instead. On earlier SQL Server versions, the only way to avoid it is by using LIKE expressions. Read more

SQL code smells

Phil Factor

16 August 2018

Phil Factor

16 August 2018

Documenting your Database with SQL Change Automation

Phil Factor uses SQL Change Automation and PowerShell to verify that the source code for the current database version builds successfully and, if so, to generate web-based database documentation using SQL Doc. Read more

PowerShell

automated database deployment

13 August 2018

13 August 2018

SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)

Data in relational tables is never guaranteed to be returned in a particular order, so adding an ORDER BY to an INSERT INTO statement is meaningless and, pre-SQL Server 2012, could cause performance issues. If you really need to impose a particular order on rows in the table, use a Row_Number() window clause instead. Read more

3 August 2018

3 August 2018

Practical PowerShell Processes with SQL Change Automation

Starting from the object-level source code for a database, Phil Factor shows how SCA can create a NuGet build package, use it to migrate target database to the same version, and publish web-based database documentation, a report of what changed on the target and the results of a static code analysis assessment. Read more

Nuget

automated database deployment

25 July 2018

25 July 2018

SQL Prompt code analysis: avoid non-standard column aliases (ST002 and DEP021)

If you declare a column alias using equals SQL Prompt will raise a violation of a style rule (ST002). It's best to follow the ANSI-SQL92 standard of assigning aliases using the AS keyword. If the alias is not a standard identifier, delimit it with double quotes. using single quotes has long been deprecated (DEP021). Read more

SQL code analysis

SQL code smells

Redgate Test Data Manager

Redgate Flyway

Redgate Monitor

Overview

Protect

Automate

Monitor

Product learning

Posts by

Phil Factor

Checking for Database Drift using Extended Events and SQL Monitor

Spotting Unauthorized Configuration Settings in SQL Server

Testing the Performance of Individual SQL Statements within a Batch using SQL Prompt

How to Detect SQL Injection Attacks using Extended Events and SQL Monitor

Pseudonymizing a Database with Realistic Data for Development Work

SQL Prompt Code Analysis: Avoid using the ISNUMERIC Function (E1029)

Documenting your Database with SQL Change Automation

SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)

Practical PowerShell Processes with SQL Change Automation

SQL Prompt code analysis: avoid non-standard column aliases (ST002 and DEP021)

Redgate Test Data Manager

Redgate Flyway

Redgate Monitor

Overview

Protect

Automate

Monitor

Posts by Phil Factor

Posts by

Phil Factor