Another common request for DBA’s is to search for or validate that certain tables or other objects exist and in what database. This post is a follow-up to our “Check If Database Exists Using Powershell” post where we search look for a database remotely using the “invoke-sqlcmd” commandlet. Note: This… Read more »
We use simple T-SQL commands to retrieve the PATH from our msdb backup records. T-SQL doesn’t seem to have a method to slice up a folder/file path and return the parent folders so we have to use CHRAINDEX, SUBSTRING and REVERSE to pull out the file name leaving just the… Read more »
This post is for the beginner to understand how to write and modify a SQL stored procedure with parameters and variables. We will try to include some “why & how” answers that many stored procedure examples leave out. Procedures can become very complicated so we will keep it simple and… Read more »
As a DBA we are often tasked to analyze tables, views and procedures that are unfamiliar such as a vendor provided database. This is a simple script working with SQL Sever to find a stored procedure containing a text string. It’s very useful for auditing bad procedures or looking for… Read more »
Developer will create their web.config, populate the SQL Sever connection settings and rarely return to that file again. This is usually after contacting their DBA to get the server, port and SQL version information as well as permissions to access the database. Then they “google it” for the connection string… Read more »
Even in smaller environments the DBA will run into large databases and very large tables. This can be due to neglect, bad table design or just a recent change with unexpected results. When dealing with these large tables we often need the fastest way to get the table row count…. Read more »
Dealing with dates in SQL Server is a critical skill and required when developing reports that depend on the Date, Day and Hour to be accurate. This includes logically moving through the year or bracketing results inside a month in your code. Here are some examples of how to get… Read more »
The use of a subquery (sub-select) statement in T-SQL queries is common but can cause slow performance as the “subquery” needs to to be evaluated first. We can use a common table expressions (CTE) in place as a temporary result set to work with. The code can also be reused… Read more »
Sending emails using DBMail is one of the most common requested tasks for the DBA. This could be alerts, reminders, results from a job or reports for users. As a DBA you should be familiar with querying a table and sending results as a CSV or Excel file as an… Read more »
Inventory tracking and reporting is important for any Administrator position in IT. The DBA needs to be able to be able to report on the Database size requirements for all supported systems. The following script is not the only way this information can be collected but it is simple. We… Read more »
While working with Powershell I have begun to move away from using the SSMS GUI to get basic details on SQL instances. Management Studio is an excellent tool but I find myself keeping Powershell open and using it for quick queries to answer the basics. One of the basics is… Read more »
Random strings, characters and ID’s are used all the time in development and especially in Database work where some data needs to be unique or uniquely identified. This example code was written many years ago to generate passwords in SQL 2008 with certain complexities for automation purposes using the RAND()… Read more »
This is the continuation for using the advanced methods to install a SQL cluster instance unattended. In SQL Failover Cluster Part 1 we created a Configuration file using the wizard and then modified the file to allow a Silent or Quiet unattended installation. This “prepare” install is only used to… Read more »
This is an advance process that allows us to prepare a cluster node or likely multiple nodes quickly. We will use the configuration file feature and install the SQL services and instance level resources on each node so they are exactly the same. Then we fail all of the drives… Read more »
For the Python experienced Developers & DBA’s I thought I would include an article about connecting to SQL server similar to the SQL server powershell version we wrote recently. I tested this code using PyCharm and a new virtual environment that did not have pyodbc. To connect to SQL Server… Read more »
The articles I found on Relational Database design are pretty dry and academic. The assumption is you are a seasoned Database Developer and you fully understand all of the terminology and why things like data types and columns are even needed. They tell you to list all of your data… Read more »
The SSMS application is very versatile and can be easily used to export queries you run into a simple comma separated file (CSV). This is great when your working with only one instance but what if you need to consolidate data from multiple instances in the environment? With this example… Read more »
For most DBA’s we will use SSMS and connect to the SQL instance to see if a databases exists and is spelled correctly. This is usually in response to troubleshooting a connection so connecting to the SQL instance makes sense as you may need to check the login permissions too…. Read more »
Learning to create and manage stored procedures in SQL is another useful SQL databases skill for the DBA and developer. The Procedures allow you to create repeatable code that can be executed using just a database single call. We can also design the procedure to handle passing parameters into it…. Read more »
