How to change interval for Is-alive?

Solution

Microsoft Cluster Server (MSCS) uses poll intervals to determine the status of the resources it is protecting. The "Looks Alive" poll occurs frequently, performing a quick check to verify that the resource is still running, while the "Is Alive" poll performs a more comprehensive check of the resource and occurs less frequently.

Please follow the steps below to change the default poll intervals for a resource type in a cluster:

1. Open the ClusterX client, and highlight the cluster with the resource type to be changed (see Figure 1).
2. Select the "Configuration" view. This will show a list of every object contained in the cluster, including the resource types (see Figure 2).
3. Highlight the resource type that is to be changed, right-click, and select "Properties..."
4. Change to the "Poll Interval" tab (see Figure 3).
5. Make the appropriate poll interval changes, and click OK.

Figure 1:

Figure 2:

Figure 3

 

Looks Alive Vs Is Alive

Looks Alive 

check:Looks alive check is a basic resource health check to verify that the service(SQL service in our context) is running properly. To perform this , cluster service queries the windows service control manager to check the status of the service. By default looks alive check will happen in every five seconds.

Is Alive check: 

An exhaustive check to verify that a resource is running properly. If this check fails, the resource is moved offline and the failover process is triggered. During the Is alive check the cluster service connects to the SQL server instance and execute select @@SERVERNAME. It will check only the SQL server instance availability and does not check the availability of user databases.

IBM:


1.     How many IP’s are required for 2 node cluster?

Ans: 6

2.     Brief about IP’s usage?

Ans: 6 IP (  2 external IP (public), 4 Private IP)

3.     Scenario: My SQL server instances are working fine on Node A but instances are not failover from Node B, what could be the reason?

Ans: Network down

4.     What are differences between SQL 2005 and 2008 w.r.t Cluster?

Ans:

5.     What are the differences between SQL 2005 and SQL 2008 installation wise?

Ans:

6.     What are the agents created when we configure Transactional replication?

Ans: snapshot agent, distribution agent

7.     How to trouble shoot if Primary key violation is happened in Transactional replication?

Ans: sp_browsereplcommands

8.     How transactions are move between Publisher to Distributor and Distributor to Subscriber?

Ans:

9.     What is difference between Transactional replication and Merge replication?

Ans:

10.  What is diff between Log shipping and Mirroring?

Ans:

               With Log Shipping:

Data Transfer:    T-Logs are backed up and transferred to secondary server
Transactional Consistency:  All committed and un-committed are transferred
Server Limitation:   Can be applied to multiple stand-by servers
Failover:   Manual
Failover Duration:  Can take more than 30 mins
Role Change:   Role change is manual
Client Re-direction:  Manual changes required

With Database Mirroring:

Data Transfer:    Individual T-Log records are transferred using TCP endpoints
Transactional Consistency:  Only committed transactions are transferred
Server Limitation:   Can be applied to only one mirror server
Failover:   Automatic
Failover Duration:  Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change:   Role change is fully automatic
Client Re-direction:  Fully automatic as it uses .NET 2.0
11.  I want to give permissions to only one job for creation/deletion of that job only, out of 100’s of jobs, how?

Ans:

12.  Where we find Log files for clustering?

Ans: c:/program files/ windows/cluster.log

13.  How to move Tempdb, Is it require to re-start sqlserver?

Ans: Yes

Killing all Sql Server Data Bases

Declare @DBName varchar(50), @withmsg bit
Set @withmsg=1
Set @DBName='yourdatabasenmame' ---Change your databasename here
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
IF @withmsg =1
PRINT  CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB '  + @DBName
GO

Note: Change the databasename for @DBName variable

How do we know which files and file groups are ONLINE

select fg.name as FilegroupName, fg.type_desc as FileGroupType, df.name as FileName, df.physical_name, df.state_desc 

from sys.database_files df left outer join sys.filegroups fg on df.data_space_id = fg.data_space_id

Create Folder in SQL Server 2005

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @DBName sysname
DECLARE @DataPath nvarchar(500)
DECLARE @LogPath nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

-- 2 - Initialize variables
SET @DBName = 'Foo'
SET @DataPath = 'C:\zTest1\' + @DBName
SET @LogPath = 'C:\zTest2\' + @DBName

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @DataPath

-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree

-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath

-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath

SET NOCOUNT OFF

GO

Shrink Log file

use test
GO
DBCC sqlperf(logspace)


Use Test
GO
--'Nul' it is an old DOS trick that behaves as if you are writing a file,
-- but really just dumps the information off, It's not use diskspace
BACKUP LOG test TO DISK = 'Nul'
go
dbcc shrinkfile(2,256)--Shrink file upto 256
GO

--TO know what are the files created for 'Test' database
Use Test
GO
SP_Helpdb 'test'

Monitor tempdb In Sql Server 2005

How to Monitor tempdb Use?
Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information, see Troubleshooting Insufficient Disk Space in tempdb.

TempDB Best Practices

What is TempDB responsible for in SQL Server 2005?

Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.
Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.
Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
DBCC CHECKDB work tables.
Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

What are some of the best practices for TempDB?

Do not change collation from the SQL Server instance collation.
Do not change the database owner from sa.
Do not drop the TempDB database.
Do not drop the guest user from the database.
Do not change the recovery model from SIMPLE.
Ensure the disk drives TempDB resides on have RAID protection i.e. 1, 1 + 0 or 5 in order to prevent a single disk failure from shutting down SQL Server.  Keep in mind that if TempDB is not available then SQL Server cannot operate.
If SQL Server system databases are installed on the system partition, at a minimum move the TempDB database from the system partition to another set of disks.
Size the TempDB database appropriately.  For example, if you use the SORT_IN_TEMPDB option when you rebuild indexes, be sure to have sufficient free space in TempDB to store sorting operations. In addition, if you are running into insufficient space errors in TempDB, be sure to determine the culprit and either expand TempDB or re-code the offending process.

Difference Between FREETEXT and CONTAINS

Yes - Let's take a look at the comparison first and then we can work through a few different examples.  The FREETEXT command is another alternative to access the data indexed by Full Text Search.  In general the FREETEXT command provides the ability to search for a matched term based on the meaning of the terms as opposed to the exact character string.  At a high level, this commands finds matches based on separating the string into individual words, determining inflectional versions of the word and using a thesaurus to expand or replace the term to improve the search.

Now let's compare the FREETEXT functionality with the CONTAINS command.  The CONTAINS command uses exact match or fuzzy logic to perform the matches with a single word or a phrase.  In addition, the words near another word can be found as well as performing a weighted match of multiple words where each word has a weight as compared to the others that are searched.  Check out CONTAINS (Transact-SQL) for a explanation on the CONTAINS command.

Depending on the search you are performing dictates which Full Text Search command you should use.  Keep in mind that FREETEXT and CONTAINS are only two of the four commands available.  The other two commands are CONTAINSTABLE and FREETEXTTABLE.  The comparison between the four commands will be saved for a future tip since it is fairly involved explanation that should include examples.

Until that point in time, here is one data point to consider: according to SQL Server 2005 Books Online FREETEXT (Transact-SQL) "Full-text queries using FREETEXT are less precise than those full-text queries using CONTAINS. The SQL Server full-text search engine identifies important words and phrases. No special meaning is given to any of the reserved keywords or wildcard characters that typically have meaning when specified in the parameter of the CONTAINS predicate."  Based on my testing, when the basic terms are queried with either command similar results are returned, so the precise factor for simple queries seems less of an issue.  For complex searches the CONTAINS command wins hands down with the ability to use wild cards, NEAR statements, etc. As such, if the flexibility of the search is built into the front end application then the highest level of flexibility on the back end, between the FREETEXT and CONTAINS commands, tips the scales toward the CONTAINS command.

Syntax:

USE AdventureWorks;
GO
SELECT *
FROM Production.Product
WHERE FREETEXT(*, 'screw washer spaner');
GO

SQL DBA interview questions+ CSC

CSC:

1.     What is backup strategy?

2.     How to rename SQL Server?

sp_dropserver 

GO

sp_addserver , local

GO

Remote Logins - If the computer has any remote logins, running sp_dropserver might generate an error similar to the following:

Copy

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44

There are still remote logins for the server 'SERVER1'.

To resolve the error, you must drop remote logins for this server.

To drop remote logins

·         For a default instance, run the following procedure:

Copy

sp_dropremotelogin old_name

GO

·         For a named instance, run the following procedure:

Copy

sp_dropremotelogin 'old_name\instancename'

GO

3.     How to trouble shoot temp db?

4.     How to solve connection issue?

5.     How to solve recovery status issue, if my db box has 100 databases, In that only one db has go to suspect mode? How to solve?

6.     IS Full text search service comes as default service?

7.     How to find fragmentation level by using command prompt?

8.     What are maintenance plans that you executed?

9.     What type of alerting system that you configured for your sql server box?

10.  What is your database size?

11.  How to solve Log file growing issue?

12.  Is it possible to take back up from primary server what was participated in log shipping?

13.  What is end point in mirroring?

14.  What are the security concerns that you execute while we configure mirroring?

Security in SQL Server 2005 VS SQL Server 2000

SQL Server 2000

1.Security:Owner = Schema, hard to remove old users at times

2.Encryption:No options built in, expensive third party options with proprietary skills required to implement properly.

3.High Availability:Clustering or Log Shipping require Enterprise Edition. Expensive hardware

4.Scalability:Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.



SQL Server 2005

1.Security:Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.

2.Encryption:Encryption and key management build in.

3.High Availability:Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.

4.Scalability:4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.

1)-In SQL SERVER 2000 there where maximum 16 instances but in 2005 you can have up to 50 instances.

2)-Database mirror concept supported in SQL SERVER 2005 which was not present in SQL SERVER 2000.

3)-SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000.It was a separate installation for SQL Server 2000.

4)-SQL Server 2005 introduces a dedicated administrator connection (DAC) to access a running server even if the server is not responding or is otherwise unavailable. This enables you to execute diagnostic functions or Transact-SQL statements so you can troubleshoot problems on a server. which was not present in SQL SERVER 2000.

SQL DBA interview questions+ IBM

1.     What are tools that are used for backup and ticketing?

a.     Lite speed, BMC Remedy

2.     How to restore lite speed backup into sqlserver, is it possible to restore directly?

a.     That is not possible, because media type should be same

3.     What is the difference between User and login?

a.     User is data base level and login is server level

4.     How to add drive for cluster?

a.     Go to cluadmin

b.    Choose groups from left side

c.     Right click on add

d.    Choose drive which we want to add for existing cluster

5.     What is heart beat?    

6.     What are problems that you face for log shipping?

a.     Establish network tunnel

b.    Reduce transaction backup time from 30 min to 15 min

7.     What is your backup strategy?

8.     What are the major differences between 2000 and 2005 in terms of security?

a.     Owner = Schema, hard to remove old users at times in 2000

b.    Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates. In 2005

c.     In 2000 there is no concept of synonyms, in 2005 it is introduced

d.    In 2000 there in policy based management, in 2008 it’s there

9.     What are the steps for restoring data base?

a.     Go through the restore wizard in sql server management studio

10.  How to find deadlocks and how to resolve that?

a.     Just switch on the trace for 1204, 1222, 3605

b.    ex:DBCC Traceon(1204,1222,3505,-1)

11.  How to move logfile location from once drive to another drive?

a.     By attach and detach

12.  My log file is full how to solve it?

a.     By apply the shrink command

b.    Or by applying truncate command

13.  Can you tell some regular use of DBCC Commands?

a.     Dbcc checkdb()

b.    Dbcc showcontig()

c.     Dbcc dbreindex()

d.    Dbcc sqlperf()

e.     Dbcc traceon()

f.     Dbcc traceoff()

g.    Dbcc shrinkdatabase()

h.     Dbcc shrinkfile()

i.      Dbcc help()

j.      Dbcc ind()

k.     Dbcc checkident()

l.      Dbcc showfilestats()

m.   Dbcc inputbuffer()

n.     Dbcc outputbuffer()

o.    Dbcc log()

p.    Dbcc page(‘wowzzy’,1,945894,3)—check page is corrupted or not

14.  What is importance of dbcc showcontig()?

SQL DBA interview questions+ HCL + 2nd round

1.     What is the difference between sys.databases and sysdatabases

2.     How can we know linked servers information?—Ans: select * from sys.servers

3.     How to know all databases info that are in box? Ans: select * from sysdatabases

4.     Are we run query on mirror server?

5.     Are we run query on secondary server of log shipping?

6.     What is default port for sqlserver? 1433(tcp/Ip), UDP:1434

7.     What is dynamic port in sqlserver?

8.     How to set default backup directory path?

9.     How to add full text service after installation?

10.  How to find orphan logins?

11.  What is quorum?

12.  What is the advantage of active & active two node clustering?

13.  What are the reasons when a particular user account is not working?

14.  What are the reasons for connection is slow?

15.  What is the default time for remote connections? 600

16.  What is auditing?

17.  Which server role is assigned for backup? ANS: DB_BACKUPOPERATOR

18.  I have 30GB Ram, How can we configure for production environment? Ans: Depends SLA

19.  What are problems that you faced for log shipping?

20.  What is the distribution database?

21.  What does distribution database contains is it contains data?

22.  What is fill factor?

23.  How to shrink database? What are the steps?

24.  How to move temp data base?

25.  What are suggestible raid levels for mdf and log files?mdf-raid1,  log – raid5

26.  How many temp db’s are recommended as per Microsoft standards?

27.  Have you used profiler?

28.  Is it possible to save profiler data into table? Yes, in text file aslo

29.  What are table partitions?

30.  What is schema binding?

31.  Can I create index on table partitions?

32.  Can I get error while index is rebuilt?

33.  I have 4 databases, in that having 4 users, those 4 users are mapped with some server roles, and those 4 users are there in one database. If I am deleting that major db, is remaining db users are working or not? Yes, they have access with reaming db’s. Those are called orphan logins

34.  How to take resource data base backup?

Backing up the Resource database 
Since the Resource database is not available from the SQL Server tools, we cannot perform a backup similar to how we do it with the other databases. You can backup the database using the following options:

1.    You can use a simple xcopy command to copy from the source location to a destination where you keep your daily database backups. Use the -Y option to suppress the prompt to confirm if you want to overwrite the file. You can create a scheduled task to do this on a daily basis. If you want to keep multiple copies of the database files, you can create an automated script to rename them after the copy process.

xcopy :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf /Y xcopy :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf /Y

2.     

3.     

4.    You can use your file-based backup utilities such as NTBackup, IBM Tivoli Storage Manager, Symantec BackupExec, etc.

Restoring the Resource database 
It is important to document the location of your master database as part of your disaster recovery process. In previous versions of SQL Server, all we need to do to restore the server instance is to worry about the masterdatabase.

After a SQL Server 2005 instance has been rebuilt a restore of the master database will be done, the Resourcedatabase files should go along with it should a WITH MOVE option be required. This means that if the old location of themaster database will be different from the one after the restore, the Resource database files should already be there prior to restoring the master database. This is very critical if a hardware failure occurred and you need to move the system databases on a different drive during the server instance rebuild.

To restore the Resource database, just copy the database files to the location of the master database files. If you have an older version of the Resource database, it is important to re-apply any subsequent updates. This is why the recommended approach is to simply do a daily backup of these files.

SQL DBA interview questions+ Infosys

1.     What is your backup strategy?

2.     What is quorum?

3.     How to install quorum?

4.     What is DMV?

5.     What normalization?

6.     Solve backup failure scenario?

7.     How many instances are there for your production box?

8.     How to create Login?

9.     How many CPU’s are there for your environment?

10.  What is the size of DB?

11.  What are differences between sql server 2000 to sql server 2005?

12.  What are the different versions in sql server?

13.  What is your current version?