Category Archives: MSSQLServer

Check User Logins in SQL Server

Check User Logins in SQL Server

Instance Or Server Level :
Logins: In this define SQL Server User and integrity mode user

Database Levels
USERS: SQL Server define seperate Database user at database security tab.

Note: Login name is mapped with Database user

Check all the roles and user at database level

select name, type_desc , authentication_type_desc from sys.database_principals;

check roles and user at Server level

select name , type_desc , is_disabled, default_database_name from sys.server_principals;

check the database roles

select dp.type_desc , dp.name ,p.name
from sys.database_role_members r
JOIN
sys.database_principals dp on r.role_principal_id = dp.principal_id
JOIN
sys.database_principals p on r.member_principal_id = p.principal_id;

Check the Server Roles

Select dp.type_desc, dp.name, p.name
from sys.server_role_members r
join
sys.server_principals dp on r.role_principal_id = dp.principal_id
JOIN
sys.server_principals p on r.member_pricipal_id = p.principal_id
GO

Check the Database Permission

select class_desc , permission_name,p.name
from
sys.database_permissions dp
JOIN
sys.database_Principals p on dp.grantee_principal_id = p.principal_id

Check the Server Permissions

select class_desc , permission_name,p.name
from
sys.server_permissions dp
JOIN
sys.Server_principals p on dp.grantee_principal_id = p.principal_id;

Advertisements

Transaction Log file is growing large in SQL Server

Transaction Log file is growing large in SQL Server

Analyze the log size of the databases present in SQL Server:

dbcc sqlperf (logspace );

Output:

Database Name   Log Size(MB) Log space Used(%)  Status
--------------  -----------  -----------------  -------
AdventureWorks  813.245      71.3443535         0
TEST              0.5        79.1235678         0


It show the log size of all databases Present in SQL Server instance.

Logspace is growing large because of recovery mode defines.
Recover model is simple then once checkpoint is occurred then it truncate the logs. No point in time recovery at simple recovery.
Recover model is bulk-logged then at checkpoint occurred, its not truncated the transaction logging.
Recover model is full then every transaction is full logged and transaction is not truncated
Note:Another way is to take backup of transaction log otherwise it becomes larger or larger in size

Check the Status of Log transaction status

select name , log_reuse_wait_desc from sys.databases;
Output:

NAME             LOG_RESUE_WAIT_DESC
-------------    --------------------
master		 NOTHING
AdventureWorks   LOG_BACKUP
TEST             ACTIVE_TRANSACTION

LOG BACKUP values show need to be backup the transactional log for adventureworks database otherwise its continue grow until you take a transaction backup.

You can take the transaction log backup with right click on database in SQL Server Management Studio.

In TEST database having active transaction it finished after checkpoint issue.

Manually fire the checkpoint on ADVENTUREWORKS & TEST database

use ADVENTUREWORKS
Checkpoint

use TEST
Checkpoint

Check the status of log transaction again:

select name , log_reuse_wait_desc from sys.databases;

name             log_Resue_wait_desc
-------------    --------------------
master           NOTHING
AdventureWorks   NOTHING
TEST             NOTHING

Check again the AdventureWorks transaction Log space

dbcc sqlperf (logspace );

Output:

Database Name   Log Size(MB) Log space Used(%)  Status
--------------  -----------  -----------------  -------
AdventureWorks  813.245      2.443535         0
TEST              0.5        79.1235678         0

Following are steps to shrink the Transaction Log file in File System of Operating system:

alter database adventureworks set recovery simple;
Go
-- Shrink log file to 1 MB
DBCC shrinkfile (adventureworks_log,1);
Go
alter database adventureworks set recovery FULL;
GO

Migrate Database from Oracle to MS SQL Server with SSMA

Migrate Database from Oracle to Microsoft SQL Server with SSMA Utility

Following are the steps performed from migrate database from oracle to sql server:

1. Download the SSMA software for migration from Oracle to SQL Server

https://www.microsoft.com/en-us/download/details.aspx?id=54255

2. Install the SSMA software and found SSMA icon on Desktop by simple next next button.

SSMAicon1.JPG

3. Open the SSMA software and start a new project.
Go to File–>click New then Pop Window ask for project name, location and into which sql server want to migrate included SQL Azure option. (selected sql server 2014 for example)

SSMAnewproject.JPG

4. Connect with oracle database have three options:
1. Oracle Client Provider – Need to install Oracle Client
2. OLEDB Provider – Direct connectivity for database if not using PDB of 12c
3. ODBC Driver – Create ODBC connection first
Note: you are on another server or pdb database of 12c version.Its better to install Oracle client for connectivity and create TNS entries in Client.

In my example, My Server is in 12c and Schema is in PDB database. So i installed Oracle Client and configured PDB1 for direct connectivity. Make Sure your pdb is in open state.

Created TNS entry for PDB database in tnsnames.ora file:
Go to Oracle Client location $ORACLE_CLIENT_HOME\network\admin

tnsping.JPG

5. Connect with PDB1 database with SSMA Module in Oracle Connectivity:

PDBconnectivity.JPG

Note: it will take time after message of connected, it will start load the object from Oracle to make its own repository.

LoadOracleObjects.JPG

Error:
Connection to Oracle failed.
ORA-01033: ORACLE initialization or shutdown in progress

I am using PDB1 database , it is not in open state. So, I connected with sysdba user and open the pdb1 database
sqlplus sus@pdb1 as sysdba
Password
SQL> Alter database open;

6. After loading the Oracle Object, it will enable the connectivity button for SQL Server.

ConnectwithSQLServer

7. Connect with the SQL Server database.
Note: Choosed TEST database as name which is not present in SQL Server. So it will pop window for creating TEST database.

SqlServerconnection.JPG

Error: Sql Server Agent is now working. You need to start the service of SQL Server Agent in services.msc, Then press continue button.

SqlServerAgentService.JPG
Note: You can also modify reporserver schema in TEST Database to default dbo

Modifytodba.JPG

8. Right click on the schema in Oracle tab which want to migrate and choose create report.

CreateReport.JPG
Note: Process of creating report is started in output window.

9. Warning occured during the create report and continue on it.

Prerequestnotmet.JPG

10. After continue, it start converting and creating report in html fromat which is present in location of project –> report –> report –M mainindex.html

creatingreport

11. Report open in internet explorer which will give you detail of objects and estimate of manual convertion time.

Reporthtml.JPG

12. Start the convert process by right click on schema of Oracle and choose the convert button using the SSMA tool.

Conversionfinish.JPG

13. On Right tab SQL SERVER Metadata Explorer, Right click for sync the sql server database with repository
It will create the tables, procedure, function into the sql server database when you choose to sync the database.

synchronisedatabase.JPG

14. Syncronization is completed for sturcture

syncforstructure.JPG

15. Start the migration data process
Note: Both oracle and SQL Server database is selected in migration process

Startmigratedata.JPG
Note: Will ask for password again for data migration.

16. Datamigration report is finished.
Error 58 table failed to migrated

Datamigrationreport.JPG
Save the report for future correction.

17. Verify the sql server by connecting the TEST database.

Install Microsoft SQL Server 2014

Install Microsoft SQL Server 2014

1. Download the SQL Server and click Setup.exe.

1Setupforsqlserver.PNG

2. Go to installation tab and choose your configuration : Standalone, cluster, Upgrade

Choose Standalone installation:

InstallationSQLServer

3. Go to the product key, My version is temporary for practice (180 day trial)

3ProductKey.JPG

4. Chooaw license terms and customer experience as needed. Click on i accept.

4LicenseTerms.JPG

5. Check for global rules

5GlobalRules

6. Choose for Microsoft updates and next.

6Microsoftupdates.JPG

7. Install setup files:

7InstallSetupFiles

8. Choose SQL Server Feature Installation, it give option to choose feature:

8SetupRole

9. Select the feature you needed, like database , reporting tool, ETL tool

9FeatureSelection.JPG

10. Check the features needed configuration.

10FeatureRoles.JPG

11. Check the .Net Framework 3.5 service pack

11RuleCheckResult.JPG

12. Install the instance with default name MSSQLSERVER, you can choose your own name also:

12InstanceConfiguration.JPG

13.  Set the Server configuration of Services:

14ServerConfiguration.JPG

14. Choose mixed mode for both window or sql server authentication (SA password)

15DatabaseEngineConfiguration.JPG

15. Choose analysis services type:

16AnalysisServicesConfiguration.JPG

16. Choose reporting Services configuration:

18ReportServicesConf.JPG

17. Choose distributed replay controller:

19DistributedReplayController.JPG

18. Choose distributed replay client:

20DistributedReplayClient.JPG

19. Now it ready to install:

21ReadytoInstall.JPG

20. Installation is in progress:

22InstallationProgress.JPG

21. Installation done.

24Complete.JPG

 

 

Transaction Isolation Levels in SQL Server

Transaction Isolation Levels in Microsoft SQL Server

There are four isolation levels:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Microsoft SQL Server supports all of these Transaction Isolation Levels.

READ UNCOMMITTED
SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read.

READ COMMITTED
This is the default isolation level in SQL Server. When it’s used, SQL Server will use shared locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it not ensures that the data will not be changed before the end of the transaction.

REPEATABLE READ
When it’s used, the dirty reads and nonrepeatable reads cannot occur. It means that locks will be placed on all data that is used in a query, and another transactions cannot update the data.

NONREPEATABLE READ
When a transaction reads the same row more than one time, and between the two (or more) reads, a separate transaction modifies that row. Because the row was modified between reads within the same transaction, each read produces different values, which introduces inconsistency.

SERIALIZABLE
Most restrictive isolation level. When it’s used, the phantom values cannot occur. It prevents other users from updating or inserting rows into the data set until the transaction will be completed.

PHANTOM
Phantom behavior occurs when a transaction attempts to select a row that does not exist and a second transaction inserts the row before the first transaction finishes. If the row is inserted, the row appears as a phantom to the first transaction, inconsistently appearing and disappearing.

You can set the appropriate isolation level for an entire SQL Server session by using the SET TRANSACTION ISOLATION LEVEL statement.
SYNTAX:

SET TRANSACTION ISOLATION LEVEL [ READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE ]

You can use the DBCC USEROPTIONS statement to determine the Transaction Isolation Level currently set. This command returns the set options that are active for the current connection. This is the example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DBCC USEROPTIONS
GO

DB Mail configuration on SQL Server

Enable the Database mail configuration

1. Enable the db mail configuration on SQL Server

sp_configure ‘Database Mail XPs’,1
reconfigure

 

2. Enable service broker in the MSDB database.

ALTER DATABASE [MSDB] SET  ENABLE_BROKER WITH NO_WAIT;

 
3. Open the sql server management studio and right click on database mail

4. Configure the mail with smtp and mail id

5. Go to SSMS (SQL Server Management Studio) and test the mail by right click on database mail.

Indexed View in Sql Server

Indexed View

A view with a unique clustered index is an indexed view. It will save data for better performance and utilized the index on the query. It help SQL Server to increase performance. It act like materialized view in oracle.

As base table is updated the indexed view is also updated.

View: A view is the normal representation of SQL queries. It is saved in data dictionary of the database. It is used to keep the complex query for reuse purpose, security purpose( if not want to represent the exact structure of the database to real world).

View does not have its own data it will fetch data from its base tables.

Example for creating the Index view:

1. Create view on the SQL Query:

CREATE VIEW index_view_name AS
SELECT columnA, columnB FROM dbo.table_name
GO

 

2. Create unique clustered index on the view:

CREATE UNIQUE CLUSTERED INDEX idx_index_view_name ON index_view_name(columnA)
GO