Data Management Tips

You are assigned the task of creating an application to maintain the data for a new application from a data administration perspective. The application should be able to add new records, but first check to see if the record already exists. If the record already exists, it should update the record. The user should also be able to list and delete the selected records. Typically the approach is to build separate stored procedures for inserting, updating, deleting and selecting all the records in the table. What if you are asked to create just one stored procedure for all this functionality per table to lessen the number of stored procedure to maintain in a project from a data administration perspective? What would you do? Is there a way to inc

Every system has it, do not feel like you are the only one. Step up to the plate and correct it. That is large amounts of data that were once needed for the business, but that can now be archived. This is data could be a large performance issue because you are not able to perform maintenance on the table(s) because the time needed exceeds your maintenance window. As a DBA or developer just because you do not think the data is needed may not mean that it can just be deleted. Many industries have legal requirements to maintain the data. On the other side of the coin though, just because a business person says the data is needed does not mean it has to be kept in the primary OLTP database that your entire company uses. Seek out the fac

I am running very low on storage to support my SQL Server 2000 user defined databases on a number of my production SQL Server instances. We have made the decision to invest in some sort of NAS\SAN or direct attached storage solution. Independent of which technology, vendor and RAID sets we select, I need to plan for my database storage for the next 18 to 24 months and beyond. I have seen your script for capacity planning at a table level (Easing the Capacity Planning Burden), but how can I roll this up at a database level? I need to determine the storage needs at a database and SQL Server instance.

The institution I work with receives data through a variety of vehicles and since they conduct different surveys and investigations the data does not always fit into a single model. Once the data is received, I need to get the data loaded into SQL Server. One of the common vehicles used to deliver the data is Microsoft Access 2007. I spend a significant amount of time looking through these databases to figure out how the data is arranged and would like to find a way of interrogating the database in a more efficient manner. In this tip I show you how this can be done.

At our organization we need to check for a minimum amount of free space before proceeding with some processes that run on SQL Server 2000, 2005 and 2008 SQL Server instances. Do you know of a way to find out the free disk space and then fail the process if it does not meet the minimum requirements? Can you provide some sample code?

I've seen so many different options and scripts for determining free space, consumed space, and total space allocated for databases in Microsoft SQL Server. Problem is none ever seem to give me all the information. I need to run one script to see this information for the log file and a different one for the data files. Is there anything out there that provides this information universally?

In data warehousing applications during ETL (Extraction, Transformation and Loading) or even in OLTP (On Line Transaction Processing) applications we are often encountered with duplicate records in our table. To make the table data consistent and accurate we need to get rid of these duplicate records keeping only one of them in the table. In this tip I discuss different strategies which you can take for this, along with the pros and cons.

A common headache for database and system administrators is capacity planning in SQL Server 2000. A good guess is a good start, but it is equally important to keep a close eye on databases and tables to make sure disk space shortage doesn't creep up on you. One of the easiest ways to keep up with your databases is to use some of the system functions to your advantage, like sp_spaceused and xp_fixeddrives.

Is Microsoft SQL Server superior to MySQL or not? What are the pros and cons of using SQL Server over MySQL? Is MySQL mature enough to compete with a big player like Microsoft and Oracle? Can we fairly compare both products?

One of the advantages of using the Bulk-Logged recovery model is to minimally log bulk load commands, but still have other transactions fully logged. You may find that when you set your recovery model to Bulk-Logged or to Simple and you run your bulk load commands that the size of your transaction log still grows quite large. The reason for this is that there are some other settings and criteria that need to be met in order to minimally log the bulk insert commands and to minimize the amount of space needed in your transaction log.

Sometimes there is need to have a static set of data from your database. The need may be for reporting, auditing or even recovery of data. This can be pretty easily done using SQL Server backups and restores, but the time that it takes to perform the backup and restore task can be quite long depending on the size of the database. With SQL Server 2005 a new feature has been introduced called database snapshots.

One problem that DBAs often face is maintaining lookups tables across multiple servers or sites. These tables could either be replicated or manually updated, but in any case sometimes the data in these tables get out of synch. In a previous tip we talked about SQL Server comparison tools that allowed you to compare database objects or even the data itself between two different databases. These tools are great and definitely offer a lot of advantages, but there is a new tool in SQL Server 2005 that might help as well.

Do you need to make SQL Server data available for a SAS Analytics package? Do you also need to accept some data back from a SAS package and also process the returned values? In this tip we cover how a SQL Server professional with limited or no SAS experience can accomplish these goals.

Is Microsoft SQL Server superior to MySQL or not? What are the pros and cons of using SQL Server over MySQL? Is MySQL mature enough to compete with a big player like Microsoft and Oracle? Can we fairly compare both products?

At our organization we need to check for a minimum amount of free space before proceeding with some processes that run on SQL Server 2000, 2005 and 2008 SQL Server instances. Do you know of a way to find out the free disk space and then fail the process if it does not meet the minimum requirements? Can you provide some sample code?

You are assigned the task of creating an application to maintain the data for a new application from a data administration perspective. The application should be able to add new records, but first check to see if the record already exists. If the record already exists, it should update the record. The user should also be able to list and delete the selected records. Typically the approach is to build separate stored procedures for inserting, updating, deleting and selecting all the records in the table. What if you are asked to create just one stored procedure for all this functionality per table to lessen the number of stored procedure to maintain in a project from a data administration perspective? What would you do? Is there a way to inc

One of the advantages of using the Bulk-Logged recovery model is to minimally log bulk load commands, but still have other transactions fully logged. You may find that when you set your recovery model to Bulk-Logged or to Simple and you run your bulk load commands that the size of your transaction log still grows quite large. The reason for this is that there are some other settings and criteria that need to be met in order to minimally log the bulk insert commands and to minimize the amount of space needed in your transaction log.

Do you need to make SQL Server data available for a SAS Analytics package? Do you also need to accept some data back from a SAS package and also process the returned values? In this tip we cover how a SQL Server professional with limited or no SAS experience can accomplish these goals.

In data warehousing applications during ETL (Extraction, Transformation and Loading) or even in OLTP (On Line Transaction Processing) applications we are often encountered with duplicate records in our table. To make the table data consistent and accurate we need to get rid of these duplicate records keeping only one of them in the table. In this tip I discuss different strategies which you can take for this, along with the pros and cons.

Every system has it, do not feel like you are the only one. Step up to the plate and correct it. That is large amounts of data that were once needed for the business, but that can now be archived. This is data could be a large performance issue because you are not able to perform maintenance on the table(s) because the time needed exceeds your maintenance window. As a DBA or developer just because you do not think the data is needed may not mean that it can just be deleted. Many industries have legal requirements to maintain the data. On the other side of the coin though, just because a business person says the data is needed does not mean it has to be kept in the primary OLTP database that your entire company uses. Seek out the fac

I've seen so many different options and scripts for determining free space, consumed space, and total space allocated for databases in Microsoft SQL Server. Problem is none ever seem to give me all the information. I need to run one script to see this information for the log file and a different one for the data files. Is there anything out there that provides this information universally?

The institution I work with receives data through a variety of vehicles and since they conduct different surveys and investigations the data does not always fit into a single model. Once the data is received, I need to get the data loaded into SQL Server. One of the common vehicles used to deliver the data is Microsoft Access 2007. I spend a significant amount of time looking through these databases to figure out how the data is arranged and would like to find a way of interrogating the database in a more efficient manner. In this tip I show you how this can be done.

I am running very low on storage to support my SQL Server 2000 user defined databases on a number of my production SQL Server instances. We have made the decision to invest in some sort of NAS\SAN or direct attached storage solution. Independent of which technology, vendor and RAID sets we select, I need to plan for my database storage for the next 18 to 24 months and beyond. I have seen your script for capacity planning at a table level (Easing the Capacity Planning Burden), but how can I roll this up at a database level? I need to determine the storage needs at a database and SQL Server instance.

Do you need to make SQL Server data available for a SAS Analytics package? Do you also need to accept some data back from a SAS package and also process the returned values? In this tip we cover how a SQL Server professional with limited or no SAS experience can accomplish these goals.

You are assigned the task of creating an application to maintain the data for a new application from a data administration perspective. The application should be able to add new records, but first check to see if the record already exists. If the record already exists, it should update the record. The user should also be able to list and delete the selected records. Typically the approach is to build separate stored procedures for inserting, updating, deleting and selecting all the records in the table. What if you are asked to create just one stored procedure for all this functionality per table to lessen the number of stored procedure to maintain in a project from a data administration perspective? What would you do? Is there a way to inc

The institution I work with receives data through a variety of vehicles and since they conduct different surveys and investigations the data does not always fit into a single model. Once the data is received, I need to get the data loaded into SQL Server. One of the common vehicles used to deliver the data is Microsoft Access 2007. I spend a significant amount of time looking through these databases to figure out how the data is arranged and would like to find a way of interrogating the database in a more efficient manner. In this tip I show you how this can be done.

Is Microsoft SQL Server superior to MySQL or not? What are the pros and cons of using SQL Server over MySQL? Is MySQL mature enough to compete with a big player like Microsoft and Oracle? Can we fairly compare both products?

In data warehousing applications during ETL (Extraction, Transformation and Loading) or even in OLTP (On Line Transaction Processing) applications we are often encountered with duplicate records in our table. To make the table data consistent and accurate we need to get rid of these duplicate records keeping only one of them in the table. In this tip I discuss different strategies which you can take for this, along with the pros and cons.

At our organization we need to check for a minimum amount of free space before proceeding with some processes that run on SQL Server 2000, 2005 and 2008 SQL Server instances. Do you know of a way to find out the free disk space and then fail the process if it does not meet the minimum requirements? Can you provide some sample code?

I've seen so many different options and scripts for determining free space, consumed space, and total space allocated for databases in Microsoft SQL Server. Problem is none ever seem to give me all the information. I need to run one script to see this information for the log file and a different one for the data files. Is there anything out there that provides this information universally?

I am running very low on storage to support my SQL Server 2000 user defined databases on a number of my production SQL Server instances. We have made the decision to invest in some sort of NAS\SAN or direct attached storage solution. Independent of which technology, vendor and RAID sets we select, I need to plan for my database storage for the next 18 to 24 months and beyond. I have seen your script for capacity planning at a table level (Easing the Capacity Planning Burden), but how can I roll this up at a database level? I need to determine the storage needs at a database and SQL Server instance.

One of the advantages of using the Bulk-Logged recovery model is to minimally log bulk load commands, but still have other transactions fully logged. You may find that when you set your recovery model to Bulk-Logged or to Simple and you run your bulk load commands that the size of your transaction log still grows quite large. The reason for this is that there are some other settings and criteria that need to be met in order to minimally log the bulk insert commands and to minimize the amount of space needed in your transaction log.