database administrator – Ramblings of a Crafty DBA

Staying Connected

January 13, 2014April 20, 2016 admin

There are many companies in the world that have offices in remote locations. Sometimes downloading software media from Microsoft or Oracle might take minutes if not hours. During this time, your terminal session with the remote computer might time out. How do you prevent this from happening? The windows server operating system allows two concurrent remote desktop (RDP) connections. However, there are out of the box settings that automatically determine idle session limit and when to disconnected a user. I am including a link to MSDN that describes remote desktop…

Transferring Logins Between Servers

January 12, 2014April 20, 2016 admin

There are two main categories of logins in SQL Server. There are WINDOWS logins in which password restrictions are controlled by the Active Directory (AD) policy. Then there are SQL logins in which the database administrator has more control. Lately, I have been crafting new database servers with the latest hardware to make sure my organization is ready for growth during the next five years. This includes installing the current version of SQL Server and migrating all the applications from the old server to the new server. In short, transferring…

Database Files – Part 3

February 20, 2012October 12, 2017 admin

DATABASE SNAPSHOTS were added to the SQL Server product in the 2005 version. They are great for saving a version of the database before major data changes happen and perfect for rolling back the changes if they are not successful. While books online suggest other uses, I think this one is the best. Please see the limitations of snapshots before creating one. I am going to explain how to create a database snapshot, how to revert back to a snapshot after corrupted occurs, and how to drop a database snapshot.…

Database Files – Part 2

February 17, 2012October 12, 2017 admin

The most basic Microsoft SQL Server database is composed of two operating system files: a primary data file and a transaction log file. If possible, the data and log files should be on different physical disks. Today, I am going to be re-creating the [WILDLIFE] database using this simple design in preparation for talking about MOVING and COPYING database files. The key CREATE DATABASE, CREATE TABLE and CREATE INDEX statements are below. The complete script which catalogs ANIMAL names is enclosed as well as today’s new examples. If you have…

Database Files – Part 1

February 16, 2012October 12, 2017 admin

A Microsoft SQL Server database is composed of at least two operating system files: a primary data file and a transaction log file. I am going to be talking about how to define databases with multiple files tonight. There are three types of operating system files. PRIMARY data file is the starting point of the database and is a pointer to any other data files that are allocated. A extension of .mdf is used to identify these files. This data file can contains system tables and user defined objects as…

Searching Stored SQL – Part 4

January 30, 2012October 12, 2017 admin

There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Earlier today, I created a pattern searching tool for FUNCTIONS. I am going to clone and modify the code so that it will work with VIEWS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…

Searching Stored SQL – Part 3

January 30, 2012October 12, 2017 admin

There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for TRIGGERS. Today, I am going to clone and modify the code so that it will work with FUNCTIONS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…

Searching Stored SQL – Part 2

January 27, 2012October 12, 2017 admin

There are four main database objects that contain stored (compiled) SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for SQL code in STORED PROCEDURES. Today, I am going to clone and modify the code so that it will allow pattern searching on TRIGGERS. First, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that…

Searching Stored SQL – Part 1

January 27, 2012October 12, 2017 admin

Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red…

Full-Text Search – Part 1

January 18, 2012February 12, 2012 admin

Today, I want to go over why text searching for patterns is an expensive query operation. I am going to create a [WILD LIFE] database that contains a table called [ANIMALS]. A identity column called [ID] will be populated by the system automatically and a [NAME] column will be loaded with 445 animals names that I grabbed from WIKIPEDIA. The idea is to search for animals that have a root word in common. The snippet below creates the database and table. The full SQL script including insert statements is at…