How to make Identity Column in Existing Table.

We got a request to make column IDENTITY column in the existing table.

This is the process we followed.

1. Script-out the  table and index structure.

2. Rename the table using SQL syntax

EXEC sp_rename 'Old_Table_Name', 'New_Table_Name'


3.Run the scripted SQL from old table in step-1 and & make the column  DB_ASC identity column before running statement

CREATE TABLE  [D_ASC](
[DBAS_ID] [varchar](30) NOT NULL,
[DBC_AS_ID] [varchar](10) NOT NULL,
[DB_ASC ] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [X_AS_DB] PRIMARY KEY CLUSTERED
(
[DB_ASC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DB_Data]
) ON [DB_Data]
 GO

4. Run the  Import/Export wizard.

Right click on database where we have New table ----> Tasks ----> Import

5. Data Source will be the server/database which have old table

 Connect to source server and DB name where Old table exists. Click Next

6. Choose Destination Server/DB name  and Click on Next button

 7. select first option and click on next button.

8. Choose source and destination tables & Click on "Edit Mapping" Button.
On edit mapping window, Check the "Enable Identity insert" Box else we will get error message while data transfer from source table to destination.

9. Now click Next - Next and Finish Button. data will be transferred to new table with Identity column values.

How to Find SQL Server Version,Edition,Product Level

1. Select @@version

2. Run below Query in SSMS

Select
Case
When Convert(Varchar(128), ServerProperty ('ProductVersion')) like '9%' Then 'SQL Server 2005'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '10.0%' Then 'SQL Server 2008'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '10.5%' Then 'SQL Server 2008 R2'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '11%' Then 'SQL Server 2012'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '12%' Then 'SQL Server 2014'
When Convert(Varchar(128), ServerProperty('ProductVersion')) like '13%' Then 'SQL Server 2016'
Else 'Unspecified'
End as Version,
ServerProperty('Edition') as Edition,
ServerProperty('ProductLevel') as ProductLevel,
ServerProperty('ProductVersion') as ProductVersion

SQL Server 2014 Installation

SQL 2014 software downloaded from Microsoft site and now we will learn the installation.

I downloaded the ISO file,So I have mounted it.Open the disk where s/w mounted and right click on setup.exe, select "Run as Administrator".

It will open new window. from the below windows , select Installation option. click on  New server stand-alone .

New Windows will open, We will click on next button since we installing evaluation edition which is valid for 180 days.

On next page, check "I accept the license terms" box and click next

Global rules status should be passed. if anything fails,fix that before proceeding .Click Next if all green.

I am not checking Microsoft update check box , Click on Next button.

Install rules should be passed, we can ignore firewall warning.Click Next

From Set-up rules, select 1st option(SQL server feature installation).if u want to install all feature with default, choose 3rd option. choose 2nd option for share-point. Click next

choose whatever features u want to install from below page.Click  next

All feature rules should be passed.

Instance Configuration :
Default instance: we can have only one default instance per server and its name will be the name of  server  .we can connect to default instance using server name,local host or using dot(.) in SSMS.
Named Instance: we can have multiple named instance per server. in order to connect to named instance we should use [server-name\Named-Instance] Name in SSMS.
 I am going to install named instance "TEST" . Click on Next button.

Server Configuration:
we select how our sql server services and agent services run. Domain account must be used to run SQL server services and sql agent services. since i am installing it on my local machine,i am ok with default options.
Collation: it's simply rules to compare and  sort order,string,letters in SQL server.
SQL_Latin1_General_CP1_CI_AS is default collation in SQL server. Click on next

Database Engine Configuration:

1. Server configuration tab:

Windows authentication: only domain users (Windows) can connect to SQL instance
Mixed mode: Windows and SQL users  both can connect to SQL instance.

When we select Mixed mode, we have to type in the password for SA account.SA account have unrestricted access to SQL instance. MS recommends to disable the SA account.

Now we have to add the SQL admin group which will have full privilege on SQL server.
Click on ADD   then type the DBA admin group.

Now Click on "Data Directories " tab

For Better I/O performance:

1. Keep System DB on separate drive.
2. user database data files should be placed on separate drive
3. user database log files must be on different disk
4. Temp db should be on dedicated drive

Click on Next

Feature Configuration rules must be passed.Click on next

On Ready to install page, click on Install button & view the installation progress.

Finally SQL server is installed.

Click on Close Button.

Open SSMS and connect to SQL named instance.

SQL Server Reporting issue -- What to look at first glance

We had a issue with our reports since  users were not able to access the reports.

We checked the reporting log files and got the actual issue .

Connect to your Report server and explore the path & open the latest report log file

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles

Error:

Logon attempt for user 'svc_ssrs_report' failed., Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed.
Ensure the user name and password are correct. ---> System.ComponentModel.Win32Exception:
The referenced account is currently locked out and may not be logged on to
at Microsoft.ReportingServices.Diagnostics.Utilities.LoginUtil.Login(String userName, IntPtr ptrPwd, String domain)

Resolution:

Reporting user was unlocked by Windows team and account policies were changed so it never locked-out again.

Cluster Index with Drop Existing

CREATE CLUSTERED INDEX cixEmp
on Employee_InfoV2(emp_id)
with
(
DROP_EXISTING = ON,
sort_in_tempdb = ON,                 
FILLFACTOR = 80,         
PAD_INDEX = ON
)


Clause :

DROP_EXISTING = ON ----> if index exists, drop it  then recreate.

sort_in_tempdb = ON  ----> sort index operation in tempb

FILLFACTOR = 80 ---->fill each index page 80%. remaining 20% free,

PAD_INDEX = ON ----> If  Fillfactor is 80% ,then  each data page(Intermediate page in B-tree) will be  80% full remaining 20% free.

JOIN OPTIONS IN SQL SERVER

WHEN TO USE WHICH JOIN OPTION FOR FASTER JOIN OPERATIONS?



MERGE JOIN: APPLICCABLE FOR LARGER TABLES

LOOP JOIN: APPLICABLE FOR SMALLER TABLES

HASH JOIN: APPLICABLE FOR TABLES WITH UNKNOWN SIZE PARAMETERS

SELECT * FROM SALES_DATA
JOIN
PRODUCTS_DATA
ON
PRODUCTS_DATA.ProductKey = SALES_DATA.ProductKey
OPTION (MERGE JOIN)

SELECT * FROM SALES_DATA
JOIN
PRODUCTS_DATA
ON
PRODUCTS_DATA.ProductKey = SALES_DATA.ProductKey
OPTION (HASH JOIN)

SQL SERVER ISOLATION LEVELS:

A MECHANISM TO CONTROL THE DEGREE OF DEPENDANCY BETWEEN TRANSACTIONS.
EVERY TRANSACTION HAS A UNIQUE AUTO ASSIGNED LSN: LOG SEQUENCE NUMBER.

TYPES OF ISOLATION LEVELS:
1. READ UNCOMITTED  ISOLATION LEVEL
2. READ COMITTED  ISOLATION LEVEL
3. SNAPSHOT ISOLATION LEVEL
4. SERIALIZABLE ISOLATION LEVEL
5. REPEATABLE READ ISOLATION LEVEL
6. READ COMMITTED SNAPSHOT ISOLATION LEVEL

-- 1. READ UNCOMMITTED ISOLATION LEVEL : used to read table data even though there are
-- uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select * from DBA_DETAILS   -- DIRTY READS


-- 2. READ COMMITTED ISOLATION LEVEL : used to read table IF there are
-- NO uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from DBA_DETAILS -- BLOCKING


-- 3. SNAPSHOT ISOLATION LEVEL : Used to read table data even though there are
-- uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from DBA_DETAILS -- NO BLOCKING & NO DIRTY READS


-- 4. SERIALIZABLE ISOLATION LEVEL : used to read table IF there are
-- NO uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
select * from DBA_DETAILS --  THERE WILL BE BLOCKING


-- 5. REPEATABLE READ ISOLATION LEVEL : used to read table IF there are
-- NO uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
select * from DBA_DETAILS -- THERE WILL BE BLOCKING



-- 6. READ COMMITTED SNAPSHOT ISOLATION LEVEL : ABLE to read table data even
-- Though there are uncommited / open transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT
select * from DBA_DETAILS -- NO BLOCKING & NO DIRTY READS

SSAS tabular model process using XMLA code

We will create the SQL agent job for processing the SSAS tabular model.

Below are the steps:

1. Connect to SSAS instance using SSMS

2. Expand the databases , right click on the preferred database and click on "Process database"

3.Now select the mode from drop-down menu, select FULL.

4.Click on Script and select "Script actions to new query window" from drop down menu.

5.It will generate the XMLA script. Click on cancel button if don't want to process database.

6.Copy the script and connect to SQL Server instance where it needs to be automated. expand the SQL agent and create new Job to automate it.


7. click on new step in the Job.

In the type choose "SQL server analysis command".

Run as --> use proxy account if u have in your environment else default option.

Server : use SSAS instance where XMLA code was generated earlier.

Command : Paste the XMLA code  in command pane window. and click OK.

Schedule the Job as per the business requirement.

Grant Execute permission to user in SQL Server

we need to create database role in particular database where user wants execute permission.

Use DB_NAME
GO
CREATE ROLE [db_executor]
GO

Grant the execute permission to database role:

Grant execute to [db_executor]

Now add the user to the database role

ALTER ROLE [db_executor] ADD MEMBER [User-Name]
GO

Uninstall SQL Server 2012

we have to open control panel -->Programs.

We will land to new window. Now we need to look for "Microsoft SQL Server 2012(64-bit).

click on this program.

It will open a new window: Click on " Remove" option:

we will land on "Setup support rules" page

all rules should be passed on the page, if any of fails check the reason and fix before proceeding.

click on OK button.

from drop down menu, select the instance we wish to remove.then click on "NEXT" button.

we get "Select feature" page, we have to select feature which needs to be removed. if want to remove all, choose "select all".

Click Next button.

it opens "Removal rules", check all rules are passed.

Click "NEXT" button.

Click "Remove" button.

UN-installation of SQL instance starts:

Once the UN-installation process complete, we need to close the window.

Setup Account Privileges Rule failed while installing SQL server

We got Setup Account Privileges Rule failed while installing SQL 2012.

Reason: 

The account that is running SQL Server Setup does not have one or all of the following rights: 

1. Right to back up files and directories

2.Right to manage auditing and the security log

3. Right to debug programs.

Resolution:

I checked that user was part of both groups(Right to back up files and directories and Right to debug programs.) but it was not part of "Right to manage auditing and the security log" group.

So i added the user to this group.

1. Click Start, click Run, type secpol.msc, and then click OK.

2. In the Local Security Settings dialog box, click Local Policies, double-click User Rights Assignment, and then double-click “Manage auditing and security log properties”.

3. In the “Manage auditing and security log properties” dialog box, click Add User or Group.

In the Select User or Groups dialog box, type the user account that is being used for setup, and then click OK two times.

we re-run the SQL software and installation completed successfully.

Error While enabling Windows feature: netFx3

While installing SQL server 2012, we got this error message.

This error indicate that .net framework 3.5 was not enabled,SQL server tried to enable it but failed.

Resolution:

we click on start button in windows 2012, in the search box type "Add Roles and features".
Click on "Add Roles and features".

It will open below window:

Click Next:

Click Next

Click Next

check "Application Server" Box and click next

select ".NET Framework 3.5 features" and click Next

Click on " Restart the destination server automatically if required" and click Install button

Operation finished successfully.

We successfully installed the SQL server after this operation

.