Category Archives: SQL

Migrate onpremise SQL DB to the Azure SQL Database

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.

Thanks & Regards

Sathish Veerapandian

Product Review: Stellar Repair for MS SQL Database

According to Embarcadero Technologies’ Database 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 performance.

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 more

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;

SQL 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.

Let’s 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.

Software Requirements:

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 version.

Compatibility:

MS SQL 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 issues:

  • The database is in Suspect mode or Offline mode
  • Clustered or non-clustered index corruption
  • Recovery pending and database attachment issues
  • Damaged Transaction Log file in SQL server
  • Recovery 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 recovered components.

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 format.

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 Report.

The advantages and disadvantages of using Stellar Repair for MS SQL software:

Advantages:

  • Resolves All MSSQL server and database related errors and maintains database integrity
  • Repairs corrupt and damaged SQL database files – MDF and NDF and also saves LDF files
  • Saves repaired database as new Database to reduce further chances of corruption
  • Recovers all deleted records from SQL database
  • Saves the repaired data in multiple formats – MS SQL, HTML, XLS, and CSV

Disadvantages:

  • Needs to be installed on the same system on which the corrupt MSSQL database resides.

Conclusion:

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.

SysTools SQL Log Analyzer Tool Review – Views of the SQL Expert

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.

Overview of SysTools SQL Log Analyzer

Product Name SysTools SQL Log Analyzer
Version 3.0
Size of EXE File 6.1MB
Prerequisites MDF file is required for analyzing the log file
Windows OS Windows 10, 8.1, 8, and all below versions
SQL Server 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.

S1.png

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

  1. 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.

S1.png

  1. 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.

S1.png

  1. 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 :
    1. Export as SQL Server Database
    2. Export as CSV File
    3. Export as SQL Server compatible script

 

S1.png

  1. 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.
  2. 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.

S1.png

  1. 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.

Pros

  • 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

Cons

  • 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

Thanks & Regards

James Smith 
Data Recovery Expert

Error occured while establishing a connection to the SQL server

Recently in one of our application while trying to configure  reporting services configuration we were getting the below error while trying to connect to a SQL database.

IMG2

Checked the remote server connections for the database and it was enabled

SQL4

 

Went into the component services and checked the local DTC connection

 

Test3331

Network DTC access was disabled and hence the issue.

IMG-1.jpg

Enabled them and after MS DTC service restart checked UDL connection for the affected database on that instance.

Final

In addition to the above we can also check the execution account permission on the SQL database server.

This can also happen if the SQL service state is not running.

Make sure SQL Server service status is Running.
Also make sure the TCP/IP communication is enabled on the SQL server configuration manager on the instance where the problematic DB exists

final2

By default SQL Server runs on port 1433, if the default port is changed then these new ports should be added in the firewall exceptions.

You can also check the connectivity to the SQL Server by the below commands

netstat -ano| findstr 1433

You should get a successful  TCP listening establishment on the SQL server IP address and on port 1433 .

Hope this helps

Thanks & Regards 

Sathish Veerapandian 

MVP – Office Servers & Services

Creating SQL instances for messaging related services

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.

SQ

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

SQ1

 

 

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”

Autoclsoe

 

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

SQLM

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.

Thanks 

Sathish Veerapandian

MVP – Exchange 

%d bloggers like this: