SQL Server Security Checklist
This check list provides, the bare minimum security requirements, you should consider when installing a new SQL Server. These requirements are important in protecting data and preventing data breaches. This checklist provides guidelines and recommendation that i use and are focused on for main areas: Physical, Operating System, User Configuration and SQL Server Configuration. Continue reading “SQL Server Security Checklist”
GitHub is a web hosted collaboration and version control solution that is opened sourced. The main purpose of this tool is to allow for developer teams to collaborate on projects virtually.
Developers and bloggers, typically use this tool to collaborate on community projects and companies use it in the same manner for corporate tech initiatives. Continue reading “Getting Started with Github Commits, Merging and Pull Request”
SQL Server starting with 2012 ship with a robust set of functions that can be used to make code perform faster, with fewer lines of code. The functions can be used in ETL Process to provide better error handling. A example of this would be the Try_Parse function that allows you to check if a value can be converted.
Another example would be using the FIRST_VALUE() and LAST_VALUE() functions which work against a set of data. These functions are very useful when looking for things like month over month averages and when doing calculations. The table below contain a list of function that are supported starting with 2012 along with some examples of how to use them.
Continue reading “SQL Server Functions”
In this post, I will use T-SQL and Microsoft C# to create a application to log file metadata into a SQL Server table.
Often times when processing data into and between data systems you need away to determine if all the data that is in the file is added to your tables. I like to store file information in the database and use this information as a way to true-up the data, in my data systems. When a file is stored on a file system it is stored with metadata that can provide very useful information. This information include file name, Creation time, Last Modified Time, File Path, File Size and you can all so get row count information. Collecting this information requires you to create a table inside the database to store the file metadata. You will also need to create an application to pull the metadata information from the files system folder directories. Continue reading “Import File Logging Application”
Sometime as a ETL developer or Database Administrator you will need to gain insight into SQL Agent job executions times. This insight can be used to proactively monitor the processing times of the various jobs running within your data environment. Continue reading “SQL Server Monthly Job Run Time Averages”
One of the most fundamental aspects of working with SQL Server is querying data stored inside a database. To query data from a database you use the SQL SELECT statement. There are two important things to understand about the SELECT statement. The first is syntax, which are the rules and structure of the SQL statement that allows the statement to be sent to the database and have results returned. The second thing to understand is the “logical processing order” of select statements. The way the SQL Server engine processes select statements to retrieve data, is different from the order statement appears in the query. When you write syntactically correct SELECT statements in SQL Server Management Studio. Continue reading “Understanding T-SQL SELECT”