:::: MENU ::::

Posts Categorized / SQL Server Training

  • Dec 14 / 2011
  • 0
DBA Interview questions, dbDigger, Deployment, SQL Server Training, SQL Server versions and editions, Windows Server

SQL Server editions and Windows platform

Each of SQL Server release like SQL Server 2000, SQL Server 2005 and SQL Server 2008 is further categorized in editions based on their features and capabilities. For example, edition of a specific release may be any of following

  • Data center edition
  • Enterprise edition
  • Standard edition
  • Developer edition
  • Express edition
  • Apart from these there may be some other editions like run time edition or web edition. Some editions like Data center edition or enterprise edition can only be installed on Server OS while others may be installed on both server or client OS.
    For windows platform the term Server OS refers to different editions of

  • Windows server 2000
  • Windows server 2003
  • Windows server 2008
  • Windows server 2008 R2
  • While for windows platform, client OS would be any edition of following

  • Windows 2000
  • Windows XP
  • Windows vista
  • Windows 7
  • So while installing any SQL Server version, do consult the requirements related to OS.

    • Dec 13 / 2011
    • 0
    dbDigger, Memory management, SQL Server Training, System Administration, Windows Server

    A broad comparison of PAE and AWE memory extensions

    Physical address extension (PAE) and Address windowing extensions (AWE) both are used to address more than 4GB memory on 32 bit systems. I define both in following lines to remove the confusion regarding this concept

    Physical address extension (PAE)

    PAE is used by any OS to extend more than 4 GB ram for 32 bit systems. 32 bit systems natively cannot utilize more than 4GB of physical memory. As most of the server hardware is with much more memory than 4GB, it is required to make it accessible for 32 bit operating systems. For windows platform PAE is enabled by adding some parameter in boot.ini file.

    Address windowing extensions (AWE)

    AWE is a windows API used for supporting 32 bit applications to access more than 4GB of memory. It may be required for memory extensive software and applications. Read about enabling PAE for SQL Server.

    • Dec 10 / 2011
    • 0
    dbDigger, SQL Server Training, Storage, System Stored Procedures

    Logical and physical file names of SQL Server data and log files

    SQL Server files have two types of file names.

    Logical File Name

    Logical file name of SQL Server data or log file is used to refer the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

    Physical File Name/ OS File Name

    Physical file name is the name of the physical file including the directory path. It belongs to OS  hence must follow the rules for the operating system file names.

    Here is a simple practical to exactly mark both the types.

    Create a database

    When a SQL Server database is created, by default same physical name is granted to the file in folder as that of its logical name. Following T-SQL script would create a database with different and meaningful logical and physical filenames.

    CREATE DATABASE [LogiPhysiFiles]
    ( NAME = N’LogiName_data’,
    FILENAME = N’F:DBs2k5PhysiName_data.mdf’)
    LOG ON
    ( NAME = N’LogiName_log’,
    FILENAME = N’F:DBs2k5PhysiName_log.ldf’)

    A database has been created with following file names

    Logical data file name      = LogiName_data
    Physical data file name     = F:DBs2k5PhysiName_data.mdf
    Logical log file name        = LogiName_log
    Physical data file name     = F:DBs2k5PhysiName_log.ldf

    We may get information about files of this database by using Files option in properties of this database in SSMS

    Logical and physical file names of SQL Server data and log files

    For T-SQL use following script to get files information of this database

    USE [LogiPhysiFiles]
    SELECT Name AS LogicalName, filename AS PhysicalFile
    FROM sys.sysfiles

    Following result shows both types of file names

    Get SQL Server files info through T-SQL

    Or use system stored procedure

    USE [LogiPhysiFiles]
    • Dec 10 / 2011
    • 0
    DBA Interview questions, dbDigger, SQL Server Training, Storage, Transaction log files

    Major types of SQL Server files

    SQL Server databases have three types of files:

    Primary data files

    Primary data file is the starting point of the database. It points towards other files in the database. Every database has one primary data file. Recommended file extension for primary data file is .mdf.

    Secondary data files

    All data files other than the primary data file in a database are secondary data files. These are optional and may be added for further requirements. Recommended extension for secondary data files is .ndf.

    Log files

    Transaction log files hold all the log information used to recover the database and individual transactions.  At least one log file is required for each database. Recovery models of the database affect the growth of log files. Recommended extension for log files is .ldf.
    It is notable that SQL Server does not enforce the recommended extensions, but for proper and standard environment these should not be changed.

    Consult us to explore the Databases. Contact us