welcome to 2015

Hi,


Happy New Year 2015 to All

Happy New Year 2015

Hi,


Happy New Year 2015 to All

Embedded and Shared Datasets

   

• 
  
  Shared Datasource: A shared data source is defined as a separate item and can be used by multiple reports.
  
  Shared datasets :   A shared dataset is published on a report server and can be used by multiple reports. A shared dataset must be based on a shared data source. A shared dataset can be cached and scheduled by creating a cache refresh plan.
  
  Embedded Datasource : An embedded data source is defined in a report definition and used only by that report.
      
• Embedded datasets :   Embedded datasets are defined in and used by a single report.
  Use an embedded dataset when you want to get data from an external data source to be used only in one report.
   
  

 

 

How many IP addresses are required to configure 2 node cluster

1. Two public network addresses for node1 and node2
2. Two private network addresses for node1 and node2
3. Windows Cluster IP
4. MSDTC IP
5. Virtual Server IP

HEAP Table / Clustered Table

A Table without clustered index is called HEAP Table.

When you define a clustered Index on heap table, then it gets a structure then that table can be called as Clustered Table.

How to change script language in SSIS

From 2008 onwards we can implement c# script along with vb script in SSIS.

To change language for script task.
BIDS -- Tools - Options
Business Intelligence Designers - Integration Service Designers -- General
Script Language -- Choose the script language from here.

For Each Loop Container in SSIS

It defines a repeating control flow in a package. Loop implementation in the for each loop container is similar to the ForEach loop concept in programming language.
Types of Enumerators

7 Type Enumerator

Foreach File Enumerator – This enumerate files in a folder. We can get list of files which has extension .jpg from any folder and its subfolders
Foreach Item Enumerator – Enumerate values in an item
Foreach ADO Enumerator – Enumerate rows in tables
Foreach ADO.Net Schema Rowset Enumerator – Enumerate a schema
Foreach from variable Enumerator – Enumerate the value in a variable
Foreach nodelist Enumerator – Enumerates nodes in an XML document
Foreach SMO Enumerator – Enumerate a SMO Object.


Foreach Loop Container Properties

1. Enumerator – Select appropriate enumerator (select Foreach File Enumerator)
2. Folder – Select source folder from where you want to enumerate files
3. Files – Specify extension (*.xls)
4. Drag the script task inside the for each loop container
5. Bind the variable User::File_Name to ReadOnly Variable
6. Below code snippet displays list of files which are matched with .xls extension from the specified folder.

Editions of SQL Server 2012

SQL Server 2012 Editions

1. Standard Edition

2. Business Intelligence

3. Enterprise Edition

Specialized Editions

Developer -- The developer edition includes all the features which are available in enterprise edition, but it is only meant for development, testing and for demonstration purpose.

Web    -- It is mainly focused on hosting internert facing web applications. Like Express editions it doesn't have database size restrictions, and it supports upto 64 GB RAM. It is a ideal platform for websites and web applications

Express -- This is a free edition, which can integrate with independent software vendors. It is limited to one processor and 1 GB memory and it is integrated with visual studio environment.

sp_MSforeachdb

sp_MSforeachdb Examples

Without using cursor we can execute a command in all the databases or on required databases by using sp_MSforeachdb.

Examples
sp_MSforeachdb 'select ''?'''   -- List all databases that are in the instance. '?' Indicated Database Name

sp_MSforeachdb 'Use [?]; exec sp_spaceused'  -- Example to execute any stored procedure

sp_MSforeachdb 'Use [?]; select * from sysfiles'  -- Example to execute any SQL Statement

sp_MSforeachdb 'Use [?]; Create table ForEachDBTest(Id Int)'  -- Creating a table in all databases

sp_MSforeachdb 'Use [?]; select * from ForEachDBTest'   -- Selecting rows from a table through all databases.

sp_MSforeachdb 'If ''?'' like ''TempDB''    -- Conditional wise execution
begin
select * from sysfiles
end '
Below command is used to display list of databases which has Create Statistics property is set to True

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''ISAutocreateStatistics'')=1  
begin
 Print ''?''
end'

SQL Server 2012 Startup Parameters

Unlike earlier versions from SQL Server 2012 onwards, we have a new window to add startup paramerts like trace flags and other parameters to SQL Engine. Below is the screenshot for reference.

Contained Databases - SQL Server 2012

SQL Server 2012 introduced a new feature called Contained Databases.

Database is a collection of data objects, views, sps and users also. If you move database from ServerA to ServerB then we might not able to access the database using ServerB Logins because there is a dependency between server and database, logins will be stored under server level and users are stored at database level.

Hence database is not a individual component, This contained database can break the barrier of dependency.

If we migrate the contained database into other server (from ServerA to ServerB) then we need not to createa any logins to make use of it.
 

Attach Adventure Works Sample Databases SQL 2008, SQL 2012

Microsoft provides few sample databases for all SQL versions to work with it.
Search with "adventureworks database download" keyword in google and open the codeplex site and download the required version of your chocie.

The download will provide only MDF file. To attach sample databases, follow the below process

1. Download the MDF to your required location (C:\temp\2012\)
2. Attaching database can be done in two ways
       a) Using T-SQL Syntax
       b) Using GUI

Using T-SQL

CREATE DATABASE [AdventureWorks2012] ON ( FILENAME =N'C:\Temp\2012\AdventureWorks2012_Data.mdf' ) FOR ATTACH
GO

Using GUI

1. Open SSMS
2. Right Click on Databases - Attach
3. Browse and select the MDF File
4. You will find two files in the database details window
            1) MDF File
            2) LDF File --- Not Found
5. You need to select the Not Found Row -- Remove
6. Click OK to attach the database with out LDF file

What is tempdb database?

 

Temp Database

Tempdb database is a global resource that is available to all users connected to the instance of SQL Server.

1) TempDB will cleared everytime when the SQL Server is stopped and started, It will recreate only if the file path is moved using alter database tempdb.

2) By default TempDB creates an MDF file with 8 MB and LDF file with 1 MB and grows upto 2 TB

3) Each SQL Server Instance have only one Temp DB Database.

4) We cant change the recovery model and of TempDB Database, It is always in Simple Recovery Model

5) It is not possible to drop or detach tempdb database

6) Not possible to change database options like (e.g. Database Read-Only, Auto Close, Auto Shrink Etc)

7) Like all other databases we can't backup or restore or setup mirroring for tempdb database

 

Below command is used to move tempdb database into new location

USE master;
GO
ALTER DATABASE TEMPDB
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\test\TEMPDB.mdf');
GO
ALTER DATABASE TEMPDB
MODIFY FILE(NAME = templog, FILENAME = 'D:\test\Datatemplog.ldf');

Approaching Database Server Performance Issues

This  is very light weight script and it will give the result even if the server is under pressure and will give an over all state of the server at that moment. (how quickly we can check the runnable task and I/O pending task on an SQL server instance)

When you work as DBA, many people will approach you with a complaint like "Application is taking ages to load the data on a page,could you please check something going wrong with database server?" There might be hundred of other reason for slowness of the page.It might be a Problem with application server,network issues,really a bad implementation or problem with database server due to generation of huge report /job running at that moment. What ever be the issue, database gets the blame first. Then it is our responsibility to cross check the server state. 

Let us discuss how we can approach this issue. I use following script to diagnose  the issues. The first script which I will run against server is given below:

SELECT 

parent_node_id                  AS Node_Id,
COUNT(*)                        AS [No.of CPU In the NUMA],
SUM(COUNT(*)) OVER()            AS [Total No. of CPU],
SUM(runnable_tasks_count )      AS [Runnable Task Count], 
SUM(pending_disk_io_count)                    AS [Pending disk I/O count],
SUM(work_queue_count)          AS  [Work queue count]
FROM sys.dm_os_schedulers WHERE status='VISIBLE ONLINE' GROUP BY parent_node_id



This will give following information. 

• Number of records in the output will be equal to number of NUMA nodes (if it is fetching only one record , it is not a NUMA supported server)
• Node_id : NUMA node id . Can be mapped into the later scripts.
• No.of CPU in the NUMA : Total number of CPU assigned to the specific NUMA node or the number of schedulers.
• Total No. of CPU : Total No. of CPU available in the server.If you have set the affinity mask, total number of CPU assigned to this instance.
• Runnable Task Count: Number of workers, with tasks assigned to them, that are waiting to be scheduled on the runnable queue. Is not nullable. In short number of request in runnable queue.To understand more about Runnable queue , read my earlier .
• Pending disk I/O count : Number of pending I/Os that are waiting to be completed. Each scheduler has a list of pending I/Os that are checked to determine whether they have been completed every time there is a context switch. The count is incremented when the request is inserted. This count is decremented when the request is completed.
• Work queue count: Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up.

I have scheduled this scrip to store the output this query in a table for two days in the interval of 10 minutes. That will give baseline data about what is normal in your environment. In my environment people will start complaining once the Runnabable Task Count of  most of the nodes goes beyond 10 consistently. In normal scenario, the value of Runnabable Task Count will be always below 10 on each node and never seen a value greater than 0 for work queue count field.This will give a picture of current state of the system.If the output of this step is normal, we are safe to an extent, the slow response might be issue which might be beyond our control or a blocking and slow response is only for a couple of screens(sessions) not for entire system

How Much Percentage Database Is Restored Through T-SQL

select percent_complete, status, start_time, command
from sys.dm_exec_requests where command = 'RESTORE DATABASE'

System Databases

1. Master DB
2. MSDB
3. TEMP DB
4. MODEL DB
5. RESOURCE DB