Database Replication

Database Replication

 here are several uses for replication. For Example, disaster recovery or for reporting (analysis).

When used for Disaster Recovery, replication is generally setup between geographically dispersed data-centers. For example, you are hosting your servers with a hosting company that has multiple data centers located around the country. You can setup replication service which will allow your database to be replicated in one more data centers. If the database in your primary data center is inaccessible, traffic could be re-routed to the secondary data center.

Apart from the disaster recovery, another usage I have seen is that the data is periodically replicated from the primary server to the secondary server for running reports or heavy analysis. This way, you can run your reports, setup data cubes etc. on a secondary server while the primary server is mainly for online transaction processing.

Key difference between Transaction Log shipping, Mirroring and Replication is that the secondary databases in both log shipping and mirroring are in standby mode and are mostly unusable but in replication both primary and secondary databases are active.

Replication can be configured in several ways such as...

• Snapshot - Data is simply copied from the main server to the one or more secondary servers, sometime even to a secondary database on the same server.
• Merging Replication - Data from two or more databases is merged into a single database
• Transactional Replication - Full initial copies of the databases are configured on one or more secondary servers and then periodic updates are applied.

Distributed database management system ensures that any inserts/updates/deletes that are performed on the data in one location are automatically synched with other locations.

There are two types of replication - Synchronous and Asynchronous. In Synchronous mode, the data is replicated on both primary and secondary server(s) at the same time, while in asynchronous replication, there is a delay before data is written to the secondary server. While asynchronous is a better option for performance reasons, a data loss could occur in case of a disaster.

Replication generally occurs either in the storage array (array of disks where the data is stored), at the server or in the network. Most SAN vendors provide a replication software that you can use to setup replication at the storage array level. There are also software solutions to setup replication at the server level, when you don't have a storage array. This is usually a cheaper solution but could impact server performance. Network replication usually is hardware based using an appliance.

Below are some of the Key differences between Log Shipping / Replication

• Latency - A minute or more in Log Shipping, just few seconds in Replication
• Schema altered at the primary server (also known as publisher)?
• Log Shipping - No
• Replication
• Snapshot - No
• Transactional - No
• Merge / Updates- Yes
• A GUID column is added if one doesn't exist.
• Schema altered at the secondary server (also known as subscriber)?
• Log Shipping - No
• Replication - Depends - see below
• Snapshot and Transactional Replication may make schema changes. Standard Snapshot and Transactional replication will not transfer identity attributes. Instead they become normal numeric columns on the secondary server.
• Are there any requirements at the Database Schema level?
• Log Shipping - No
• Replication - Primary Keys are required on the tables involved in replication.
• Can you Select individual Articles?
• Log Shipping - No
• Replication - Yes
• Is the Secondary server usable 
• Log Shipping - Mostly No - Requires exclusive lock to apply the transaction log.
• Replication - Yes

Hopefully this article will provide you enough insight into replication and how to get started if you are thinking about setting one.

Thank you and as always, your comments are welcome.

Change SA Password in SQL Server & Identify when last time SQL Server SA Password was changed? How to enable SA account in SQL Server?

Introduction

In this article we will take a look at how to change SA Password in SQL Server using TSQL code and by using SQL Server Management Studio. The steps mentioned in this article are applicable to change any SQL Server Login Password works on SQL Server 2005 and higher versions.

When Last time SQL Server SA Password was changed?

Execute the below TSQL script to know when last time SQL Server SA Password was changed.

MORE SQL SERVER TROUBLESHOOTING ARTICLES

ALSO READ ... STEPS TO REBUILD SYSTEM DATABASES IN SQL SERVER
DETECT VIRTUAL LOG FILES IN SQL SERVER TRANSACTION LOG

Sign up today for MyTechMantra.com Newsletter
Like Us on FACEBOOK   |  Follow Us ON TWITTER

USE Master
GO

SELECT   
    name   AS [Login Name]
  , sid   AS [SID]
  , type_desc  AS [Login Type] 
  , create_date  AS [Created Date]
  , modify_date  AS [Last Modified Date]
FROM    sys.sql_logins
 WHERE   [name] = 'sa'
GO   

Different ways to change SA Password in SQL Server

• Change SA Password in SQL Server Using TSQL Query
• Change SA Password in SQL Server Using SQL Server Management Studio

---

Change SA Password in SQL Server Using TSQL Query

USE master
GO

ALTER LOGIN [sa] WITH PASSWORD=N'NewSAPassword'
GO

You will receive the below mentioned error message as the password specified does not meet windows policy requirements because it is not complex enough.

Error Message

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

It is always a best practice to set a complex password for an SA (System Administrative Account) in SQL Server to avoid unauthorized access. If you still want to set a simple password for an SA account in SQL Server which is not recommended; then you add CHECK_POLICY = OFF clause to the above query.

USE master
GO

ALTER LOGIN [sa] WITH PASSWORD=N'NewSAPassword', CHECK_POLICY = OFF
GO

There may be a scenario that once you try logging in to SQL Server using SA Password you get the below mentioned error.

Error Message

Login failed for user 'sa'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)

Enable SA Account in SQL Server

In such a scenario you need to go ahead and run the below TSQL code to enable SA account in SQL Server.

USE master
GO

ALTER LOGIN [sa] ENABLE
GO

---

Change SA Password in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio
2. Expand Security -> Logins -> Right click sa to open up Login Properties as shown in the snippet below.

In Select a Page choose Status page and on the right side pane choose the Enabled radio button for Login to allow login for SA password as shown in the below snippet.

Important Note Once SA Password has changed successfully you can login immediately with the new Password without restating SQL Server. In order for SQL Server Authentication to work you need to change Server Authentication as SQL Server and Windows Authentication Mode if not already selected. For more information on this refer the second page of the article titled “How to Identify Currently Used SQL Server Authentication Mode”.

Conclusion

In this article you have seen how easily you can change SQL Server SA Password using TSQL Code and by using SQL Server Management Studio.

How to Get Exclusive Access to SQL Server Database

Introduction There are scenarios when database administrator needs to disconnect all the connected users from a SQL Server Database to get exclusive access of the Database. The exclusive access is needed before restoring a database, before a database can be detached, to perform critical maintenance tasks, to recover a database which is in Suspect Mode etc. In this article we will take a look at how database administrator can leverage ALTER DATABASE Commands to disconnect users from a database.

Using ALTER DATABASE SET Options

Database administrator can executed ALTER DATABASE command to get exclusive or restricted access of a database.

Get Restricted Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to get Restrictive access to a database.

ALTER DATABASE DatabaseName
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO

Get Single User Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to get a Single User access to a database.

ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Give Multi User Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to give Multiple User access to a database.

ALTER DATABASE DatabaseName
SET MULTI_USER
GO

Difference Between SINGLE_USER, RESTRICTED_USER & MULTI_USER Alter Database SET Commands

SINGLE_USER

When SINGLE_USER WITH ROLLBACK IMMEDIATE command is used only one user can connect to the database at a time.

RESTRICTED_USER

When RESTRICTED_USER WITH ROLLBACK IMMEDIATE command is used any number of users who are in DB_OWNER, DB_CREATOR or SYSADMIN roles can connect to the database.

MULTI_USER

When MULTI_USER command is used any number of users who have rights to connect to the database will be able to connect to the database.

Why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?

Introduction If you are trying to restore a database on an SQL Server 2005 or a lower version from the database backup which was performed on an SQL Server 2008 instance then it will fail. In this article we will take a look at the reason why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?

Error Message received when restoring an SQL Server 2008 database backup on an SQL Server 2005 or lower versions

Msg 3241, Level 16, State 7, Line 1
The media family on device 'Drive:\BackupFolder\DatabaseName.BAK' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The reason for the failure is the internal version number of SQL Server 2008 is different from that of an SQL Server 2005 or lower versions. Since the internal version number of SQL Server 2008 is higher than that of an SQL Server 2005 database the restore process fails. To know more about internal version number of a database checks the following article How to identify if the database was upgraded from a previous version of SQL Server.
Important Note: - You will not be able to restore a database backup from a higher version of SQL Server to a lower version of SQL Server this is by design from Microsoft. Downgrade of a database is not supported using the database backup and restore method.

Why an SQL Server Database from a higher version cannot be attached onto a lower version of SQL Server?

If you try to attach an SQL Server database which was initially created on an SQL Server 2008 instance to an SQL Server 2005 or a lower version then the process will fail with the below mentioned error.

Error Message received when attaching an SQL Server 2008 database on to an SQL Server 2005 or lower versions

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DatabaseName'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'DatabaseName' cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

The above error message clearly states that the database cannot be opened in SQL Server 2005 instance as the database which you are trying to attach is of the version 655. The SQL Server 2005 instance can only open databases which are of version 611 or earlier. To know more about internal version number of a database checks the following article How to identify if the database was upgraded from a previous version of SQL Server.
In below table you will able to relate Database Compatibility Levels and Internal Database Version Numbers for different versions of SQL Server.

SQL Server Edition	Database Compatibility Level	Internal Database Version Number
SQL Server 7	70	515
SQL Server 2000	80	539
SQL Server 2005	90	611/612
SQL Server 2008	100	655
SQL Server 2008 R2	105	660
SQL Server 2012	110	706

Conclusion

In this article you have seen why you can’t restore or attach an SQL Server database from an higher version of SQL Server to a lower version.

How to Attach Database Without a Transaction Log File in SQL Server

Introduction

This article demonstrates how to attach a database in SQL Server when transaction log file is missing. The steps mentioned in this article are applicable on SQL Server 2005 and higher versions. To know more about how to Attach Database in SQL Server with transaction log file refer the following article “Attach Database in SQL Server”.

Different ways to Attach Database without a transaction log file in SQL Server

• Attach Database without a transaction log file in SQL Server Using SQL Server Management Studio (SSMS)
• Attach Database without a transaction log file in SQL Server Using TSQL Script

Permissions to Attach Database in SQL Server

You need membership in the db_owner fixed database role to Attach Database in SQL Server.

Important Notes:

You will not be able to detach and attach database from a higher version of SQL Server to a lower version of SQL Server this is by design from Microsoft. To know more read the following article “Why an SQL Server Database from a higher version cannot be attached onto a lower version of SQL Server?”

Downgrade of a database from higher version to lower version cannot be achieved using Database Backup and Restore Method and also by using Database Detach and Attach Method. However, you can downgrade a database to a lower version using SQL Server Integration Services. To know more read "How to Downgrade SQL Server Database Using SQL Server Integration Services".

Let us go through each of the above mentioned options in detail.

Attach Database without a transaction log file in SQL Server Using SQL Server Management Studio (SSMS)

1. In Object Explorer, right click the Databases node and then click Attach… from the drop down menu as shown in the clip below.


2. In Attach Databases dialog box, click Add… button.


3. In Locate Database Files dialog box, browse to the location where you database MDF file is available and then choose the file and click OK and to return to parent Attach Databases dialog box.


4. In Attach Database dialog box; you will see under database details as highlighted that SQL Server is unable to locate the LDF file. To attach the database without the LDF file select the LDF file and then click remove button. Finally, click OK to successfully attach the database. In this case SQL Server will create a new log file while attaching the database.

Important Note: -The above mentioned steps will work only when your database which was cleanly shutdown and you have the .MDF file of the database.

---

Attach Database without a transaction log file in SQL Server Using TSQL Script

Using the below script you can attach database in SQL Server which has a missing transaction log file.

USE [master]
GO

CREATE DATABASE [AdventureWorksDW2008R2] ON 
 ( FILENAME = N'D:\Downloads\SQL2008R2SampleDBs\AdventureWorksDW2008R2_Data.mdf' )
FOR ATTACH
GO

---

Execute DBCC CHECKDB

Once you have successfully attached a MDF file without a transactional log (LDF) file go ahead and execute DBCC CHECKDB to check the logical and physical integerity of all the objects in the specified database.

Use MyTechMantra
GO

DBCC CHECKDB
GO

Reference:- Database Detach and Attach (SQL Server)

How to Rename SQL Server Stand Alone Instance After Installing SQL Server

Introduction

In this article we will take a look at the steps you need to follow to rename a stand-alone instance of SQL Server. The steps mentioned in this article are applicable for SQL Server 2000 and higher versions.

Get Local SQL Server Name

Execute @@SERVERNAME built-in configuration function to return the name of local server that is running SQL Server.

SELECT @@SERVERNAME AS 'Server Name'
GO

Rename SQL Server Stand Alone Instance or Default Instance of SQL Server

In case if you are using a named instance of SQL Server, then you can just rename the host name and not the SQL Server Instance Name. For example, let us assume that you have a named instance of SQL Server as SERVERONE\SQL. Using the below script you can just rename the host computer name from SERVERONE to NEWSERVERONE. You cannot rename the instance name SERVERONE\SQL to NEWSERVERONE\NEWSQL. In case if you want to rename SQL Server Instance name then the only way out is to Uninstall SQL Server and Install SQL Server with a new Instance name.
Execute the below TSQL script for a renamed computer which hosts a default instance of SQL Server.

Use Master
GO
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

Important Note: - Once you have executed the above TSQL script, go ahead and restart SQL Server Instance Service using SQL Server Configuration Manager.

---

Rename Host Name of Named Instance of SQL Server

Execute the below TSQL script for a renamed computer which hosts a named instance of SQL Server.

Use Master
GO

sp_dropserver <old_name\instancename>
GO
sp_addserver <new_name\instancename>, local
GO

Important Note: - Once you have executed the above TSQL script, go ahead and restart SQL Server Instance Service using SQL Server Configuration Manager.

Confirm Changes

To confirm the changes rerun the @@SERVERNAME built-in configuration function to return the name of local server that is running SQL Server.

SELECT @@SERVERNAME AS 'Server Name'
GO

For more information on how renaming a computer / server with a new name can affect Replication, Log Shipping, Data Mirroring refer the following MSDN Article.