How do you set up collation during sql installation ?

How will you take SQL Server in single user mode ?

SQL Server Configuration Manager > SQL Server Services > SQL Server (InstanceName) > Properties > Advanced > Startup Parameter >

For SQL Server 2005 and 2008

 Just put   -m;  then Save/Ok
Example:
-m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

For SQL Server  2012:
Click Startup parameter and just put -m;

Tips:
You should stop other sql server services to get connect with SQL when sql server is in single user mode

 

Microsoft SQL Server, Error: 14640

Service Broker message delivery is not enabled in this databse. Use the ALTER DATABASE statement to enable Service Broker message delivery.(Microsoft SQL Server, Error: 14640)

USE master
GO
ALTER DATABASE MSDB SET ENABLE_BROKER ;
GO

Top Secret Tips:
Delete Database Mail Profile and Account and Re-create it

How do you find Number of current connections ?

SELECT DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM  sys.sysprocesses
WHERE dbid > 0 GROUP BY dbid, loginame

How do you configure Msmdpump.dll for Analysis service ?

Install  sql_as_oledb.msi (Analysis Management Objects (olap oledb) ) can be downloaded via sql server 2012 feature pack 1 folder. Install as well as Web server.

The below configuration is needed to make the ini file  point to the OLAP server.

1. Log in to Analysis Server and copy the below files
at <drive>:\Program Files\Microsoft SQL Server\<instance>\OLAP\bin\isapi:
 MSMDPUMP.DLL,
 MSMDPUMP.INI, and
 a Resources folder

2. Log in web server and paste those files in below location

C:\inetpub\wwwroot\OLAP


In the msmdpump.ini the FQDN (Fully qualified domain name) of the Analysis Server name is to be used (because KERBEROS authentication implemented by  is using FQDNs, e.g. UAT01SQL2012.wolterskluwer.com).

More Information in below
http://msdn.microsoft.com/en-us/library/gg492140.aspx


 

Which features do we need for Analysis services ?

Instance Features:
Analysis Services
Shared Features:
Client Tools Connectivity
Client Tools Backwards Compatibility
Client Tools SDK
SQL Server Books online
Management Tools - Basic
           Management Tools - Complete
 SQL Client Connectivity SDK
Microsoft Sync Framework
             

How Data base size and path

SELECTdb.[database_id] AS 'DB ID',
db
.[name] AS 'Database Name',
af
.[name] AS 'Logical Name',
af
.[filename] AS 'Physical Location',
(((
CAST(af.[size] AS DECIMAL(18,4)) * 8192) /1024) /1024) AS 'File Size (MB)',

((((
CAST(af.[size] AS DECIMAL(18,4)) * 8192) /1024) /1024) /1024) AS 'File Size (GB)'
FROM sys.databases db INNER JOIN sys.sysaltfiles af ON db.database_id = af.dbid

WHERE [fileid] in (1,2)

===================================================
SELECT name,Size, physical_name AS current_file_location

FROM sys.master_files=============================
sp_helpdb

There are no sql server instances or shared features that can be updated on this computer

This message means - Server is already updated for that sql server by that Service Pack
"There are no sql server instances or shared features that can be updated on this computer"

How do you install 32 bit SQL ??

Options > Processor Type > x86

How can Find Fully Qualified Domain Name(FQDN) ?

Start Menu > Run > cmd > nslookup <Server_Name Or IP>
More Commands IP related:
netstat -na|find "LISTEN" netstat -an
arp -a



 

why and how do you restore MSDB Database in SQL Server ?

Why:
1. MSDB can be corrupted
2.If you restore Master Database, MSDB will be re-built . This means you will loose data in MSDB.

How:
Stop SQL server agents and other SQL services without SQL Service
then you can restore with over-write

How do you find out SQL Server installation Path ?

declare @rc int, @dir nvarchar(4000)
Exec @rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\Setup',
N'SQLPath',
@dir output, 'no_output'
Select @dir AS InstallationDirectory

 

Which features do you need for SQL Client/Management Studio ?

Need to select only Shared Features
1. Client Tools Connectivity
2.Client Tools Backwards Compatibility
3.SQL Server Books Online
4.Management Tools - Basic
   Management Tools - Complete

How do you open new port and use in SQL server

How to open TCP Port 1755

1. Control Panel>System and Security>Windows Firewall>Advanced setting
2. Select inbound rules  then New Rule at the right hand corner.
Role Type: Port>Next
Specific local ports: 1755

 

How monitor SQL Cluster

Step 1:
Create a table named [CLUSTERFAILOVERMONITOR] in the master database of the SQL Server instance, with a column [PREVIOUS_ACTIVE_NODE]. Use the query below for creating the table.

create table CLUSTERFAILOVERMONITOR ( PREVIOUS_ACTIVE_NODE varchar(30) )

Step 2:

Insert the value of the currently active node in that table. Use the query below for this.

insert into CLUSTERFAILOVERMONITOR
values
(
'Node1_Name'
)
Step 3:
Create a job with code shown below in it. The job will execute on the master database, will run every minute (can be run every 5-10 seconds in SQL Server 2008) and when the failover happens it will send a notification.

After the failover has occurred, this job will change the value of PREVIOUS_ACTIVE_NODE column in the CLUSTERFAILOVERMONITOR table that we created in step1, and is therefore ready to monitor the next failover event.

Declare @var1 varchar(30) SELECT @var1= PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR CREATE TABLE PHYSICALHOSTNAME ( VALUE VARCHAR(30), CURRENT_ACTIVE_NODE VARCHAR(30) ) INSERT INTO PHYSICALHOSTNAME exec master..xp_regread 'HKEY_LOCAL_Machine', 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\', 'ComputerName' declare @var2 varchar(30) SELECT @VAR2=CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME if @VAR1<>@VAR2 Begin EXEC msdb..sp_send_dbmail @profile_name='DBAMail', @recipients='xyz@company.com', @subject=' Failover occurrence notification - SQLExample', @body='Cluster failover has occured for instance SQLExample. Below given are the previous and current active nodes.', @QUERY='SET NOCOUNT ON;SELECT PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR;SELECT CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME;SET NOCOUNT oFF' update CLUSTERFAILOVERMONITOR set PREVIOUS_ACTIVE_NODE=@VAR2 End DROP TABLE PHYSICALHOSTNAME