Create Custom Audit Specifications using SQL Server Audit
In this tip, we will create custom audit specifications using SQL and show how to attach these to an audit in SQL Server.
Dr. Derek Colley is the lead technical consultant for Optimal Computing, a boutique data consultancy based in the UK NW helping clients troubleshoot, configure, plan and deploy their data platforms. He specializes in data integration and database performance optimization, and is a PhD candidate with Staffordshire University, looking into methods of integrating AL query selection algorithms with the cost-based query optimizer. In his spare time, he enjoys building robots, going outdoors, and spending time with his four children.
- MSSQLTips Awards: Trendsetter (25+ tips)
Run Python Scripts in SQL Server Agent
In this we look at how you can run Python scripts using SQL Server Agent jobs.
Predicting Customer Spend with a Neural Network using R and SQL Server
This tip introduces neural networks and shows how we can use SQL Server and R to codify data, create and train an R-based neural network, store the definition of a neural network for re-use and create stored procedures to make predictions.
Automatically Create and Anonymize Downstream Databases from Azure DB
In this tip we look at how to automatically anonymize data in your databases to create test and development databases from your production databases.
Importing Data from AWS DynamoDB into SQL Server 2017
In this tip we present a solution to import data directly from DynamoDB within SQL Server 2017 by using in-line, natively-supported Python scripting to communicate with the AWS service using access key pairs.
Choosing Resource Model and Service Tier for Azure DB
This tip will define the key terms of an Azure DB and provide more information on the levels of service each combination provides.
SQL Server Data Access Using R – Part 3
In this tip we will look at how to use R functions, in memory settings and mapping data with R.
SQL Server Data Access Using R – Part 2
In this tip, we will follow on from SQL Server Data Access using R – Part 1 and show how to perform further data analysis and refinements, demonstrate more R functions, and show how to overlay and multi-plot R graphs.
SQL Server Data Access Using R – Part 1
In this tip, we will look at RStudio, an integrated development environment for R, and use it to connect, extract, transform, plot and analyze data from a SQL Server database.
Configuring SQL Server Endpoint and ACL Access in Windows Azure
This tip will demonstrate how to secure SQL Server in Azure using a real, deployed virtual machine by configuring the endpoint ports and Access Control Lists (ACLs) on each endpoint.
Remodel Poorly Designed SQL Server Database Tables
Learn how to analyze and remodel poorly designed SQL Server database tables including First, Second and Third normal form as well as design best practices.
Storing passwords in SQL Server – things to know to keep the data secure
Learn about the risks of storing passwords in a SQL Server database and how to do so properly without compromising your overall application.
Building Quick SQL Server Data Input Forms with InfoPath
Learn how to build quick and efficient data input forms using InfoPath to access SQL Server data in a step by step example.
Implementing Luhn’s Algorithm in T-SQL to validate credit card numbers
In this tip we look at using Luhn's Algorithm to validate numbers such as valid credit card numbers. We walk through how this Algorithm works along with how to create a T-SQL function to validate the numbers.
Accessing OLE and COM Objects from SQL Server using OLE Automation Stored Procedures
You wish to access OLE or COM objects, such as file system objects (files, folders) and their properties directly from SQL Server. This might be, for example, to load data automatically from a raw data source; to check for the existence of a directory bef
Demonstrating the Effects of Using Data Compression in SQL Server
You are a DBA or developer mulling over data compression options in SQL Server. You wish to know how much space compressing a particular index will save; you need to know how this will affect query performance. In this tip we compare the impact of compres
Different ways to get random data for SQL Server data sampling
You are looking to retrieve a random sample from a query result set. In this tip we look at different approaches using T-SQL.
Explanation of SQL Server IO and Latches
Recently, I noticed this intriguing message in one of my SQL Server 2008 R2 error logs: Warning: Failure to calculate super-latch promotion threshold. In this tip we review locking and latching and this is used by SQL Server.
Compare Big Data Platforms vs SQL Server
SQL is derided by modern developers as 'Scarcely Qualifies as a Language'. But just how efficient are the new wave of NoSQL languages touted by bleeding-edge skunk works? This tip is a defense of SQL and of the relational model, and argues for the efficie
Techniques For Improving SQL Query Performance – Indexing, Parameterization and Partitioning
You are tasked with examining poor performance for queries on a certain table, or range of tables. Perhaps a sales report is timing out or running unacceptably slow; perhaps your web application is failing to return the result set before the application t
Creating Dynamic T-SQL to Move a SQL Server Database
You have a task to undertake the scripting out of many individual T-SQL statements. For example, you need to rename a number of SQL Server database files. You need to automate the creation of T-SQL statements rather than manually script out the solution.
Troubleshooting Excess Compilations in SQL Server Using the Plan Cache and PowerShell
You are seeing evidence of an excessive number of query compilations or recompilations in SQL Server. This may be manifesting itself as high CPU load, or longer transaction execution times. You need to identify which queries are causing this behavior and
Demonstrations of Transaction Isolation Levels in SQL Server
You are looking at ways of ensuring atomicity and consistency in your database by ensuring transactional integrity. You may want to avoid locks, or ensure problems such as lost updates or dirty reads cannot occur, or otherwise ensure the quality of your d
Configuring Alerts for SQL Server AlwaysOn Availability Groups
You need to configure alerts to notify your DBA or out-of-hours team if there is a problem with your SQL Server replication or high-availability groups; for example if an automatic failover occurs, or an unacceptable number of queued transactions builds u