Azure dataplatform also provides Azure SQL database as a relational database as a service PAAS which is fully managed by Microsoft.This helps the developers to build their apps very quickly and removes the overhead of database administration.
There are few methods to migrate an on premise SQL database to Azure SQL Database and in this article we will have a look at migrating them with two options.
1) Using BACPAC export and import.
2) Data Migration Assistant.
Using BACPAC export and import:
With BACPAC export and import firstly we need to export the SQL database from the on premise SQL instance as a data tier application.
To export – Open SQL Management Studio – Right Click on the desired database and click on tasks – select export data tier application.
Now we need to save them in bacpac format.
The exported bacpac file will be successful.
Now this bacpac file needs to be imported to the Azure SQL database. Now we need to connect to the Azure SQL database to from SQL Management Studio.
Once after it is connected right click on the database folder and select import data tier application.
Select the exported bacpac file from the local disk and select the new database name that needs to be mentioned. Here we need to choose the Edition of Microsoft Azure , size of the database and the service type for this database in Microsoft Azure.
Having selected the required option select import and the import operation will start.
After a successful import we can see the status to be green and result successful.
Now we can see the migrated database in the Azure SQL database which have been successfully imported. Now we need to provide the username and the connection strings to the application owner to access their data which is present on the Azure SQL database.
Data Migration Assistant:
We can use the SQL migration assistant with source and
target end points and migrate the data to SQL PAAS Azure easily.
Below are the readiness to be prepared for migrating the SQL data from on premise to Azure :
Download and install SQL data migration assistant.
We need to enable TCPIP on source.
Port 1433 must be accessible.
SQL browser service must be started.
Enable SQL remote connection must be enabled.
Once the Data Migration Assistant is installed open and click on new
Here we have two options assessment or migration. Assessment helps us to identify the readiness required for this migration and will let us know if any connection or prerequisites missing. Here we can click on assessment.
Now we can select the authentication type and click on next
Select the desired DB’s that needs to be migrated to Azure.
Now we have the option to click on start assessment
Check the assessment report once it is completed.
To Migrate – rerun the agent and choose the option migrate
and specify the source server details.
Once after its authenticated successfully now we have an option to choose the database that needs to be migrated.
Now we need to specify the target Azure SQL PAAS Db details and the credentials.
Once after its been authenticated successfully , we can see the schema objects from the source database which we would like to migrate to Azure SQL database.
For the schema to migrate successfully we need to deploy the schema which will help us to migrate the schema initially.
Later once the schema is populated successfully now we have an option to migrate the data.Click on migrate data.
Choose the list of tables that needs to be migrated.
Once the table have initiated the migration we can see the progress.
On a successful migration we get the below message.
The result of the online migration is that the data is successfully migrated to the cloud.
According to Embarcadero
Survey Report, 83% of respondents say that they have Microsoft SQL
database environment. The widespread adoption of SQL database is attributed to its growing application scope
and relevance in the modern enterprise IT setup. However, like other database applications, SQL Server also faces tuning and performance
issues despite the Database Administrators’ (DBAs) efforts to maintain optimal
Hardware and software issues turn SQL database inaccessible, which is due to the following reasons:
Network failure issue when
database is accessed by a user
Storage media corruption leading
to corrupt MDF files
Changes on SQL server account
Corruption in File Header and
Such cases of database
corruption can be resolved with the help of Microsoft suggested utilities like DBCC
CHECKDB. But these utilities are time taking, need more technical proficiency,
and may lead to data loss situations if not used properly.
Instead, using a database repair
software like Stellar Repair for MS SQL ensures a timely and secure recovery from almost all types of Microsoft SQL corruption
issues. The software repairs the corrupt MDF and NDF files, and recovers the
entire database as new or an existing DB.
Worldwide, Microsoft SQL server has the largest share – 23.8% in relational database management category, as per a survey conducted by iDatalabs, and the reason is Microsoft’s understanding of the Database Administrator’s needs – the flexibility to manage the database, effectively. Still the issues crop-up either due to an error in SQL database file or SQL server for example SQL Server Error 5172, where the file header for a MDF file is rendered incorrect;
server page level corruption in which corruption in a particular page leads to
corruption all over the database. Numerous such errors in SQL database leads to
corruption and Administrators perform DBCC check to check and resolve the
error. However, the limitations of the Microsoft inbuilt utility compel the DBAs
to search for an effective, more comprehensive solution to fix the MSSQL
issues. And amongst a few
good SQL recovery tools is a reliable software – Stellar Repair for MS SQL.
review the software features, its advantages and the disadvantages.
About the software: A Proven SQL Repair Solution:
Based on real used cases DBA admins find it easy to recover corrupt SQL server database with the help of this enterprise-grade database repair software – Stellar Repair for MS SQL. The software’s user-friendly interface and unique features are designed to repair the primary and secondary (MDF and NDF) database files, thus addressing almost all SQL database repair and recovery needs.
The Versions of Stellar Repair for MSSQL:
Stellar Repair for MS SQL is available in three editions:
Demo Edition: The Demo edition is available for download from Stellar’s SQL Recovery Software details page and is the best to evaluate the core functionality of the software. The Demo edition provides a free preview of all the recoverable components of MSSQL database. Users can view and verify database content. The Demo edition also offers a free Log Report.
Licensed Edition: The licensed software edition
allows to recover and save the entire MS SQL database in the available database
(beneath the original database) or as a new database at the specified location.
The paid version of the software repairs the corrupt or damaged database and
also helps to resolve the database errors.
The software should be
installed on the same system on which the corrupt database resides. The
minimum system requirements include Pentium class processor, 1 GB Memory, and
50 MB of Hard disk space.
As the software supports all Windows versions including
Windows 10 (32bit/64bit)/Windows 8.1 (32bit/64bit) / 8 (32bit/64bit)/Windows 7
(32bit/64bit)/Windows Vista (32bit/64bit)/Windows Server 2012 and Windows
Server 2008, it can be installed to repair SQL database of any size and
Server: 2016, 2014, 2012, 2008 and older versions
The Key Features of MSSQL Recovery Software:
A comprehensive solution to repair the corrupt and inaccessible MDF and NDF files and recover all database components including tables, triggers, keys, rules, stored procedures in recent as well as older versions. Besides, the software also recovers XML indexes and data types, column set property, sparse columns, and file stream data types.
Resolves SQL Database Corruption Issues and Database Errors:
The software helps resolve database corruption issues at the
time when Microsoft’s built-in utility DBCC CHECKDB fails. The software fixes SQL server database corruption errors
like 5171, 8942, 3414, and also fixes SQL
database issues like header file corruption, schema corruption, consistency
error, and recovers the SQL database when it is stuck due to the following
is in Suspect mode or Offline mode
or non-clustered index corruption
pending and database attachment issues
Transaction Log file in SQL server
of Deleted Records of SQL database
The repaired LDF file is available at the MDF-file saving location.
Provides Free Preview of Repaired and Recoverable Objects:
Stellar Repair for MS SQL scans the entire corrupt database,
repairs it, and displays the preview of recoverable items in a tree-like structure. DBAs can search for specific
entries, as the database is sorted before
display. The preview feature helps verify the original database with the
Recovers Selective components:
The software allows Administrators to select specific
objects from the recovered database and save them at a specified location. This is
particularly helpful in cases where the user doesn’t need to preserve all components of the database.
Recovers ROW and PAGE compressed data:
The software review shows that it recovers SQL Tables with
PAGE and ROW compression. Also, the software supports Standard Compression
Scheme for Unicode (SCSU) for SQL Server 2008 R2, and ensures recovery of the entire database without disrupting the original
Establishes connection automatically when disrupted:
The SQL Recovery Software establishes
the connection automatically, in case the connection is interrupted. This feature saves time and effort to start the
process all over again if the connection is
disrupted and the process is halted without complete recovery.
Saves Scanned Results:
Stellar Repair for MS SQL scans, repairs and saves the scanned results of repaired SQL database during
the repair process. DBAs save these repaired
files at a later stage in case there is time or space crunch.
Saves Log Report:
The product review also shows that the software saves a Log
Report of all the repair activities. Software users can verify the software
performance with the help of the Log
The advantages and disadvantages of using Stellar Repair for MS SQL software:
All MSSQL server and database related errors and maintains database integrity
corrupt and damaged SQL database files – MDF and NDF and also saves LDF files
repaired database as new Database to reduce further chances of corruption
all deleted records from SQL database
the repaired data in multiple formats – MS SQL, HTML, XLS, and CSV
to be installed on the same system on
which the corrupt MSSQL database resides.
An analysis of the software’s key features, advantages, and
disadvantages confirms that the software effectively repairs corrupt MS SQL
database files and recovers all components of the database. The capacity with
which the software resolves the database corruption errors where even the
Microsoft utility fails, makes it distinct. Its easy-to-use interface serves
both technical as well as non-technical users well. This SQL database recovery software
review shows that the product Stellar Repair for MS SQL provides all the
database repair and recovery solutions and never compromises on database
integrity. Considering how much a
downtime can cost such tools are great lifesavers. You can use this especially
if you do not have expert knowledge about database and recovery.
There might be cases in where we need to analyze transactions made by the SQL server users. Manually, it was difficult (almost impossible) to carry out this procedure therefore, I decided to spent few dollars on a product like LDF file analyzer. I asked from many of my friends or colleagues for the same and all of them provided their different opinions. A saturation point came in my life when I got frustrated while searching for a solution to read SQL server transaction log. One day I was scrolling down my FB account page where I saw this SysTools SQL Log Analyzer in advertisement section. I clicked on it and read all the reviews posted by the customers. After seeing its rating and reviews, I decided to use this product and thus, download trial version of this. Finally, I found a software for which I was looking from a very long time. Because of this analyzer, now I daily check SQL transaction log without spending my entire day for it. Through this review page, I want to share my working experience with the SQL LDF file reader.
Supports log file of SQL server 2016 and all below editions
SysTools SQL LDF File Reader : Introduction
The software is a great tool to open and analyze SQL server log file records. It enables customers to connect with the online SQL server and extract database & all log file records. This is a Windows-based utility that displays preview of all transaction activities, which are saved in a LDF file. Each record is shown with log activity, time, table name, query, etc. The software shows the total number of records saved in the LDF file, at the time of scanning it. What I personally liked about the product is that it also displays the username who had made changes in transaction. Generally, this feature helps a lot when something goes wrong in the server. The SQL server transactions like Delete, Insert, and update are shown by this product.
SysTools SQL Log Analyzer : Its Availability
LDF file viewer is available in 2 editions, which are illustrated below :
Free LDF File Reader : The free setup file of this product is available on official site of SysTools. You can download it to check functionality and then, decide whether you want to purchase it or not. This freeware permits users to preview only 50 records per table of a LDF file.
Full Version of Tool : In order to view unlimited tables and records, you have to purchase licensed edition of the software. The SQL Log Analyzer full version is further categorized in 3 parts :
Personal License : At rate of $299
Business License : At the cost of $799
Enterprise License : At price of $1499
You can purchase product by making online payment through visa or any other online method. Well, I am using the business license, which is completely fulfilling all my needs to read LDF file.
Features of SQL LDF File Reader
Extract Records From Live SQL Server : The software is having feature to extract records from Live SQL Server environment. One just needs to mention the server credentials and then, tool will automatically fetch out entire database with records.
Supports LDF File of All SQL Versions : SQL Log Analyzer permits users to open, read, and analyze entire log activities made in the server database. It supports extraction of data from SQL server 2016, 2014, 2012, and all earlier versions.
Interesting Option to Export Records : The most amazing and unique option of this product is that it is providing 3 option to save the extracted log file records :
Export as SQL Server Database
Export as CSV File
Export as SQL Server compatible script
Comfortable with All SQL Data Types : SQL LDF file reader supports the set of all advance data type. This includes sql_variant, geometry, Datetime2, geography, and datetimeoffset data types. Apart from this, SQL server installation is needed only to work in online mode with this tool.
Display User Identity in Log Records : A column with name Login Name is shown in preview wizard of the software. You can track the user id of the person who had made transaction in the server. Also, you can learn the time when the transactions were made.
Recover Deleted Records from LDF : The SQL LDF file viewer is capable of performing recovery from corresponding corrupted database. It extracts deleted records from the database, if the specified database is in Simple recovery mode.
Instantly performs SQL LDF File recovery process
No MS SQL Server is required to view LDF file
SQL server credentials are kept safe and secure
Filter log records to be exported at targeted path
Stores the resultant files at desired PC location
Works both with online and offline server mode
Also, supports analysis of several NDF file data
If you are using offline mode then, MDF file is mandatory to access LDF data.
Time to Conclude
SysTools SQL Log Analyzer is providing an easiest way to read SQL server transaction log. Depending upon the software performance and working, I would like to rate this utility 9.9 / 10. I am not rating product full because of only one that primary database file is required for working in offline mode
We will come across certain scenarios where we need to integrate our Exchange Servers with other products for additional functionality.
Few examples are installing an automated mail signature software on the Transport Server for customizing and applying the email signatures automatically to all users.
Installing an additional layer of Anti-spam product on the email servers.
Configuring a Meeting room solution for integrating our room mailboxes,Equipment mailboxes with additional product and integrating with meeting schedule display monitors.
Bringing up an archive solution for all the primary mailboxes and the list goes on.
Most of the products which are coming on the above category will require a centralized database where it needs to store its information related to the configuration,logs, reports,etc.
In most of the cases these products will be using SQL DB’s for storing these data and values.
So planning in these kind of scenarios is very very important.Since an improper configuration of SQL instance and installation of the related product can interrupt the Exchange functionality very easily.
When we come across these kind of scenarios its always better to interact with the associated product expert .Get recommendations from a SQL expert with regards to the SQL configuration for the product.
I have collected few things which will help an Messaging guy during these scenarios
1) First get in touch with a SQL person and explain him about the requirement of the SQL for the product.Basically what kind of job it writes and stores in the SQL.
2) Reach the associated product expert and get advise on configuring the SQL instance for the product.
If your environment is or the product that you are going to install is not that complex and does not require SQL enterprise then you can go ahead and install the local SQL instance on the corresponding Exchange Server.
I have collected few points based on my experience which might help during configuring SQL instance if you are going to configure the instance :
Make sure during the installation you select the SQL authentication mode and create a service account for the same. Though the builtin sa account will have the permission but its better to create a dedicated service account for the admin.
Advantages of selecting SQL server authentication mode
SQL Authentication is the typical authentication used for various database systems, composed of a username and a password. Obviously, an instance of SQL Server can have multiple such user accounts (using SQL authentication) with different usernames and passwords. In shared servers where different users should have access to different databases, SQL authentication should be used for better security since the users only with Windows authentication cannot connect to the database.
Also make sure that you set the file db and the log file value to some greater figure as below example
Make sure the auto-close option is set to false
Functionality of Auto Close :-
Having this option set to true will annoy us from unwanted SCOM alerts.
When we set this option to True the DB will go offline when it doesn’t receive any active connections and will resume back whenever it gets new connections. So when we have a SCOM agent monitoring this SQL we will get unwanted alerts from them.
Its better to set this parameter to false
Launch Management Studio –> Select the Database –> Right Click Properties–> Options –> “Auto Close” value to “False”
After the installation is done check the total and target server memory to see if its eating up any additional memory by executing the below query
Select * from sys.dm_os_performance_counters
where counter_name in
(‘Target Server Memory (KB)’,
‘Total Server Memory (KB)’)
or (object_name=’SQLServer:Buffer Manager’ and counter_name in(‘Buffer cache hit ratio’,
‘Page life expectancy ‘))
Also you can check the physical memory ,available committed memory , memory utilization percentage by running the below query
select * from sys.dm_os_process_memory
If we are not sure about these values its better after the configuration you can show your SQL installation to an SQL expert and check if all the settings are in right place.