Category Archives: SQL Server Administration

What’s new in Installing SQL Server 2012

Posted on by
7

You might be aware about SQL Server 2012, the next version of SQL Server. As always, there are a lot of new enhancements features announced in SQL Server 2012. Covering all of the new features and enhancements in SQL Server 2012 in detail might not be accomplished in a single blog. Hence I have decided to break that down to a series of blogs. So in this blog we shall look at some changes new additions / deprecations which are related to installing SQL Server 2012.

The following are the changes in installation of SQL Server 2012. 

  • Changes in the Editions, (BI Edition introduced and Datacenter Edition discontinued), I’ve already written a blog on this, read it here..
  • Business Intelligence Development Studio (BIDS) is gone, making way to SQL Server Data Tools (SSDT)
  • Installation of Data Quality Services (DQS) can be done from the SQL Server setup itself.
  • SQL Server failover clustering supports the nodes being in two different subnets, called as SQL Server multi-subnet clustering.
  • SQL Server editions are no more available for Itanium based systems, so if you don’t see I64 directory in the installation software don’t panic.
  • All Operating Systems would need at least SP1 to be installed (Windows 7 and Windows Server 2008 and R2)
  • All applicable updates can be installed along with the main product, so that your SQL Server product is up to date at the time of installation..

Note: The information in this blog is up to date as of SQL Server 2012 Release Candidate 0 and might change at a later time, visit Microsoft SQL Server official site for more updates.

Updated on 11/20/2011: Read Step by Step Installation of SQL Server 2012 RC 0

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

SQL Server Database Backup types and why are they required

Posted on by
Reply

Database Backups are very important and there is not much to say why are they important; that is the only way to restore the data back to a point of time. In this blog we shall learn the different database backup types available in SQL Server. There are 3 basic types of backups (there are other types too) in SQL Server which every aspiring SQL Server Database Admin should be aware. They are

  • Full
  • Differential  and
  • Transaction Log

Let us look at more details about these backup types.

Full Backup: Full backup is perhaps the most basic type of the backup in SQL Server and the important too. Full backup contains the complete database, including the data (in the tables) and other database objects such as Indexes, Stored Procs, Triggers etc.. Using this backup, the database can be restored (or recovered) until the point-in-time when the Full backup was completed. The advantage with Full backups is that they are completely independent of any other backups, unlike the other 2 types of backups. Since a Full backup will contain the entire database, the size of the backup will be proportionate to the actual backup size (when the backup is not compressed, a feature released in SQL Server 2008).  The same is true with the amount of time it takes to perform a Full backup, the larger the database, greater the time. Any kind of database restore should always begin with a Full backup. These backups can be taken on a database with any of the recovery model.

Differential Backup: Differential backups contain all database changes made since the last Full backup. A differential backup taken a day after the Full backup will contain all the database changes during that one day. A differential backup taken 2 days after the Full backup will contain all the changes during those 2 days. Due to this, Differential backups can be called as cumulative in nature. A recent differential backup contains the changes that were already backed up by a previous differential backup. As more and more data is modified, the Differential backups size increases. A Differential backup cannot be restored separately, instead it should always be used along with a good Full backup set (that is, the most recent Full backup preceding the Differential backup). Differential backups can be taken on a database with any of the recovery model.

Transaction Log: Transaction Log backup also called as Log backup contains all the changes that have been made since the last Full backup or a previous Log backup. Transaction log backups are opposite to Differential backups, i.e, you need each and every transaction log backup, before a specific point-in-time. Maintaining and protecting all transaction log backups is vital. A single log backup missing in a chain can break the ability to restore the database to the most recent point-in-time. Unlike Full and Differential backups, the benefit of Transaction log backups is that they can help us restore to a specific point in time. Log backups are generally scheduled to run at a meaningful short interval such as every 10 or 15 minutes. Log backups are available for only those database which have the recovery model set to Full or Bulk-Logged. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

How to find the last time SQL Server was started? Part l

Posted on by
2

In this blog, let’s take a look at different ways we can check the date and time SQL Server was started last time. There are multiple ways to do it and each of them has its pros and cons.

Using TempDB

The first method we shall look is to see when was the TempDB created. If you recollect,  one of the first things you have learnt about TempDB is it a database used to store objects temporarily and is refreshed/ recreated every time SQL Server is started. Here we would use the creation time of TempDB to determine when was the SQL Server started last time. To do this, open SQL Server Management Studio, go to object explorer, expand databases node/folder. Expand System databases and right-click on Tempdb as shown below and choose properties. 

Database properties window of Tempdb is displayed. In the very first screen when the General page is selected you can notice the Date Created column (highlighted below)…

This way of finding information from Tempdb creation is the easiest way I feel. This step is

SQL Server Error Log

Another way of finding the same information is from the SQL Server Error Logs. This is also one of the easier ways of finding the SQL Server start time, but can be tricky sometimes, which I shall explain in a while..

First let’s take a look at the steps to perform this task. In object explorer, expand the Management node and find SQL Server Logs. Right click on SQL Server Logs and go to View –> SQL Server Log (as shown below). These SQL Server Logs contain very important information regarding that SQL Server. Every time SQL Server is started, entries are written in the SQL Server Log.

When the entries in Error Log are displayed, you can scroll down to the row which contains the SQL Startup information. In the picture below, look at the entry at 8/27/2011 7:48:06 PM.

It looks like you could find the startup information in SQL Error Log easily, but in some cases it might not be that way. Every time a database backup is performed, there is an entry in the SQL Server Error Log which on an SQL Instance with multiple databases will result in a large number of entries and it would make it difficult to find this information easily. Also, in some cases the Error Log would be regularly recycled in order to ensure that it is easier to read the Error Log and once the maximum number of Error Logs are reached, SQL Server disposes the oldest Error Log when it has to create a new one.

There are other ways to find the SQL Server startup time, which we shall look in my next blog.

Part 2 of this blog can be found here How to find the last time SQL Server was started? Part ll

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty