Shutdown Event Tracker on Windows Server 2003

Windows server 2003 requests a reason when you try to shutdown your server. For a development or Test environment, this process consumes some times as multiple restart of a server is necessary. To stop the above process, kindly follow the steps mentioned below:

• Click Start-->Run, and type gpedit.msc and press Enter.
• Expand Computer Configuration -->Administrative Templetes. Click on the System Object.
• Locate and double-click the Shutdown Event Tracker Settings. Click on the disable button to disable the Shutdown event tracker. Click OK and close the Group Policy Editor console.

When you shut down your server, you won't be asked to enter a reason.

Restoring Active Directory on Windows Server 2003

• When you restore the Active Directory, you can only restore all the system state data which was backed up, including the registry, the COM+ class registration database, system boot files, files under Windows NT protection, the sysvol and Active Directory (only if the server is a DC) and the Certificate Service Database ( if the Certificate Service is installed on the same server). You can not restore only the indivisual component of the system state data.


• You must be a member of the Administrator group or Backup Operator group to perform the Active Directory Restore.


• Active directory restoration can be performed in one of the two ways: They are: Authoratative mode and Non-authoratative mode.


• The default mode of restoring the system state data to a domain controller is Non-authoratative.


• In a Non-authoratative restore, the distributed services on a domain controller are restored from backup media and the restored data is then updated through normal replication. In this case, after you restored the domain controller, any changes made to the server after the backup, will be updated from other servers. Non-authoratative restore is typically performed after a domain controller is completely failed due to hardware or software failure.


• An Authoratative Restore brings a domain or a container back to the state it was in at the time of backup and overwrites all changes made since last backup. If you don't want to replicate the changes that have been made subsequent to the last backup operation, you must perform an authoratative restore. To authoratatively restore Active Directory data, you must run Ntdsutil utility after you performed a nonauthoratative restore of the system state data but before you restart your server.


• When you restart your computer in directory service restore mode, you must log on as an administrator by using the valid Secutiry Account Manager (SAM) account name and password, not the active directory administrator's name and password. This is because Active Directory is offline and account verification cannot occur. Rather, the SAM accounts database is used to control access to Active Directory while it is offline.
• By default, passwords are reset after seven days; except for computer accounts. The previous password is also maintained. Therefore, performing authoratative restore with a backup older than 14 days can affect the trust relationship.

Backing Up Active Directory on a Windows Server 2003 domain

• You must be a member of administrator or Backup Operator group to initiate the Active Directory backup on a Domain Controller running on Windows Server 2003.


• When you backup Active Directory using Backup Or Restore wizard, the wizard automatically backs up all the system components and all the distributed services that Active Directory requires. Collectively, these components and services are known as system state data.


• System state data backup is only possible in local server, not on a remote server.

Interview Questions for SQL DBA

I have added some of the asked questions related to SQL Servers. Please mention your comments and suggestions to improve the same and help other to clear the interview related to SQL Support.

1. What are difference between Delete and Truncate command?

Ans: Delete command removes the rows from a table based on the condition we put in the where clause. Truncate command removes all the rows the table immediately.

DELETE:

• Delete is slower as it writes the entries in transaction log.
• Delete removes one row at a time from the table and records an entry of the deleted rows in the transaction log.
• The identity column is not reset while we use Delete command.
• Delete can be used with/without the where clause.
• Delete statement can be rolled back.
• Delete statement is a DML statement.

TRUNCATE

• Truncate is faster and uses fewer system resources and transaction log.
• Truncate removes the data by deallocating the space and only deallocation of space is written in the transaction log.
• Truncate removes all rows from the table and doesn't use the where clause.
• The identity column is reset to the seed value while we use Truncate command.
• Truncate statement can not be rolled back.
• Truncate is a DDL statement.
  

2. What are the various types of Recovery models available in Ms SQL Servers?

Ans: There are three types of Recovery models available with SQL Server 2005. They are:

1. Full Recovery Model
2. Bulk-logged Recovery Model
3. Simple Recovery Model
   

3. What are the different types of Backups available in SQL Server?

Ans: The various types of Backups are:

• Full Backup
• Differential Backup
  
• Transaction Log Backup
  

4. How to move a database from Server A to Server B with least administrative access?

Ans: There are various methods to copy the database from one server to another. But using the system stored procedures sp_detachdb and sp_attachdb you can copy the database with least administrative access. The following are the syntax to follow up:
1)-- To remove the server from the database
sp_detachdb 'databasename';
GO
2) Copy the database files (.mdf & .ldf) from Server A to Server B.
3) -- To attach the database to the server B, use the following command.
sp_attachdb '.mdf file location of the database';
GO

5. How to copy the user-login details from one database to another database?

6. How to reduce the size of the tempdb?
Ans: Sometimes, due to badly written queries, the size of the tempdb database in your SQL Server may increased a lot. There are various ways to reduce the size of the tempdb. I am addressing one of the way to do it.
Restart your SQL Server and execute the following statements:
-- To bring the server into Single User mode
sp_dboption 'tempdb', 'Single User', 'TRUE';
GO
USE tempdb
GO
-- The following statement reduce the size of the data file
dbcc shrinkfile (tempdev, 'target size in MB');
GO
-- The following statement reduce the size of the log file
dbcc shrinkfile (templog, 'target size in MB');
GO
-- The following statement change the database from Single User mode to Multi User mode sp_dboption 'tempdb', 'Single User', 'FALSE';
GO

7. You are administering a database Customers on SQL Server. You have scheduled a backup plan for the database Customers. The plan is as follows: Full backup on Monday morning at 5 am Differential backup on rest of the day at 11pm Transactional Log backup after every hour. Your database server crashed on Wednesday morning at 11:15am due to a hardware failure. How do you bring your database server online ASAP?

Ans: As describe in the above scenario, the database server failed at 11:15am on a Wednesday. So you have one full backup, which was taken on Monday morning, two differential backups taken on Monday and Tuesday at 11pm, and transactional log backup every hour up to 11am on Wednesday. First you have to replace the hardware which failed, the restart your server. Than take a backup of the transactional log. This log contains the details of all the transactions after the last transaction backup. This is also known as Tail log backup. Now we have all the restore available to backup our server up to the time of failure i.e. 11:15am on Wednesday.

Follow the steps mentioned below to restore the database:
1. Restore the full backup of the database.
2. Restore the differential log backup of Tuesday.
3. Restore all the transaction log backup( first at 12am, then 1am, then 2am....up to 11am)
4. Restore the tail backup which you took after rebooting your server, after 11:15am.

Now the Customers database is restored up to the time of failure.

8. How to start the SQL Server in Single User Mode?

Ans: We can bring a SQL Server in Single User mode by using the query analyzer and by using Management Studio.
Using Query Analyzer:
--Use the following system stored procedure, we can change a database to Single User mode. sp_dboption 'databasename', 'Single User', 'TRUE';
GO
Using Management Studio: check the msdn article on this at; http://msdn.microsoft.com/en-us/library/ms345598.aspx

9. How many indexes can be created on a table?

Ans: We can create one Clustered index and 249 non-clustered index on a table.

10.Difference between Primary Key and Unique Key?

Ans: Primary Keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Primay Key, by default, creates Clustered index.

As compared to Primary Key, Unique Keys are also the unique identifiers for each row. Unique key contains maximum one Null value. Unique key creates non-clustered index.

11. How to migrate a database?

12. What are the different type of authentication mode available in SQL Server?

Ans: There are two types of authentication mode available in SQL Server. They are:

• Windows Authentication
• Mixed Mode Authentication

In windows authentication, the user account created in Windows NT4.0/2000/2003, is used to login to SQL Server. When the user tries to connect to the SQL Server, SQL Server will check the user's account details with the AD, and provide access to SQL Server, with the required permission assigned to the user.

In Mixed mode authentication, the user required an windows account to login to the system and a sql login account to login to the database.

For more information, please visit the MSDN website: http://msdn.microsoft.com/en-us/library/aa905171.aspx

13. How to check the database performance?

14. What is the difference between Physical Database and Logical Database?

15. Difference Between Clustered and Non-clustered index?

Ans: A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

16. On which port, SQL Server listen to?

Ans: SQL Server listen on TCP port 1433.

17. Where is the sql server users and password information stored in SQL Server?

Ans: In SQL Server 2000, its stored in sysxlogin table and in SQL Server 2005, its stored on sys.syslogins table

18. What is Database Mirroring?

Ans: Database Mirroring is a disaster recovery method used in SQL Server. In Database Mirroring, we create a mirror of a existing database, which contain the exact copy of the database. In case of a disaster, the mirrored database can be use to serve the request.

19. What is Database Isolation Level?

20. What is Statistics in SQL Server?

Defination of Endpoints

An endpoint is the connecting point between two services in the communication architecture and is addressed by a protocol and an IP port number. We need to specify endpoint when setting up communication between two broker services.

There are three types of endpoints you can use in SQL Server 2005. They are:

Service Broker endpoint SQL Server uses Service Broker endpoints for Service Broker communication

outside the SQL Server instance. An endpoint is a SQL Server object that represents the capability for SQL Server to communicate over the network, so a Service Broker endpoint configures SQL Server to send and receive Service Broker messages over the network.

Service Broker endpoints provide options for transport security and message forwarding. A Service Broker endpoint listens on a specific TCP port number. By default, an instance of SQL Server does not contain a Service Broker endpoint. You must create a Service Broker endpoint to send or receive messages outside the SQL Server instance.

Mirror endpoints For database mirroring, a server instance requires its own, dedicated database-mirroring endpoint. Mirror endpoints are special-purpose endpoints used exclusively to receive database-mirroring connections from other server instances. The same as broker instance endpoints, database-mirroring endpoints use TCP to send and receive messages between the server instances in database-mirroring sessions. Each configured database mirror endpoint exclusively uses a TCP-specific port number.

HTTP endpoints SQL Server exposes native XML web services through the database engine by configuring and creating HTTP endpoints. To work enable native calls, SQL Server will request and register with the HTTP listener http.sys, which is available only on Windows Server 2003 and Windows XP Service Pack 2. This means SQL Server uses XML and HTTP to access services and objects regardless of the client software used.

SQL Server uses SOAP message requests to an instance of SQL Server over HTTP to provide

the following:

· Access to T-SQL batch statements, with or without parameters

· Access to stored procedures, extended stored procedures, and scalar-valued user-defined functions

The key point to remember is that SQL Server does not use Internet Information Services (IIS) but instead directly registers with http.sys. When setting up a web service that can listen natively for HTTP SOAP request, you have to configure this endpoint using the CREATE ENDPOINT statement.

Operation Master Roles of Active Directory

In Windows NT 4.0-style domains, we have only one PDC (Primary Domain Controller) which has accepted any directory object modifications and any number of BDCs (Backup Domain Controllers)that held read-only copies of the accounts database.BDCs could authenticate users, but any changes to any attributes of domain accounts had to take place in direct communication with the PDC. Since the PDC pushed out copies of the accounts database, known as SAM database, to the BDCs for a domain, this sort of replication was known as Single-master replication.

Active Directory concepts in Windows 2000 Server and Windows Server 2003, changes the above drawbacks of NT 4.0 domains. Unless, your domain is functioning at the NT interim functional level, all domain controllers for a domain can accept changes for data in their domain, and domain controllers have peers to which, they can replicate changes to those objects. This type of setup is called as Multimaster Replication as each domain controller acts as a master, passing changes to other domain controllers until those changes are replicated fully.

Since some domain controllers need to have greater control over others, Microsoft took care of this problem by implementing special roles for some domain controllers in Active Directory, called Operations Master Roles (also know as Flexible Single master of Operation (FSMO). There are five specific operation master roles. They are:

1. Schema Master (one per forest)
2. Domain Naming Master (one per forest)
3. RID Emulator/Pool (one per domain)
4. PDC Emulator (one per domain)
5. Infrastructure Master (one per domain)

Note: The first domain controller in a forest assumes all five roles simultaneously. The first domain controller in the second domain of a forest assumes all three domain-specific roles simultaneously. Organizations with only one domain controller have all five roles on that one domain controller.

Schema Master:

The Schema Master in a forest ensures that changes to the schema, or to the actual structure of the Active directory database, are made in a consistent manner. The active directory and global catalog are tangelled. The global catalog contains a subset of information from all domains within a forest. If you will added new attributes to the schema and wanted to include those in the golbal catalog, all your domain controllers that act as global catalog servers will need to received the change.

For Windows 2000-based DCs, the entire global catalog must be flushed and rebuilt on each domain controller; however for Windows Server 2003-based domain controllers, only the changes needs to be propagated.

The Schema Master role is one of the forest-specific roles, that only one domain controller in the entire forest can have.

Domain Naming Master:

The Domain Naming Master role is one of the forest-specific roles, that only one domain controller in the entire forest can have. This role protects aganist the creation of identically named domains in the same forest. The Domain Naming Master role is designed to be placed on a global catalog server on Windows 2000 forest. This role uses some information contained in the GC (Global Catalog) to fulfill its responsibilities. However in the Windows Server 2003 forest functional level, this placement is unnecessary.

To change the domain-naming master role. you must be a member of the Enterprise Admins group. Use the Active Directory Domains and Trusts tool to change the domain-naming master role.

RID Master:

The RID Master role handles the assignment and distribution of the latter portion of SIDs for objects within Active Directory. Every object in Windows is assigned a unique SID. The SID comes in the form of S-1-5-21-A-B-C-RID, where the S-1-5-21 is common to all SIDs. The "A, B, and C" parts of the number randomly generated 32-bit numbers that are specific to a domain. The RID, or relative identifier, part of the SID is another 32-bit number that is the unique part of the SID and identifies a distinct object in the directory.

The domain controller with the RID master role distributes groups of 500 unique RIDs to its brother and sister domain controllers with the domain, so that they can create unique objects.No two DCs have the same groups of RIDs to assign.

PDC Emulator:

The PDC emulator performs one of the two different roles, depending on how AD is implemented:

When AD is configured to interact with Windows NT 4.0 BDCs, or to interact with computers that don't have Windows 2000 Directory Service Client software, AD is said to be operating in mixed-mode.When AD operates in mixed-mode, the PDC emulator acts as a Windows NT PDC. In this situation, the PDC emulator synchronizes with existing Windows NT BDCs. Users or administrators of computers must contact the PDC to make the desired changes, if they are not running the DS client software for Windows 2000.

When AD is configured to interact only with Windows 2000 domain controllers and computers that run Windows 2000 DS client software, AD is said to be operating in native-mode. When operating in native-mode, the PDC emulator receives password changes more quickly than other domain controllers in the domain.

There can be only one PDC emulator in each domain in a forest.

Infrastructure Master:

The infrastructure master helps to speed up propagation and replication of certain pieces of information among the domain controllers. The infrastructure master role is designed to not be on a domain controller functioning as a GC server, unless every domain controller in your domain is a GC server as well, or if you have only one domain.

There can be only one Infrastructure master in each domain in a forest.

SYSPREP Utility in Windows 2000

The System Preparation Tool (sysprep.exe), often called as Sysprep in Microsoft's world, is a Windows 2000/2003 deployment tool designed for large organizations and OEMs (Original Equipments Manufactures). Sysprep prepares a Windows 2000 computer's hard disk for duplication, thus making it possible for that computer's hard disk to be copied to other computers. This feature is useful to install Windows 2000 along with custom applications and other data on multiple computers.

The important point to be remembered in Sysprep is "Sysprep works on both Windows 2000 Professional and Server computers, however, it doesn't work on Windows 2000 Server domain controllers."

Sysprep.exe is located in the Windows installation cd (\SUPPORT\TOOLS). In this folder, you will find a file name Deploy.cab and you have to extract this file to find the sysprep.exe utility.

How Sysprep works?

• First, you have to install Windows 2000 and all required applications and services on a computer. This computer is also known as Master Computer.
• Then you have to prepare the master computer's hard disk for duplication by using Sysprep. Sysprep works by removing user-specific data from the original master computer and by placing a Mini-setup routine on the master computer's hard disk.
• Next, use a third party software like Norton Ghost to create an image of the hard disk to copy it on the target machine.
• Finally, when the target computer boots for the first time, a Mini-setup wizard runs to gather user specific information from the user and assigns the target computer with a unique SID.

This topic describe about the SYSPREP utility in Windows 2000 Server. Hope this will help you to recall some details about SYSPREP.