Author Archives: Brumedishetty

About Brumedishetty

I work as a SQL Server DBA. Look at the About Me page for more info

Directory naming convention in SQL Server

Posted on by
Reply

In this blog we shall take a look at the naming convention of SQL Server Instance directories since SQL Server 2005. By default, SQL Server is installed in C:\Program Files\Microsoft SQL Server directory. The Instance directory is then created depending on the SQL Server Version that is installed. For each SQL Instance installed, there would be a separate directory for that Instance, all the binary files of that instance are stored in that directory.

In SQL Server 2005, the instance directories are named as MSSQL.1, MSSQL.2, MSSQL.3 and so on as new Instances are installed. This is inconvenience to find the instances by looking at the naming of these directories. See below that there are 2 directories MSSQL.1and MSSQL.2 one of them being a default instance and the other one is obviously a named instance.

Where as starting from SQL Server 2008, the naming of the directories has been changed to reflect the Instance they belong to. Instance directory names in SQL 2008 start with MSSQL10. followed by the instance name. See that there are 2 directories named MSSQL10.SQL2008INSTANCE1 and MSSQL10.SQL2008INSTANCE2. 

This naming convention has continued in the next releases of SQL Server, such as SQL 2008 R2 (which is often referred as 10.5) hence the naming is MSSQL10_5.xyz and finally in SQL Server 2012, the Instance directory is named as MSSQL11.xyz…

Finally this is how the SQL Server configuration Manager looks like on the server. There are total of 6 SQL Server Instances as you can see.

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

Documents to assist you to Migrate non-SQL databases to SQL Server 2008

Posted on by
Reply

If you are planning to migrate other database products such as Oracle, Informix, MySQL, Sybase to SQL Server 2008, you should be reading the white papers available on this link. SQL Server 2008 Migration White Papers from Microsoft Download Center.

The documents will provide an overview of the non-SQL Server database Migration to SQL Server 2008 and how the SQL Server Migration Assistant (SSMA) will assist in conversion of those database objects to SQL Server database objects. What data types would be a good match on SQL Server. 

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 Post Installation steps

Posted on by
1

From time to time, organizations move from an old version to a newer version as the application using the databases keep changing and become more complex. When they move to the newer platform, eventually the database versions have to be migrated / upgraded to the newer version. All these results in setting up a new SQL Server Instance by Installing SQL Server Database and other required services.   

In this blog we shall look at some of the post installation steps performed on a newly installed SQL Server.

  • Check all the necessary Protocols are enabled except VIA (unless you are absolutely sure you need VIA).
  • Make sure that you can connect to the SQL Instance from a remote system. That way you are sure your firewall is not blocking etc..
  • Configure the Backup Compression option on the SQL Instance. (If you intend to use it and your SQL Server Version is 2008 or above)
  • Configure Database Mail.
  • Configure all Backups. (Full, Differential and Log) SQL Server Maintenance Plan is the the quickest way to get started.
  • Make sure the SQL Server Authentication Mode is set to the mode that you wanted, be it Windows Only OR Mixed Mode.
  • Set the minimum and maximum Memory settings for the SQL Instance. Remember that enable AWE is not required for 64 bit servers. (Read blog here…)
  • Assign Lock Pages in Memory to the service account under which SQL Server is running. (Read blog here…)
  • Configure startup modes for SQL Agent, SQL Server Database Engine, Integration Services, Analysis Services, Reporting Services, Full-Text and SQL Browser.
  • Create maintenance plans to manage / purge old backup files.
  • Create linked servers that are required (especially in case where you are Upgrading / Migrating)
  • Recreate the SQL Agent jobs you had on the old SQL Server (if you are doing a Upgrade/Migration).
  • Configure Reporting Services (if install only option was chosen).
  • Document everything that was performed on that Server, over a period of time you might not remember every step or change made to that Instance.
Note: All of the above steps might not be necessary in every instance of SQL Server Installation. This is an attempt to make a check list of Post Installation steps..
Update: Links to related posts are added as and when there is a blog post published..
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