Category Archives: MSSQLServer

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.

Advertisements

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

Choosed 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

 

Enable the xp cmdshell in Sql Server

XP_CMDSHELL is the extended stored procedure which is used to execute the Operating System command from the SQL Server TSQL language. It is stored in Master database.

Example:

exec master.dbo.xp_cmdshell 'dir D:\backup\*.bak'

exec master.dbo.xp_cmdshell 'mkdir "D:\backup\29dec2013"'

 

Steps for Enable the XP_CMDSHELL 

1. Go to the SQL Server Management Studio

2. Connect with instance with super user (sa user)

3. Use the master database

4. Execute the following command for enable xp_cmdshell

Use Master
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE WITH OVERRIDE
GO

 

Steps for disable the XP_CMDSHELL

1. Go to the SQL Server Management Studio

2. Connect with instance with super user (sa user)

3. Use the master database

4. Execute the following command for enable xp_cmdshell

Use Master
GO
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 0
RECONFIGURE WITH OVERRIDE>
GO

EXEC master.dbo.sp_configure ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO

 

Following error occured on execution of xp_cmdshell if it is disabled:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.