Friday, October 17, 2014

Backup Encryption in Microsoft SQL server 2014

Microsoft releases newer version of SQL Server that is SQL server 2014. It is very close to the previous version of SQL Server 2012 release. For many, it is very hard to upgrade the newer version of SQL server because it requires money & resources. However, the newer version of SQL Server has many new features for organizations that can justify the cost and upgrade. The most notable features in SQL server 2014 are In-Memory OLTP, enhancement in Always-on, and enhancement in backup.

In this article, we will learn and concentrate on backup enhancement in SQL server 2014.

SQL server 2014 supports in-built backup encryption for the database. All the previous SQL server versions did not support database backup encryption that required a third party software to accomplish this task. With SQL server 2014, DBAs can encrypt the database backup while creating the backup of database. It can be accomplish by specifying the encryption algorithm.

Benefits of SQL Backup Encryption:
  • It helps in securing the data of SQL server database backup. When you try to create a backup of yourdesired database, SQL server 2014 will provide the option for backup data encryption.
  • It supports multiple encryption algorithms: AES 128, AES 192, AES 256, and DES up to AES 256 bit.
  • It is helpful for all the SQL server databases that are encrypted using TDE.
How to Encrypt SQL Backup: You can encrypt the SQL database backup during backing up the database by following methods:
  1. Maintenance Plan Wizard
  2. Using T-SQL
Maintenance Plan Wizard: It creates maintenance plan that is used by database agent on regular basis to perform the task. You can use it to perform database administrative tasks like integrity check, backup, statistics update, shrink database, cleanup history, rebuilt index, recognize index, and many more,  when you required.


Perform following steps to encrypt SQL database backup with Maintenance plan wizard.
  • Right click on the desired database, you want to create backup.
  • Now select ‘Tasks’ and then ‘Back Up’, a backup dialog box will be open.
  • Right click on the ‘Backup Devices’ and select ‘Back up a Database’.
  • You are on Backup option page, Tick ‘encrypt backup’ as shown in the below image.
  • Now select your desired backup algorithm & certificate key from the drop down list.
  • Click on the ‘OK’ button to accomplish the backup encryption operation.

Using T-SQL:

You can easily encrypt the backup of SQL server database with T-SQL during backup creation. Run below T-SQL command to accomplish the task:

BACKUP DATABASE [Database-Name]
TO DISK = D'\\ServerLocation\Backup\Database-Name_backup.bak'
WITH COMPRESSION
,ENCRYPTION (Algorithm-Name, SERVER CERTIFICATE = Key or Certificat)
,STATS = 10 GO

Recommendation:

It is very important to make a backup copy of your ‘Certificate or Asymmetric key’. During backup restoration or for attaching purpose, certificate or asymmetric key is required. Without it, you can not restore or attach your database on another server.
»»  Read More...

Monday, September 29, 2014

How to upgrade lower version of SQL server database to upper SQL server versions

The most important question severally asked by clients or junior DBAs, why should they upgrade to newer version of SQL server?

Upgrading to newer version of SQL server requires money, resources, time, training, and many more. So first decide you will have to need all these things when you are planning to upgrade to newer version of SQL server. As Microsoft stated SQL server 2005 is out of mainstream support that means you are not getting any security upgrade, support for SQL server 2005 & all lower versions. If you are still using SQL server 2005 or any lower version then I recommend you to upgrade latest version of SQL server that is SQL server 2014. You can get SQL server 2014 standard edition from here.

If you are upgrading to New version of SQL server then you are not going to get security updates only, you will have so many newer features in the newer versions like AlwaysOn, High-Availability, New features in database engine any many more.

If you have decided to upgrade to newer version of SQL server then don’t worry about your older database!!!

I will tell you different methods to upgrade your older databases on newly upgraded SQL server with step by step guide.

You can upgrade your lower version of SQL server database to newer version by any of the following methods:
  • Copy Database Wizard Method
  • Detach and Attach Method
  • Backup and Restore Method
Copy Database Wizard: Copy database wizard helps you in upgrading your SQL server to later version of SQL server. If you have chosen this method to upgrade later version of SQL server then adhere the following requirement:
 
  • No-one is trying to access the database during copy database wizard.
  • Don't try to rename your database during operation.
Here is step by step guide to perform database upgradation through copy database wizard method:
  • Connect to instance of database engineusing object explorer in SSMS.
  • Click Databases ->Right click on database -> Click on Task -> Click on Copy database.
  • Finish all the steps in database wizard.
After performing above steps, your database is available and upgraded automatically.

Note: To verify optimal performance of upgraded database, runs stored procedure (sp_updatestats) against the newly upgraded database.

Detach & Attach: It is another method to upgrade older SQL server database on newly upgraded SQL server. The database is immediately available & automatically upgraded to the newer version after performing Attach & Detach operations on the database.

Before using this method, please adhere the following restrictions:
  • Master, Model & MSDB databases created using lower version of SQL server cannot attached in the new version.
  • Make copy of data & log files. It helps in reattaching the original database on instances of original SQL server versions.
Here is step by step guide to perform database upgradation through Detach & Attach method:
  • Detach the SQL server database from instance of SQL serverby using stored procedure sp_detach_db.
EXEC sp_detach_db 'Your_Database_database;
  • Copy & move the data and log file of detached database.
  • Attach the copied data & log file to an instance of upgraded SQL server version using create statement of SQL server with for attach or for attach rebuild log options.
Note: You have the copied of log file then use For Attach option otherwise use For Attach-Rebuild-Log Option.

Backup and Restore: It is the best and easiest method to upgrade a SQL server database from lower version to later version of SQL server. For this, you have to just take the full backup of SQL server database (Read my previous article on: how to take full backup of SQL server database) from lower version and restore it on the newer version of SQL server.

Note: It is the preferred method by most of the DBAs for upgrading lower version of database on later version of SQL server.

Now point comes, what is the difference between Detach/Attach and Backup/Restore method?

As I have described above most the expert DBAs recommend BACKUP/RESTORE method to upgrade a database from lower version to higher version. It helps in disaster recovery scenario because you have backup of your database at some location. But this is not absolute, because if you want to upgrade a very large database from lower version to higher version then backup & restore method may take a lot of time which is very frustrating. At this situation, detaching/attaching a SQL server database is a better way to upgrade a database. Detaching & Attaching operation is very fast but you need to aware that detaching a SQL server database will bring your database offline for a short time. And it does not provide disaster recovery option also.
»»  Read More...

Monday, September 22, 2014

Tips to Recover Data from Corrupt MySQL Database

Stellar Phoenix Database repair for MySQL can help in recovery of data from corrupt or damaged MySQL database. In this article I will show how to repair corrupt or damaged MySQL database’ file when it becomes corrupt.

Corruption in MySQL database can happen due to any uncertain reasons. Hardware problem, virus attach, power failure, bugs in MySQL itself, automatic system shutdown are the most common causes for MySQL database corruption. A bad hard drive can write garbage value in the MySQL database and then MySQL reports error messages.

“Cannot load from mysql.proc. The table is probably corrupted”
“MySQL error code 145= Table was marked as crashed and should be repaired”


There are several ways available to recover data from corrupt or damaged MySQL database:

InnoDB Force Recovery

It helps in recovery of data from corrupt MySQL database. By default, 0 is the value of innodb force recovery and database administrator can change it from 1 to 6. A higher value of InnoDB force recovery includes all the functionalities of lower values. For Example, 6 includes all the functionalities of values 5, 4, 3, 2, & 1.

Note: Only use InnoDB force recovery value greater than 0 when there is an emergency situation and make a backup copy of your database before trying it. Backup will help you in recreating the database when needed.

Steps to use InnoDB force recovery:
  • First of all stop MySQL server (mysqld) and copy all the files from /var/lib/mysql.
  • Use innodb-force-recovery to mysqld.
[mysqld]
Innodb_force_recovery=5
  • Start the MySQL server.
  • Dump all the databases.
  • Now restore the database from dump.
Restore from Backup

If the above method does not help then try tested & updated backup. It is the ideal solution to recover database from corrupt database file. Go with the next method, if backup is not available or too old.

Stellar Phoenix Database Recovery for MySQL

The only remaining method is use Stellar Phoenix database repair for MySQL software if above solutions fail to restore the database from corrupt MySQL InnoDB database.

Stellar Phoenix database repair for MySQL is developed by Stellar data recovery and the current version is 4.0. It is available for Windows as well as for Linux but you need to copy your MySQL database from Linux machine to Windows machine and run repair operation over there then after restore the database from Windows machine to Linux machine.
»»  Read More...

Wednesday, July 30, 2014

Tips to Repair Corrupt SharePoint Database


Application started reporting an IO error in the SharePoint database, and end users started getting below error message:

Cannot complete this action. Please try again

Solution: If you have encounter above error message in the SharePoint database then try following methods to fix it.

Try command line tool - Databaserepair: Stsadm operation
It detects & deletes orphaned items from corrupt SharePoint content database. SharePoint content database can be corrupt or damaged due to any uncertain reasons like hardware failure, power failure, virus attack etc. and corrupted SharePoint database may contain orphaned items. An example of orphaned item is a document may not have parent document.

Here is the syntax of Databaserepair - Stsadm operation:

Run follow syntax to detect an orpahned site
stsadm -o databaserepair -url <url name> -databasename <database name>
Example: stsadm -o databaserepair -url <http://myfirstsite.com> -databasename <my first site>
Run follow syntax to detect an orpahned site
stsadm -o databaserepair -url <url name> -databasename <database name>[-deletecorruption]
Example: stsadm -o databaserepair -url <http://myfirstsite.com> -databasename <my first site> -deletecorruption


Force PowerShell to Repair Content Database
To do this, you have to load the SharePoint management shell and run following equivalent powershell command:
$db = Get-SPDatabase "Content_DB_Name";
$db.Repair($false);
$db.Update();

If both the above methods fail to repair corrupt SharePoint database then check the consistency of your database by disconnecting it from the SharePoint. You can detach a content database from SharePoint using “Deletecontentdb: Stsadm” operation. It detaches a content database when database name, server and web application are specified.
Here is the syntax of Deletecontentdb - Stsadm operation:

stsadm -o deletecontentdb -url <url name> -databasename <database name> [-databaseserver] <database server name>

Now, follow below steps to check the consistency of database after detaching it:
  • Login into SQL server
  • Go to the SQL server management studio
  • Run following command:
    • USE WSS_CONTENT GO
    • ALTER DATABASE WSS_CONTENT SET SINGLE_USER GO
    • DBCC CHECKDB ('WSS_CONTENT') WITH NO_INFOMSGS
  • You will get error message details & recommendation to fix the error message.
  • Re-run DBCC CHECKDB with suggested repair clause (repair_fast, repair_rebuild, & repair_allow_data_loss).
Note: Some amount of data will be lost after performing repair_allow_data_loss, and the data loss is directly proportional to the level of corruption.
 
It is a 3rd party recovery software that easily detects & repairs corrupt SharePoint database. Software does not alter the content of database because it is read only in nature.


»»  Read More...

Sunday, June 29, 2014

Is Your SQL Server Database Need to Repair?


Run below command to check whether your SQL server database needs to be repaired or not

DBCC CHECKDB (Database-Name) with no_infomsgs

  • DBCC CHECKDB: It checks all the logical & physical integrity on the specified SQL server database.
  • Database-Name: Just replace the 'Database-Name' with your database name for which to run DBCC CHECKDB integrity. If you not specified the database name then current database will be used.
  • No-Infomsgs: It will remove all the information messages

If above command executed without any error message then there is no need to repair SQL server database but if there is error message(s) then you need to repair your SQL server database.

Note: If error message appears with DBCC updateusage then you need to run DBCC UPDATEUSAGE command on the database.

DBCC UPDATEUSAGE: It detects and corrects all the table related problems like rows, data page count, reserved page, used page etc. in specified database. Run below command to resolve table related problem in the database.

DBCC UPDATEUSAGE ({database_name} {table_name}) with no_infomsgs

If appears error message does not contain dbcc updateusage then you need to repair your SQL server database.

Why Need to Repair?
You can see the reasons from the Windows system & application event logs. SQL server logged possible cause for the problem in the windows system logs.

Suggestion: I will recommend you to check your database backup before repairing the corrupt SQL server database. If Backup is clean & updated then restore it.

How to Repair?
If backup is not available then you need to repair your SQL database. SQL server provides following repair options:
  • Repair_Rebuild: SQL server suggests it when there is no any possibility of data loss from the specified database.
  • Repair_Fast: SQL server suggests it when no repair is performed
  • Repair_Allow_Data_Loss: It will try to fix all the error messages with possibility of some data loss.
To perform any of the repair options, you need to put your database in single user_mode:

ALTER Database <database-name> SET Single_User

Here is the syntax of all repair options:

DBCC CheckDB ([YourDatabase], REPAIR_REBUILD)
DBCC CheckDB ([YourDatabase], REPAIR_FAST)
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

After repairing your database, don't forget to set your database in multiple user_mode. You can do this by running following command:

ALTER DATABASE [YourDatabase] SET MULTI_USER

What to Do after Repairing the Database?
After repairing your corrupt SQL server database, you need to do following things:

  • Re-Run DBCC CHECKDB: It is very important to re-run DBCC CHECKDB command on the database after repairing. If it comes out without any error message then it is conform that there is no any inconsistency in the database.
  • Make Full Backup of Database: If DBCC CHECKDB comes out cleanly then take a full backup of your database and keep it at safe place. Backup will help you in restoring of database.
  • Test Your Backup: Verify your backup by restoring it on another server. Successful restorations can only guarantee that your backup is consistence.
  • Make Disaster Recovery Plan for Future: Make a proper disaster recovery plan for future.
»»  Read More...

Wednesday, June 18, 2014

Save $549 on SQL Toolkit bundle from Stellar Phoenix


Stellar Phoenix, a pioneer in data recovery software & services now offering SQL toolkit bundle for all SQL server DBAs. SQL toolkit bundle has three essential tools that help you in recovery of corrupt SQL database, backup file and forgotten password.

https://secure.element5.com/esales/checkout.html?PRODUCT[300624711]=1&COUPON1=SQLTULK-EN&HADD[300624711][ADDITIONAL1]=Stellarinfo.com

Tools in SQL Toolkit Bundle:

Stellar Phoenix SQL database repair tool repairs corrupt or damaged SQL server database files (.mdf & .ndf). Software supports SQL server 2012, 2008 R2, 2008, 2005, & 2000.

Stellar Phoenix SQL Backup Recovery tool safely restores corrupt or damaged SQL database backup file into a new database.

Easily recovers lost or forgotten password of SQL server system administrator (SA) and all users.


You save 50% on the purchasing of all three essential tool individually.
So don't wait, buy & Save $549
»»  Read More...

Tuesday, June 17, 2014

Is Full Backup is Sufficient to Recover SQL Server Database?

Data is very important for everyone, ranging from individual to big organizations. For me, data is my life. I can’t think, what will be my life without data. So I regularly manage and update data. If you think that your data is also important for you as of mine then you should also manage and update your data.

What is Data?
Data can be anything like list of records, phonebook, measurements, survey records, employee records or even a description of any things. In the other words, you can say that data is a set of records.

How to Manage your Data?
Creating copy or copies of your data is the primary method for data management. Just create copy of your important data and store it on external hard drive, pen drive, or any other removable media so that you can recover any lost or deleted data from backup copy. Fortunately, Microsoft SQL server provides backup & restore facilities for data management.

SQL Server Backup
Backing up of your SQL server database is very important for protecting your database from data loss. SQL server facilitates for backing up transaction log, whole database, partial database or sets of files & filegroups. For these, SQL server offers following backup techniques:
  • Tranaction Log Backup: It records all the changes performed in the database that were not available in the previous log backup. To create a transaction log backup, you need to create at least one full backup of your database before. I recommend you to take a transaction log backup of your database frequently that will minimize data loss exposure and truncate the old transaction log.
  • Full Backup: It is also known as database backup because it backs up whole database including transaction log. It can be performed on the basis of your business need. Full backup contains the data at the time of full backup finished.
  • Differential Backup: It is faster in comparison of full backup because it contains the data since last full backup.
  • File Backup: It backs up one or more SQL server database files or filegroups. You can backup whole filegroup instead of individual files.

Data Loss, it can happen to anyone
You should always prepare of it. It can happen with anyone at anytime. So it is very important of everybody to prepare of it and make a suitable disaster recovery plan to avoid data loss and minimize the business downtime when data loss event will occur.

So now you may need to make a full backup of your SQL server database. You can create full backup of your SQL server database by using SQL server management studio, transact-SQL, or powershell.

Is Full Backup is Sufficient Enough?
If you have a read-only SQL server database or data in your SQL server database is very static then full backup is sufficient for you. Possibly you may recover your whole database with the help of full database backup.  But think differently, your database experiences frequent modification regularly then full backup is not sufficient for you because it does not contain data after full backup. To recover every bit of your data, you need to create other backups such as differential or transaction log backup. I have already discussed about these backups in the above paragraphs.

What is the role of recovery model in transaction log backup?
SQL server facilitates database administrators to manage their transaction logs through recovery model. There are three types of recovery model in SQL server: Simple, Full & Bulk-Logged. A database administrator can take backup of transaction log when his/her database in Full or Bulk Logged recovery models but this is not possible in simple recovery model.

Points to Ponder:
  • Make backup of database
  • Data is key to success
  • Regularly update database backup
  • Different types of backup required to recover database
  • Simple recovery model does not allow for transaction log backup
I hope that you have enjoyed to reading my article. As always, share your thoughts through comment.
»»  Read More...

Wednesday, June 11, 2014

7 Tips to Improve SharePoint Server Performance


Hi friends, today I am going to share one of my favorite topics in SharePoint that is performance improvement. Microsoft SharePoint offers good performance even during backup & restore operation is running but I will share some tips that will make your SharePoint server better than before. Backup & Restore operation of SharePoint use SharePoint server resources and slowdown the server performance while operations are running. Follow below tips to minimize the server resource usages and improve the server performance.



Tip 1: Perform Backup on Local Disk

To improve the SharePoint performance, it is very important to make a backup of SharePoint database on a local disk on the server instead on server network drive. After making the backup on local drive, you can easily copy the database backup on network drive. If you don't have local disk on the server then it is recommended to use a network drive with same latency as of server.

Tip 2: Maintain Small Databases

Small databases help in faster backup & restore. If you have a big database or planning for it then it is recommended to split it into small databases that will help you in faster recovery. For practice, use multiple small databases for a website application instead of having a big database.

Tip 3: Use Database Backup Compression Technique during Backup

Firstly, it was introduced with SQL server 2008. Compressed database backup increases the backup speed and significantly requires less to restore because it is smaller than uncompressed database backup. It helps in faster backup & restore of SharePoint database.

Tip 4: Prefer Incremental Backup for Large Database

Incremental backup back ups data since last backup only. Last backup can be either full backup or differential backup. There is a small difference between differential & incremental backup, differential backup back ups data since last full backup while incremental backup back ups data since last backup. Incremental backup is fast and small in comparison of differential backup because it back ups data since last backup. It helps in improving the database performance as well as minimizes the changes of data loss.

Tip 5: Choose Backup Type Wisely

SharePoint uses SQL server to store database and SQL server backup uses transaction log, differential & full to minimize the recovery time of database. If you are using simple recovery model of your database then there is no need to truncate the transaction log files because SQL server does not maintain transaction log if database is simple recovery model. But if you are using full recovery model for your database, then I will recommend you to truncate the transaction log file periodically to avoid the database maintenance problems.

Tip 6: Avoid Conflicting Issue

To avoid conflicting issue in the database, it is recommended to don't run backup operation during peak hour. Typically SharePoint server runs 27*7, so it is best practice to run incremental backup to safeguard data against server failure.

Tip 7: If Possible Try to Use RAID 10

RAID stands for redundant array of independent disks. It combines multiple disk drives component into a single unit for performance improvement. If you are using RAID on the SharePoint server then I will recommend you to use RAID 10 because it reads & writes data faster in comparison of other RAID levels.  
»»  Read More...

Thursday, May 22, 2014

Fix SQL Server Login Issue: Login failed for user 'Username'


In this article I will try to list all the steps which require to follow when you receive “SQL server error message 4064 - Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064”



Possible Cause: Above error message appears due to several reasons like database is dropped that is default database for some of the users, desired database may be corrupt, database is office, database may be renamed etc. When any cause is true then you will receive above error message.

Default Database: Before going to fix above error message, lets take a look at the default database. In SQL server management studio, whenever you connect to any SQL server database then by-default database is 'MASTER database'. It is selected by default and forces you to change from database drop-down list. It is very annoying situation. If you want to change your default database then follow below steps:
  • Open SQL server management studio (SSMS)
  • Point to the object explorer –> Security –> Logins
  • Now right on the Logins and select properties
  • A properties window will be appear
  • Change your default database from MASTER to desired database

Note: You can also change the default database by running below stored procedure:

 Exec sp_defaultdb@loginame='TestUser', @defdb='Test'     

Resolution/Fix: This error message does not appears frequently, but if you have received then here is a simple fix for it. You can connect your MASTER database and then change default database. To do this you need to follow below steps:

  • Open SQL server management studio
  • Now point to the 'File' button at top-left and click on it
  • Go-to connect object explorer



  • Now type the user-name & password and click on the 'options>>' button as shown in above image.
  • From connection properties tab, change the connect-to-database to master or any existing database name on your server. As I have discussed earlier, MASTER database is the default database by default.
  • After setting the database name to connect-to-database, click on the 'Connect' button.
  • You will successfully login in your system.
  • Now run this statement through your username.

ALTER LOGIN [username] with Default_Database = yourdatabasename

  • Error message resolved.

»»  Read More...

Thursday, May 15, 2014

Top 3 methods to Repair MySQL Tables

Corruption in MySQL database table can happen due to any uncertain reasons, and some of the top reasons are bugs in MySQL itself, virus attack, power failure, hardware problem, OS problem etc. If this happens with you then to repair MySQL table is the only solution you can try. Note that MySQL table repair may cause of valuable data loss because of limited functionalities of in-build MySQL repair utility or table has been badly corrupted. So make a proper backup of your MySQL database before trying any repair method.

When you have verified that your MySQL table gets corrupt, use one of the following methods to repair it.
  1. Repair MySQL Table using MySQLCheck
  2. Repair MySQL Table using phpMyAdmin
  3. Repair MySQL Table using 3rd party Software
Repair MySQL Table using MySQLCheck: It is a table maintenance program that checks, analyzes, repairs & optimizes MySQL tables. MySQLCheck is similar to myisamchk, but myisamchk must be run when MySQL server is running while MySQLCheck must be run when MySQL server is not running.

Check MySQL Table
# mysqlcheck -c database-name table-name -u root -p
Enter password:
database-name.table-name OK

Repair MySQL Table
# mysqlcheck -r database-name table-name -u root -p
Enter password:
database-name.table-name OK

Repair MySQL Table using phpMyAdmin: It is the easiest method to fix corrupt MySQL table. Follow below steps to repair corrupt MySQL table:

  • Open your phpMyAdmin client, enter the username & password then click on 'Go' button as shown in the image.



  • Select your MySQL database from left drop-down menu and click on the desired database as shown in the image.


  • Now you will see all of the tables of your selected MySQL database. In below image, there are 11 tables for the selected database.


  • Tick-mark on the check-boxes of tables that you want to repair. For example, I have selected 'wp_options' table as shown in the image.


  • Now click on the drop-down arrow of 'with selected' option and click on 'repair table'. Take a reference from below image.


  • After that you will get a conformation message saying that “your sql-query has been executed successfully”.
Repair MySQL Table using 3rd Party Software: There is platy of 3rd party MySQL repair utility available online that claims to repair & recover data from corrupt MySQL table. But I generally recommend Stellar Phoenix Database Repair for MySQL because it is trusted software to repair corrupt MySQL tables. It uses advanced algorithm to scan & repair corrupt tables. Software does not change anything of your system because it is read-only in nature. Software repairs myisam & innodb storage engine's table and supports MySQL 6.x, 5.x, 4.x, and 3.x versions. If you know & believe any other software that can do your job they go with that. Choose the right software that will make you happy.

Your Turn: I love to hear from you guys! Share your opinion through comment.
»»  Read More...