Showing posts with label Database connectivity issues. Show all posts
Showing posts with label Database connectivity issues. Show all posts

Monday, 14 August 2017

Migrating on-premise SQL Database to Windows Azure SQL Database

Migrating on-premise SQL Database to Windows Azure SQL Database


Migrating SQL database(s) from one server to another is one of the common tasks for SQL DBAs. There are several ways to migrate a SQL database(s). I have already covered how to migrate SQL DB on premise here. Today we will see how do we migrate an on-premise SQL Database to Azure SQL Database. Please follow the below step by step procedure to migrate your on-premise SQL Database to Azure-

1.       You need to login to the Source Server first. This is the Server where your Database, which needs to be migrated to Azure, resides. Login with your account and go to the Source database-> Click on Tasks-> Deploy Database to Windows Azure SQL Database.
Migrate your database to Windows azure SQL Database


2.       Upon clicking on Deploy Database to Windows Azure SQL Database you will be presented with the Deploy Database screen which will be an Introductory screen to provide you some information regarding the Wizard. Click Next

Migrating on-premise SQL Database to Windows Azure SQL Database


3.       Next screen will be Deployment Setting Screen where you need to provide the details for the target Server. Under the Specify Target Connection pane you will see a tab to enter Server connection. Click on the connect button which is situated at the right side of the server connection box. A new Connect to Server window will be popped up and there you should furnish the details of the target Server name and Authentication details. After entering the details click on connect.
Migrate your SQL Database to Windows Azure SQL Database, specify target server name
4.       Upon Clicking next, you will be presented with the below window where you can see the connected server name. you need to furnish some details like the new database name and other seeing details like Edition of windows Azure SQL Database, Maximum database size in GB and Service Objective. Here in our example I have selected “Standard” as the edition of Windows Azure SQL database and 250 GB is what I have mentioned as the Max size of the database and S2 as the Service Objective.

Migrate your SQL Database on Azure

Also, you need to specify a backpac file path in the Deployment Settings window. Once you are done with all these things, click next.

5.       Upon clicking next, a summary page will be shown which will have all the details about Source Server and database, target server and database and environment details.
Step by Step document for Migrating SQL Database to Azure


6.       Validate if all the above details are correct and proceed to click finish. Upon finishing the wizard you will see Operation complete message on the screen once all the operations such as Exporting database, Validating database, processing objects, importing database are complete.

Migrate SQL databases to Windows Azure


7.       To verify whether your newly migrated database has been migrated successfully or not you can go to Portal and check it. Login to portal.azure.com with your credentials and see if the newly created database has been created under SQL Databases Pane. In our example, I logged into Azure portal and verified that the database with the name “test” has been created successfully under the SQL Databases pane.
Migrating on-premise SQL Database to Windows Azure SQL Database

Hence We have seen that Migrating an on-premise SQL server database to Windows Azure SQL Database is very easy and straight forward.

Friday, 16 June 2017

Common SQL Server DBA Issues

SQL Server DBA Issues Handbook


In day to day activities we as DBAs receive lot of issues which can be resolved by following so simple steps. We will discuss some common SQL Server DBA Issues below-

Disk space full

Overview: Sufficient space in the disk is very crucial for all the databases to grow and perform transactions smoothly. Over the time, the database grows as a result in the growth of the data. Effective Capacity Planning should be done to overcome such challenges. Many a times log of the database grows due to some open transactions/log running queries and for such operations separate necessary actions should be taken to free up the disk space. Often storage gets full due to unwanted files on the disk which should be identified appropriately.

Actions to be taken:

1. Check for any unwanted files in the location for which the alert has been generated (e.g. C: ,D: etc.).
2. The majority of the issues related to disk space are because of log file growth. The growth typically occurs for one of two reasons:
  • The database is in FULL recovery and transaction log backups aren’t being taken for some reason
  • Someone runs a single, very large transaction which consumes all existing log space, forcing the file to grow large.
In either case Auto growth and pre-sizing (limitations on log growth) should be set accordingly.

Database is offline

Overview: Database being offline is one of the most common SQL DBA issues. In case any of the Database goes down or the status changes to OFFLINE then it is important for us to know the reason why the database has gone offline. SQL Server error log should be read thoroughly to know the reason.

Action to be taken:

Please check the SQL server error log to know the details of the database activities before the database went offline. You can navigate to run-> eventvwr->Windows Logs
                                “OR”
Connect to SSMS-> sp_readerrorlog
Once the reason is known below steps can be performed to bring back the DB online-
Open Management Studio and in the "Connect to Server" dialog box enter your connection parameters
Click the "Options >>" button and navigate to the "Connections Properties" page
In the "Connect to database" dialog box type the word master.
Open up a New Query window and paste the following T-SQL code into it:
                                                                                                                                          
USE [master]
GO
ALTER DATABASE [database_name] SET ONLINE
GO
Database can be brought online using GUI as well. To do so you need to perform the below steps-
Open SSMS-
Run->SSMS->Enter

SQL DBA issues -Database is Offline

You will be able to see the database status for all the databases in Object Explorer. Once you identify the Offline Database, right click on the same and you’ll have to select Tasks-> “Bring Online” as shown in the figure below-

DBA ISsues -How to bring SQL DB Online

Upon clicking the Bring Online option your Database will come online and users can get connected to it. You will see the below screen after the database comes online successfully.

SQL DBA issues DB Online


SQL Server Job has failed

Overview: SQL Server agent jobs or SQL Server jobs are predefined tasks performed in SQL Server at scheduled time. Any change in permissions or job related objects may cause a job to be failed.

Action to be taken:

Get Details about specific failed job
Job named TestJob has been failed for some reason. Now we have to further investigate several aspects about its failure. For this purpose

Go to Jobs folder->Right click on failed job->Click on View History

Troubleshoot SQL Server Job failure
It will present previous history of this specific failed jobs. By further drilling any record of failure, you may find further details and the reason of job failure.

Sql Server Job is failing





Database connectivity issues

Overview: Many times, clients are unable to connect to the Servers or the front-end applications which in turn get connected to the database server. The problem could be due to various reasons but one of the major issues is authentication issue.

Actions to be taken:

   Authentication Method

To inspect the authentication mode of your SQL Server:
1.  Open SQL Server Management Studio. Then, right-click on the server and select Properties in the popup menu.
2.  In the Server Properties dialog box, select Security.
common SQL DBA issues
3.  Make sure Server Authentication is set to SQL Server and Windows Authentication Method.
Common SQL DBA Issues

User is unable to connect to the database due to inappropriate permissions

Overview: Users are unable to connect to the database. Problem could be either access rights have been modified or it’s a new user.

Actions to be taken: Get details about the User's Login and check the error. The error could be due to incorrect passwords, Modification/Revocation of access rights.

Login Failed for user

Backup/Restore is failing

Overview: The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

Actions to be taken: Please read the below error codes and action should be taken as per the error. Please read the article which I have published here(please click for the link) for some backup related scripts. You can know the granular level details which are specific to backup and restore in sql sever by going through the article.


SQL Server Backup and Restore Alerts
ID
Category
Error
Severity
Description
1
Backup Success
18264
10
Database backed up: Database: %1, creation date(time): %2(%3), pages dumped: %4!d!, first LSN: %5, last LSN: %6, number of dump devices: %9!d!, device information: (%10).
2
Backup Failure
18204
16
%1: Backup device '%2' failed to %3. Operating system error = %4.
3
Backup Failure
18210
16
%1: %2 failure on backup device '%3'. Operating system error %4.
4
Backup Failure
3009
16
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
5
Backup Failure
3017
16
Could not resume interrupted backup or restore operation. See the SQL Server error log for more information.
6
Backup Failure
3033
16
BACKUP DATABASE cannot be used on a database opened in emergency mode.
7
Backup Failure
3201
16
Cannot open backup device '%ls'. Device error or device off-line. See the SQL Server error log for more details.
8
Restore Success
18267
10
Database restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
9
Restore Success
18268
10
Log restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
10
Restore Success
18269
10
Database file restored: Database: %1, creation date(time): %2(%3), file list: (%4), number of dump devices: %6!d!, device information: (%7).
11
Restore Failure
3142
16
File '%ls' cannot be restored over the existing '%ls'. Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files.
12
Restore Failure
3145
16
The STOPAT option is not supported for RESTORE DATABASE. You can use the STOPAT option with RESTORE LOG.
13
Restore Failure
3441
21
Database '%.*ls' (database ID %d). The RESTORE statement could not access file '%ls'. Error was '%ls'.
14
Restore Failure
3443
21
Database '%.*ls' (database ID %d) was marked for standby or read-only use, but has been modified. The RESTORE LOG statement cannot be performed.
15
Restore Failure
4301
16
Database in use. The system administrator must have exclusive use of the database to restore the log.

SQL Database recovery model needs to be changed.

Overview: A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time. The model database sets the default recovery model of new databases.

Action to be taken:


Set recovery model using Management Studio
1. Right click on database name and select Properties.
2. Go to the Options page.
3. Under Recovery model select "Simple" or “Full” or “Bulk-Logged”
4.     Click "OK" to save.

Common SQL DBA issues day to day

So We have seen that the most common looking SQL Server DBA issues can be tackled very easily by following the above techniques.