In the news, Microsoft has “Velocity”.
In this week’s feature, I’ll give you the first part of a fast introduction to being a DBA.
The web link is on 64-bit architectures and SQL Server,
and the tip this week is on the features in SQL Server Express.
In the News:
In the news, Microsoft has announced project “Velocity”. "Velocity" is a distributed in-memory application cache platform for developing scalable, high-performance applications. "Velocity" can be used to cache any CLR object and provides access through simple APIs. You can read more about it at http://tinyurl.com/5e5bdk.
You may have been given a database system to manage, or perhaps you have an assignment that requires you to take on a system. Perhaps you’re brand-new to database systems in general. In any case, this podcast will give you a crash course in what you need to know if you don’t know anything about SQL Server. If you’re already a DBA, this might be a great podcast to pass along to someone that is brand new to the job.
Before we begin, I want to make sure you understand that this quick podcast won’t make you an expert. SQL Server is a huge platform, and requires a long time to learn well. That being said, there are some things that you can learn quickly, and I’ll give you lots of links that might be helpful along the way.
First, what is SQL Server? It’s a Relational Database Management System, called an RDBMS that accepts and stores data. It can run on everything from a Personal Digital Assistant to multi-processor servers, with the larger systems having more capability and more features.
There are several versions of SQL Server, with the most common being version 2000 or 2005. I’ll focus on the 2005 versions in this podcast, but most of the information holds for both versions. There are also “editions” of SQL Server, which just have to do with the features installed and the limitations imposed.
The physical parts of SQL Server involve a set of service accounts, some registry entries, the files that run the system and the data files it stores.
Let’s talk about the services. SQL Server can be installed more than once. Each installation of SQL Server is called an “Instance”. There can be one “default” instance, which is known by the name of the system where it is installed (such as “ServerA”) and then all other installations are called “named instances”, and are referred to by the system’s name, a backward-slash (\) and then the name of the instance, like this: “ServerA\SQLInstall2”. That means that the instance name is SQLInstall2 and the server name is ServerA. Each instance will have a set of services associated with it – the default instance service will be named “MSSQLSERVER”, and the named instances start the same way and are followed by a “$” and the name of the instance. There are other services associated with SQL Server, but the one that you want to focus on is called “SQLServerAgent”, because it starts automated processes like maintenance plans and so on. The important thing to know here is that these services should have a user account, and the user should have the correct permissions, which you can find at http://tinyurl.com/5s644r.
Speaking of permissions, you have two options in SQL Server. The first is to use Windows accounts to allow access to the SQL Server instance and to databases inside that instance. The second option is to create the accounts in SQL Server to access the instance and to the databases, which do not have to exist in Windows. SQL Server also has two sets of groups: one for the instance, called “Server Roles”, and the other in each database, called “database roles”. The important thing to know here is that whether the account is created in Windows or SQL Server the two logins are separate: one for the instance and then one for each database the user needs access to. You can read more about that at http://tinyurl.com/6j68r8.
Now let’s talk about the tools you have to work with in SQL Server 2005. The primary management tool is called “SQL Server Management Studio”, which you’ll hear called SSMS or Management Studio. It’s available in the Windows Start menu called “Microsoft SQL Server 2005”. When you start it, you’ll be prompted for the name of an instance and your login credentials. By default, the System Administrator roles should be granted access into SQL Server, but if not, you’ll need to locate the person who installed the server to see which account they tagged to be the “sa” account – which is the most powerful account in SQL Server.
Once inside, you have objects on the left-hand side of the screen, and an output area on the right. You can right-click any item on the tree on the left to see what actions you can take on it, and you can also click the “New Query” button in the top menu bar to bring up an area to type commands.
The commands you can type in a query window are called “Transact-SQL” or T-SQL. At any point in SSMS you can hit F1 to get to help, and from there you’ll find tutorials, references, indexes and help searches. The help for SQL Server is called Books Online, and it’s also on the Start menu in Windows.
There are several other management tools which you can read about in Books Online.
If you have just inherited a database system, the first thing you should do is try to connect to the instance and find out if there are any databases that have not been backed up recently. Backups are a way of copying the data in the database out to a file so that it can be restored in case of an emergency. If any databases haven’t been backed up, you should do that right away. You can back up a SQL Server database to a tape drive, a local hard drive file, or a remote file share on a network. You need to make sure that the database you want to back up will fit on the device where you want to put it. Here’s how you do all that:
The quickest way to explain this process is to use the commands in Transact-SQL to find the databases that haven’t been backed up, find out their sizes, and back them up to a hard drive location or a network share. I’ll explain the graphical tutorial and working with tape drives for another podcast.
If you’re not at a place where you can write down these statements; don’t worry. They are available at my blog, which I’ll talk about at the end of this podcast.
First, let’s find the databases on your system that haven’t been backed up. You can do that with this set of statements:
select database_name, backup_finish_date
order by backup_finish_date asc
Now let’s take one of those databases and see how large it is with this statement:
Finally, let’s take a backup of that database and send it on to a share on the network:
BACKUP DATABASE databasename TO DISK = '\\servername\sharename\filename.bak'
In the next podcast I’ll continue this topic. Stay tuned!
64-bit servers are the most common type available today. SQL Server can take advantage of 64-bit architectures, and you can read more about that at http://tinyurl.com/5tjd7y.
Tip of the Week:
SQL Server Express is a great alternative to the other free database editions out there. It does have limitations, however, even beyond the hardware and database sizes. You need to know these limitations to properly choose the back-end for your applications. Learn more about what does and doesn’t come in the box for SQL Server Express at http://tinyurl.com/6k7x54.