Tag Archives: sql server

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

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

 

 

Check the size of database and table in Microsoft SQL Server

Check the size of Database and table in Microsoft SQL Server

Check the Size of Database in SSQL Server

use "ADVENTUREWORKS"
exec sp_spaceused

Output:

database_name   database_size   unallocated space
adventureworks  17899.13 MB     5309.39 MB


Find the Size of Database files in Microsoft SQL Server

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) like '%AdventureWorks%'
order by SizeMB desc

Output:

DatabaseName    Logical_Name       Physical_Name                    SizeMB
ADVENTUREWORKS  ADVENTUREWORKS     D:\MSSQL\Data\adventure.mdf      21453           AVENTUREWORKS   ADVENTUREWORKS_log D:\MSSQL\Data\adventure.ldf      5234


Check the size of database

sp_helpdb'adventureworks'

Check size of data files in database

USE AdventureWorks;
GO
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;

Check Size of Table

sp_spaceused 'MyTable'

Add column in table of Oracle, Sql Server, Mysql & Postgresql

Add column in table of Oracle, Sql Server, Mysql & Postgresql

Sometime after creating the database schema and sturture we have to modified or add column in the table for future updates. So we need to modified the structure of the tables for future deployment. We can use this command to add column in databases.

Following are the example of add column in the table

Syntax:

ALTER TABLE

ADD datatype;

Example
Suppose need to add one more column in employee table
alter table employee add dateofjoin datetime;

SQL Server

ALTER TABLE table_name ALTER COLUMN column_name datatype;
ALTER TABLE dbo.table_name DROP COLUMN column_name;

Oracle

Alter table table_name modify column_name datatype;
alter table table_name drop column col_name1;

MySQL

ALTER TABLE MODIFY COLUMN datatype;
ALTER TABLE DROP ;

PostgreSQL

ALTER TABLE ADD COLUMN datatype ;
ALTER TABLE DROP COLUMN RESTRICT;

DBCC CHECKALLOC in Microsoft SQL Server

DBCC CHECKALLOC

Checks the consistency of disk space allocation structures for a specified database.

Syntax:

DBCC CHECKALLOC
( 'database_name'
[ , NOINDEX
|
{ REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
)    [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]

Parameter Details
REPAIR_ALLOW_DATA_LOSS: Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair can be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database.
REPAIR_FAST: Performs minor, non-time-consuming repair actions such as repairing extra keys in non-clustered indexes. These repairs can be done quickly and without risk of data loss.
REPAIR_REBUILD: Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.
DBCC CHECKALLOC checks allocation and page usage in a database, including indexed views. The NOINDEX option, used only for backward compatibility, also applies to indexed views.

DBCC CHECKDB is the safest repair statement because it identifies and repairs the widest possible range of errors.

Examples

-- Check the current database
DBCC CHECKALLOC
GO
-- Check the pubs database.
DBCC CHECKALLOC ('pubs')
GO