General discussion of SQL Topics; aimed for the novice-intermediate level Microsoft SQL Server User. Currently focuses on using SQL Server 2005.

Tuesday, June 2, 2009

Backing up directly to a network share

Over the past few weeks I’ve seen many users who’ve complained that they can’t directly backup to a network share drive. They’ve all expressed, at one point or another, how disappointing this is for a SQL Server product that boasts about best practices, high availability, and security…yet, you have to backup locally then use a separate script to get the backup file to a network share (seems a bit of a risk involved for the brief time the backup file is stored locally; as well as using up precious local resources to do all of this).

Well, I finally intervened in one of those discussions and pointed out that SQL in fact can backup directly to a network share. In fact there are multiple methods that can be performed to accomplish this simple feat! First, you must create a “backup device” that is located on a network share and backup to that device, this is done by one of two ways you can specify to backup to a “disk” (or file) and point to a network share drive/location. Or you may create a backup “media set” which is stored on the network share. These are just two of the methods to accomplish this, which are easy enough to perform; as well as documented in Books Online (BOL). I’ll briefly cover this method here; you’ll want to refer to BOL to find more detail on it, as well as other possible methods.

Some terminology to help…

NOTE: Be sure to understand the differences in terminology here. Notice that I’ve quoted “backup device” and “media set”.

A “backup device” is not necessarily a physical device (i.e. tape drive, DVD media, etc). A “backup device” is a virtual device to SQL, meaning that you tell SQL if the ‘device’ is a “DISK” or “TAPE” (understand that DVD-ROM, USB, etc are considered “TAPE” devices in this context). Depending on the ‘device’ you choose will depend on where SQL stores the backup.

A “media set” is a logical name of a collection of (or a single) “backup devices”. This means that you could have multiple locations on a “DISK” to where SQL stores the backup (this can be used for striping, to increase your speed of backups…as well as to increase your data integrity capabilities).

See the “References” section for BOL definition and details on both “Backup Devices” and “Media Sets”.

Network Share Backup Requirements…

First things first, if I’m pointing out that it’s relatively simple enough to backup to a network share, then why are there so many people claiming they can’t (or believe that SQL can’t)? Well, as with all other features in SQL…most of the problem is either permissions or lack of knowledge on how to implement (accomplish) the required task.

Now, to make this all work you need two ensure two things happen.

1) You have to know how to tell SQL to make the backup to the network share. As mentioned earlier you need to specify a “backup device” located on the network share. Here are the links in BOL describing this in detail. Note: There is a section in BOL dedicated to this subject, review it!

2) Ensure that the network share you are attempting to make the backup to can actually be seen and written to. A simple test is to open up SQL Server Management Studio (SSMS) and attempt to backup the database using the “Back up…” task wizard. You’ll want to backup to “DISK” and then browse to the network share location, or enter the Universal Naming Convention (UNC) path. If you can successfully backup to the network share through SSMS, then you know at a minimum that you have the permission with the account you logged into SSMS with to make the backup to the network share. Even if you can’t pass this test, it doesn’t necessarily mean SQL can’t backup to the shared drive; scheduled jobs and various other methods to create regular backups often times will use different credentials than your local account uses. Regardless you’ll probably want to look into the permissions you are given for the folder you are trying to backup to, or discuss this with the responsible party that sets up the shared folder permissions to ensure that the appropriate SQL services has the required credentials. Now you either will want to set up your back up to use the proper credentials, or ensure that the proper SQL Service account has the permissions to write to this folder (check BOL for service account information for SQL Server).

Performing the backup…

The following is a quick script to backup a sample database called “Backup_Testing” to a folder called “Backups” on a server called “DEV-CLUS-07”. This is a “script” created after using the task “Back up…” in SSMS:

Backup to Network Share using UNC path (T-SQL example):
BACKUP DATABASE [Backup_Testing] 
TO  DISK =
N'\\DEV-CLUS-07\Backups\NetShare01.bak' 
WITH  DESCRIPTION = N'Backing up the database to a network share drive',
 
NOFORMAT, INIT,  NAME = N'Backup_Testing_Full_DB_BkUp_to_Network_Share',
 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

GO

Notice that the strings are prefixed with “N” to designate unicode strings; this is an important part to use when specifying the UNC directly.

Now, instead of entering the UNC each time you want to create a backup you can create a “backup device” that specifies the UNC path for you; the drawback is that you must also specify the file name. This is ideal if you want to continuously use the same file over and over, and especially if you make heavy use of expirations for your backups and or want multiple choices of backups in a single file location (maybe for easier file administration).

You first create the “backup device”. This is a backup device called “Network_Share_Device_01”, located in the same location as the previous example (except the file name has been also specified, which is “NetShareDev01.bak”).

Create a “backup device” that points to a network share location (T-SQL example):

USE [master]
GO
EXEC master.dbo.sp_addumpdevice    @devtype = N'disk', 
                               
@logicalname = N'Network_Share_Device_01',
 
                               
@physicalname =
N'\\DEV-CLUS-07\Backups\NetShare01.bak'
GO

With this device in place you would use the same T-SQL as the first example; except instead of using the “TO DISK” option you’d use the “TO [device_name_here]” option…

Backup to Network Share using backup device (T-SQL example):

BACKUP DATABASE [Backup_Testing] 
TO
[Network_Share_Device_01]
WITH  DESCRIPTION = N'Backing up the database to a network share drive',
 
NOFORMAT, INIT,  NAME = N'Backup_Testing_Full_DB_BkUp_to_Network_Share',
 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

GO

Conclusion:

The next time you setup your database backup schedule, remember that you can directly backup to your network share and lower the risks of keeping the backup locally…and especially, lower the overhead of resources used on the local instance of SQL to get the backup to your final destination.

As you can see, the T-SQL is straight forward; the only complication is in the setting up SQL properly to allow you to directly backup to a network share location, and really the only complicated part for the DBA is ensuring the proper permissions (at the OS level) are in place. This only becomes complicated in the scenarios where the DBA is not in control of permissions for the location of the database files and backup files. With some cooperation between database administrators and network administrators this can easily, and painlessly, be overcome.

This is a win-win situation for everyone; it streamlines the process while improving the performance, security, and data integrity of the database!

References:

Microsoft: Backup Devices (SQL 2008) - http://msdn.microsoft.com/en-us/library/ms179313.aspx
Microsoft: Media Sets, Media Families, and Backup Sets - http://msdn.microsoft.com/en-us/library/ms178062.aspx

Tuesday, March 24, 2009

What are Tables in SQL Server?

Overview

The following is a general overview of tables within a SQL Server database; it is by no means a thorough discussion of this subject. This is intended to be a primer for future discussions and covers only the basics, in no particular order.

Table is a database object containing a column definition (or a collection of) in which data is stored row-by-row. You can think of this very similar to a spreadsheet; with a little extra power.

A table is generally designed to handle most relational data in an organized manner; however, SQL Server has evolved to allow handling of data that is not organized.

A table must include the following (at a minimum):

  • A name for the table
  • A column to store the data

A table may include many other attributes/properties, such as:

  • A description of the table
  • A schema the table belongs to
  • An identity column
  • A filegroup the table belongs to
  • Check Constraints, to ensure data integrity
  • Multiple columns
  • Relationships to other tables and/or objects within the database
  • Indexes

CREATE TABLE syntax

The following is the bare minimum T-SQL syntax for creating a table:

CREATE TABLE 
   
[ database_name . [ schema_name ] . | schema_name . ] table_name 
       
( { <column_definition> | <computed_column_definition> 
               
| <column_set_definition> }
       
[ <table_constraint> ] [ ,...n ] ) 
   
[ ON { partition_scheme_name ( partition_column_name ) | filegroup 
       
| "default" } ] 
   
[ { TEXTIMAGE_ON { filegroup | "default" } ] 
   
[ FILESTREAM_ON { partition_scheme_name | filegroup 
       
| "default" } ]
   
[ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]

See the "References" section at the end of this blog for the complete syntax. If you don't fully understand the text/symbols used in this syntax please reference my February 2008 posting Understanding MSDN Syntax Conventions.

Here is a CREATE TABLE command, at it's simplest, issued in T-SQL:

CREATE TABLE myTable
   
(myColumn TINYINT);

This is a very simple T-SQL command that will create a table called "myTable". The table will contain 1 column called "myColumn", this column's data type is "INT" (which is short form for Integer); thus only allowing whole numbers to be entered. The numbers are limited to a range of numbers between 0 and 255.

Columns

A column definition will include the following (at a minimum):

  • The name of the column
  • A data type, and any attributes required to define the data type
  • If it allows NULL values
  • If it is Identity Specific

A column definition may include other attributes/properties, such as:

  • A default value (or binding)
  • A description
  • A computed column specification

Example

As mentioned earlier a table will include, at minimum, a column definition. In most cases you will find multiple column definitions within a table. An example customer table for a small business might include a column indicating the CustomerID, CustomerName, BusinessName, Address, PhoneNumber. The table might be called "Customers". To create this example table the T-SQL command might look like this:

CREATE TABLE Customers
(
   
CustomerID INT PRIMARY KEY NOT NULL,
   
CustomerName VARCHAR(100) NOT NULL,
   
BusinessName VARCHAR(100) NULL,
   
[Address] VARCHAR(500) NULL,
   
PhoneNumber VARCHAR(10) NULL
)

Notice that the column named "Address" is enclosed in square brackets "[]", this is because "Address" is a reserved keyword in Transact-SQL and can only be used as an identifier object if it is called using one of the "Delimited Identifiers".  There are other acceptable delimited identifiers that may be used, you may have to set the SQL Server configuration for quoted_identifiers, or follow other methods to use these. Refer to Books Online (BOL) Delimited Identifiers (see resources at end of posting) for further information.

Also, you may have noticed that this table construction isn't the best there is. Logically the "Address" column currently would contain a complete address in the field (such as: 123 Anywhere St, Anyplace, State, 12345). If we wanted to search this field for all customers that are in the state of California or Hawaii then we would have to search using a LIKE operator (this will be discussed further later); this can take quite some time in SQL depending on how large the table is. Also, if we want to further list our customers by city alphabetically and group them by state then we introduce a complex T-SQL query to obtain this seemingly simple information.

To make this better we would perform what is known as "Normalization" on our tables. Normalization will be discussed in further detail in a future blog. Right now, the grasp of what a table is, what is does, and how the data is stored is the important information to know.

Conclusion

As you can see a table is very much a corner stone to any database, without a table there is no where to store your data in your database. A table can be very simple to setup; while also can be very complex to maintain a great amount of integrity and Business Intelligence rules. This is just a primer discussion, if you want to delve deeper into tables you will want to start your exploration with BOL's Understanding Tables section and follow the appropriate links to further your knowledge. You will find that tables can be very powerful if you setup the structure properly and utilize the appropriate techniques to ensure data integrity. I've already discussed some attributes of tables in earlier postings, such as my postings about Indexes. There is a wealth of knowledge out there on this subject and may seem like you already know most of it, but you will find that there are many aspects of tables that you may not have known about. I encourage you to take a few moments and review the BOL sections mentioned here, at a minimum. You may also want to look into Temporary Tables and for you SQL 2005+ users look into the new data type Table.

References

CREATE TABLE (Transact-SQL): http://technet.microsoft.com/en-us/library/ms174979.aspx
Understanding MSDN Syntax Conventions: http://sqln.blogspot.com/2008/02/understanding-msdn-syntax-conventions.html
TechNet - Tables: http://technet.microsoft.com/en-us/library/ms189104.aspx
Understanding Tables: http://technet.microsoft.com/en-us/library/ms189905.aspx
Creating and Modifying Table Basics: http://msdn.microsoft.com/en-us/library/ms177399.aspx
table (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms175010.aspx

Tuesday, January 6, 2009

New Article: Transaction Log Growth, do you need it?

Yesterday, SQL Server Central had published another article of mine: "Transaction Log Growth, do you need it?". It got some good reviews, and just over 8,000 views before 9 AM; I've been told anything over 4,000 views (total) is pretty good.

Be sure to look it over when you have a chance, it may prove helpful in the future or just might provide you a little more insight about Transaction Logs and how they work with SQL Server and your databases.

Until next time, Happy Coding!!!

 

References:

Article: http://www.sqlservercentral.com/articles/Transaction+Log/65255/
Discussion: http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx