R S R KARAN SQL-ORACLE-DBA HANDS ON : April 2014

Thursday 10 April 2014

Questions

Explain SQL Server Failover Clustering?
How do we check index Fragmentation?
TSQL syntax for Index rebuild and reorg. Difference between the two. How users are effected?
How do we rebuild large indexes?
Detect Locks in SQL Server.
Simulate a deadlock situation. Explain how SQL Server handles and comes out of the situation.
List out some commonly used DMVs.
Function to retrieve file level space usage.
List out and Explain some useful DBCC commands.
How do we store datafiles in a network drive.
Faster way of counting rows in a table.
What do we do when tempdb is full.
Whats the role of MSDTC.
Expalin two phase commit.
Steps before and after SQL Server upgrade. And steps to perform to enhance performance or prevent performance problems as a result of upgrade.
Isolation levels in SQL Server.
Restore a database from suspect mode without using Backup.
Frequestly used stored procedures and functions.
TLOG for tempdb is full and is not shrinking with shrink command. How do we take care of this issue?
What all activities happen during a checkpoint?
Events that can trigger a checkpoint.
How to change checkpoint interval? and whats the default interval?
Activities happening while a SQL Server instance starts.
What is 3GB switch?
Find memory usage of three instances respectively in a Server from Task manager.
How can a database go into suspect mode?
Different modes for a database in SQL Server.
Find tables that has no indexes.
Move indexes to a separate file group.
Will log-shipping work for bulk logged recovery model?
Different modes of Database mirroring?
Role of Cluster Quorum disk.
Two communication methods between the nodes of cluster.

Part 2

Maximum Number of instances possible in a SQL Server?
1. 50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster when using a shared cluster disk as the stored option for you cluster installation SQL Server supports 50 instances on a failover cluster if you choose SMB file shares as the storage option for your cluster installation.
Frequency at which the merge agent checks for changes at publisher and subscriber in a merge replication.
1. By default 60. Right click merge agent and look for pol interval inside agent profile from management studio.
Main differences in installation steps for 2005 and 2008 failover cluster.
Best recommended replication possible for sales person who do not have access to Office internal LAN. Keep in mind that they can connect only through internet without vpn.
1. Enable web synchronization. Refer http://msdn.microsoft.com/en-us/library/ms151810.aspx and http://msdn.microsoft.com/en-us/library/ms345214.aspx
What happens to a table if it doesn’t have a clustered index? Ans: It becomes a heap.

Answers and Clues

1.
a. Group of independent servers that work together to increase the availability of applications and services, protecting against software and hardware failiures by failing over resources from one server to another as required.
b. Failover cluster requires one or more clustered servers (called nodes), configuration of shared cluster disks, two networks for communication (atleast one public and one private.)

2. use dmv

sys.dm_db_index_physical_stats(
 'database_id',
 'object_id',
 'index_id',
 'partition_number',
 'mode')

. Refer MSDN for more details.

3.
Reorganize: This defragments the indexes by moving contents across pages making the data contiguous. Highly fragmented tabled should not be reorganized. Instead we should go with index rebuild. Index reorg is an online process and it can be stopped or cancelled anytime. The work done so far will not be rolled back as compared to index rebuild.
Rebuild: Existing Indexes are dropped and rebuilt from scratch. Enterprise Edition supports online index rebuild. If the index rebuild process is killed in the middle, entire work done on that index will be rolled back.
Refer http://technet.microsoft.com/en-us/library/ms188388.aspx for Syntax and Samples.
To automatically rebuild or reorg indexes, refer my post http://www.sherbaz.com/2011/12/automatically-rebuild-or-reorg-index-based-on-fragmentation/
Helpfull Links:
http://www.sqldbadiaries.com/2010/09/05/mr-dba-what-is-the-status-of-rebuild-index/
http://www.sql-server-performance.com/2011/index-maintenance-performance/

16. Isolation Levels in SQL Server
Command: “SET TRANSACTION <Isolation Level>”
Read Uncommitted
- Lowest
- Higher concurrency
- All concurrency problems: Dirty reads, lost updates,
Nonrepeatable reads(Inconsistent analysis) and phantom reads.
Read Committed
- Eliminates dirty-reads
- Other concurrency problems exists.
- Default Isolation level of SQL Server
Repeatable Read
- Eliminates all concurrency problems except Phantom reads.
- Does not release the shared lock once the record is read and keeps till the transaction
is over.
Serializable
- Highest Isolation level.
- Avoids all concurrency related problems.
- Its just like Repeatable read with one additional feature. Obtains key range locks based on the filters that have been used. It locks not only current records that stratify the filter, but new records that fall into same filter.
Snapshot Isolation Level
- Works on Row Versioning Technology.
- When a transaction is gonna modify something, SQL server will first store the consistence version of the record in tempdb so that when another transaction running on same isolation level requires same record, it can be taken from the version store(in Tempdb).
- Prevents all concurrency problems. And also it allows in multiple updates for same resource by different transactions cuncurrently.
Read commited snapshot
- New implementation of Read commited.
- Has to be applied at database level and not session or transaction level.
- Read committed Vs Read Commited snapshot : Pessimistic Vs Optimistic.
- Snapshot Vs Read commited Snapshot : Unlike snapshot, It always returns latest consistence version and no conflicts are detected.
- All concurrency problems will happen except dirty reads.
Above points were summarized from http://www.sql-server-performance.com/2007/isolation-levels-2005/3/

17. Recover a DB from suspect mode without backup.

Truncate Mirrored Database Log File

If you are running asynchronous database mirroring, then there could be a backlog of transaction log records that have not been sent from the principal to the mirror (called the database mirroring SEND queue).

The transaction log records cannot be freed until they have been successfully sent. With a high rate of transaction log record generation and limited bandwidth on the network (or other hardware issues), the backlog can grow quite large and cause the transaction log to grow.

On the mirrored database, you cannot backup the log file with TRUNCATE_ONLY. Here the steps to shrink the log file for a database participating in mirroring

Backup the log file to a location

BACKUP Log YourDatabaseName

TO DISK ='D:\BACKUP\DBNAME_20090201.TRN'

Check if there is enough free space on perform the shrink operation

SELECT name ,

size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed') ASint)/128.0 AS AvailableSpaceInMB

FROM sys.database_files;

DBCC SQLPERF(LOGSPACE);

If there is no sufficient free space then the shrink operation cannot reduce file size.

Check if all the transactions are written into the disk

DBCC LOGINFO('DatabaseName')

The status of the last transaction should be 0. If not, then backup the transaction log once again.

Shrink the log file

DBCC SHRINKFILE(logfilename , target_size)

If the transaction lof file does not shrink after performing the above steps then backup the log file again to make more of the virtual log files inactive.

Also check the column LOG_REUSE_WAIT_DESC in thesys.databases catalog view to check if the reuse of the transaction log space is waiting on anything.

Check this link to find the factors that can delay log truncation

http://msdn.microsoft.com/en-us/library/ms345414(SQL.90).aspx

Mirroring – Role of Witness Server and Quorum

When a witness server is set, a mirroring session (high safety mode with automatic failover mode) needs quorum to keep the database service. A quorum is the minimal relationship among all connected servers required for synchronous database mirroring session.

Now the next question that comes in mind is about the single point of failure for witness. It is not a single point of failure because if witness fails, principal and mirror will still continue to form a quorum.

Various types of quorum are possible.

Say for example

A = Principal
B = Mirror
C = Witness

Full Quorum – Both partners and witness are included - A∩B∩C

Quorum of partners – Only the two partners are included - A∩B

Quorum with witness and partner – Witness and one of the partners are included C∩(AUB)

Quorum loses sessions

If all the servers are disconnected then the session loses quorum

Now that we know all possible types of quorum, let’s see how each one affects the database and application.

If witness is disconnected when either partner goes down, the database is unavailable since quorum cannot be formed. If the session loses quorum, then the database will not be available until the quorum is re-established.

Log Shipping Requirements

Log shipping has the following requirements:
* SQL Server 2005 Standard, SQL Server 2005 Workgroup, SQL Server 2005 Enterprise Edition, or a later version, must be installed on all server instances involved in log shipping.
* The servers involved in log shipping should have the same case-sensitivity settings.
* The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.

Permissions
You must be a sysadmin on each server instance to enable log shipping. The backup and restore directories in your log shipping configuration must follow these requirements.
* For the backup job, read/write permissions to the backup directory are required on the following:
o The SQL Server service account on the primary server instance.
o The proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.
* For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
* For the restore job, read/write permission to the copy directory are required by the following:
o The SQL Server service account on the secondary server instance.
o The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.

What is Log Shipping in Sql Server ?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all.

The key feature of log shipping is that is will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server.

This in effect keeps the two SQL Servers in "synch". Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, its not really that easy, but it comes close if you put enough effort into your log shipping setup.

Benefits of Log Shipping

While I have already talked about some of the benefits of log shipping, let's take a more comprehensive look:

• Log shipping doesn't require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement. In addition, you can use the standby server for other tasks, helping to justify the cost of the standby server.

Just keep in mind that if you do need to fail over, that this server will have to handle not one, but two loads. I like to make my standby server a development server. This way, I keep my developers off the production server, but don't put too much work load on the standby server.

• Once log shipping has been implemented, it is relatively easy to maintain.
• Assuming you have implemented log shipping correctly, it is very reliable.
• The manual failover process is generally very short, typically 15 minutes or less.
• Depending on how you have designed your log shipping process, very little, if any, data is lost should you have to failover. The amount of data loss, if any, is also dependent on why your production server failed.
• Implementing log shipping is not technically difficult. Almost any DBA with several months or more of SQL Server 7 experience can successfully implement it.

Problems with Log Shipping

Let's face it, log shipping is a compromise. It is not the ideal solution, but it is often a practical solution given real-world budget constraints.

Some of the problems with log shipping include:

• Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs.
• The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.
• Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.
• The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.
• When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user's applications to the new standby server. In some cases, neither of these options is practical.

Log Shipping Overview

Before we get into the details of how to implement log shipping, let's take a look at the big picture. Essentially, here's what you need to do in order to implement log shipping:

• Ensure you have the necessary hardware and software properly prepared to implement log shipping.
• Synchronize the SQL Server login IDs between the production and standby servers.
• Create two backup devices. One will be used for your database backups and the other will be used for your transaction log backups.
• On the production server, create a linked server to your standby server.
• On the standby servers, create two stored procedures. One stored procedure will be used to restore the database. The other stored procedure will be used to restore transaction logs.
• On the production server, create two SQL Server jobs that will be used to perform the database and transaction log backups. Each job will include multiple steps with scripts that will perform the backups, copy the files from the production server to the standby server, and fire the remote stored procedures used to restore the database and log files.

• Start and test the log shipping process.
• Devise and test the failover process.
• Monitor the log shipping process.

Log Shipping FAQ

Question : IS it possible to log ship database between SQL 2000 & SQL 2005?
Answer: No, thats impossible, In SQL 2005 transaction log architecture is changed compared to SQL 2000 and hence you won’t be able to restore tlog backups from SQL 2000 to SQL 2005 or vice versa.

—————————————————————————–

Question: How to failover in SQL 2005 Log Shipping?
Answer: I can better ask to check out the link Failover in SQL 2005 Log Shipping, Deepak written this article clearly.

—————————————————————————–

Question:I’m getting the below error message in restoration job on secondary server, WHY?
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
Answer:Was your sql server or agent restarted Y’day in either source or destination ? because the error states there is a mismatch in LSN. A particular tran log was not applied in the destination server hence the subsequent tran logs cannot be applied as a result !
You can check log shipping monitor \ log shipping tables to check the which transaction log is last applied to secondary db, if the next consecutive transaction logs are available in the secondary server share folder you manually RESTORE the logs with NORECOVERY option, Once you restored all the logs automatically from the next cycle the job will work fine.
Incase if you are not able to find the next transaction log in secondary server shared folder, you need to reconfigure log shipping. Try the below tasks to re-establish log shipping again.

Disable all the log shipping jobs in source and destination servers
Take a full backup in source and restore it in secondary server using the With Standby option
Enable all the jobs you disabled previously in step1

—————————————————————————–

Question: Is it possible load balance in log shipping?
Answer:Yes ofcourse its possible in log shipping, while configuring log shipping you have the option to choose standby or no recovery mode, there you select STANDBY option to make the secondary database readonly. For SQL 2005 log shipping configuration check out the link 10 Steps to configure Log Shipping

—————————————————————————–

Question: Can I take full backup of the log shipped database in primary server??
Answer: In SQL Server 2000 you won’t be able to take full backup of log shipped database, because this will break the LSN chain and it directly affects the log shipping.
In SQL Server 2005, yes its possible. You can take full backup of log shipped database and this won’t affect the log shipping.

—————————————————————————–

Question : Can I shrink log shipped database log file??
Answer: Yes ofcourse you can shrink the log file, but you shouldn’t use WITH TRUNCATE option. If you use this option obviously log shipping will be disturbed.

—————————————————————————–

Question : Can I take full backup of the log shipped database in secondary server??
Answer: No chance , you won’t be able to execute BACKUP command against a log shipped database in secondary server.

—————————————————————————–

Question: I’ve configured Log shipping successfully on standby mode, but in the restoration job I’m getting the below error. What I do to avoid this in future??
Message
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\TEST_20060731131501.trn’ to secondary database ‘TEST’.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
Answer: To restore transaction logs to the secondary db, SQL Server needs exclussive access on the database. When you configure it in standby mode, users will be able to access the database and runs query against the secondary db. Hence If the scheduled restore jobs runs at that time, the db will have a lock and it won’t allow SQL Server to restore the tlogs. To avoid this you need to check “Disconnect users in the database when restoring backups” options in log shipping configuration wizard. Check the link 10 Steps to configure Log Shipping.

—————————————————————————–

Question : Can you tell me the pre-requisites for configuring log shipping??
Answer : Check out the link Pre-requisites for Log Shipping.

—————————————————————————–

Question : Suddenly I’m getting the error below, How can I rectify this???
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4323: [Microsoft][ODBC SQL Server Driver][SQL Server]The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally
Answer : We had the same issue some time ago, this was related to a new file being created in a filegroup on the source. Don’t know if this applies to your case, but restoring a backup of this new file on the secondary server solved the problem.

—————————————————————————–

Question : Is it possible to log ship database from SQL server 2005 to SQL server 2008 and vice versa?

Answer : Yes you can log ship database from SQL server 2005 to SQL Server 2008 this will work. However log shipping from SQL Server 2008 to SQL Server 2005 is not possible because you wont be able to restore SQL server 2008 backup to SQL Server 2005 (downgrading version)

SQL Server 2008 Log Shipping

Here are some notes on “SQL Server 2008 Log Shipping.

Log shipping

Automated process to ship transaction log backups to another server
Three jobs to perform the required actions: backup, copy and recover
Involves three servers/instances: primary, secondary and (optional) monitoring server
Requires full or bulk logged mode
Can have multiple secondary copies
Information about log shipping kept in MSDB
See http://msdn.microsoft.com/en-us/library/ms187103.aspx

Log Shipping Options

Interval – Default is 15 minutes. Consider the impact before using a smaller number
Delayed log restore – option that allows you to have an older copy, for an “oops” situation
If secondary is far behind. Consider backup/copy to media/restore, consider partitioning the data.
Careful – Backup typically goes to a file server share. Agent account needs access to share.
Careful – Weekend maintenance jobs (like indexing) can make you run out of disk space
No option to synchronize logins – Consider sp_resolve_logins to import.
Monitoring – MSDB tables, agent history
See http://msdn.microsoft.com/en-us/library/ms190224.aspx

Log Shipping Failover

No automated failover. If automatic failover is required, consider Mirroring instead.
To failover:
- Stop the jobs
- Take a tail of the log backup (BACKUP LOG … WITH NO_RECOVERY)
- Apply tail of the log backup to secondary with a RESTORE LOG ... WITH RECOVERY
- If required, configure log shipping in reverse direction
See http://msdn.microsoft.com/en-us/library/ms191233.aspx
Allows for rolling patches an upgrades
- Upgrade secondary
- Failover
- Upgrade the original primary (now secondary)
See http://msdn.microsoft.com/en-us/library/ms178117.aspx
Consider building a document with detailed instructions.

Comparing to Mirroring

Mirroring provides synchronous mode, with no data loss
Mirroring provides automated failover
Log shipping allows for multiple copies
Log shipping allows for delay in applying logs
See http://msdn.microsoft.com/en-us/library/ms187016.aspx

SQL Server 2008 Implementing Log Shipping

In this post

- Introduction
- Process of Log Shipping
- Log Shipping topology
- Log Shipping Prerequisites
- Step by step example
- How to Failing Over
- Conclusion
- Resources

Introduction

SQL Server provide a way to backups automation that let to others servers to maintain these backups in the scale out of the main server. it's Log Shipping ! . Log Shipping is a way to make high availability to solutions by making backups from the main server and restore it to one or more other servers . administrator can recover the main SQL server database (for any fail) from other servers by restore the database from one of other server to main server.

Process of Log Shipping

Log Shipping focus on transaction log file of the database . Log Shipping make a backup for that type of file only . therefore the recovery mode for the main database must be in Full or Bullk-logged option.

the following is the main processes of log shipping work .
1- Backup transaction log file of the main database to a folder founded in the main server.
2- Coping the transaction log backup from main sql server to one or more other sql server
3- Restore the transaction log backup that is exists in others sql servers.

Log Shipping topology

Log shipping consists of the following servers.

- Primary Database Server : that contain the primary database the need to maintain high availability.
- Secondary Database Server : in this server the second database that save the transaction log data from the primary database server
- Monitor Server : track the process of log shipping process in other both servers (primary, secondary).

Log Shipping Prerequisites

1- Primary database must to be a Full or Bulk-Logged recovery model.
2- Configurations need high permissions on SQL server and SQL Server Agent Service also.
3- at least two Database Servers or two SQL Server instances.
4- Folder for copying the transaction logs must be Shared and has a suitable permissions access.

Step by step example

1- First, SQL Server Agent Service for both server (Primary, Secondary) must enabled (Started Service)

2-Right Click on primary database for primary server. and then select Properties. from the left pane select Transaction Log Shipping .

3- click on checkbox Enable this as a primary database in a log shipping configuration to enable log shipping in this database. and then click on Backup Settings button.

4- in Backup Settings window, set network path for backup folder . this path let sql server in primary server to save backup result on it. and also you can set path in local machine (in the second textbox) if backup result will be set on the primary server not in nework path.

it's mandatory to put network path to can leave this step. Consider also that network path must be a shared folder with suitable permission to let log shipping done task job well.

after that click on Schedule Button to plan the time of backup job implementation.

5- Returning again to Database Properties window to setting up the first secondary SQL Server. in the intermediate part of window ckick on Add Button .

Press Connect Button, and select your second SQL Server.

after selecting the second server, select the first option in the Initialize Secondary Database tab. this option mean that log shipping will generate a full backup of the primary database and restore it into the secondary database . log shipping will create the secondary database if it doesn't exist.

Now, goto second tab CopyFiles to set path folder for coping backup files after backup job finish. this coping process need to automate . for automate that press the Schedule button to set appropriate time job .

in the last tab Restore transaction log select the second option Standby mode . the Standby mode mean that second database in the second server will be read only database. it's suitable situation if need working with second database by select query only, it's let customers to connect to this database for reading but it's reading only.

there is second option No recovery mode that is mean the second database will not be available for any one , it's just working for log shipping processes.

select on Schedule button to set appropriate time job for recovering.

after finish three tab options press ok to return Database Properties window for primary database. the secondary database will appear like this.

press Add button to add another SQLServer that will act as others secondary database servers.

6- in the last part of Database Properties window, optionally click on use a monitor server instance checkbox option to determine a sql server that act Monitor Server.

7- in Database Properties window click ok, to take processes of log shipping work.

8- in the stage the log shipping work well . to check the effects of log shipping check the added jobs in the both SQL agent service for two servers.

Note that because of Standby mode that selected for second database server, the second database server will act a read only database as show below.

checkup also the result file in both paths that sets in the log shipping configuration .

How to Failing Over

1- After failing primary server, it's may be to find backup files in the backup folder not transfered to destination folder. copy all that files that is not transfered to destination folder(is the secondary server).

2- Restore all tranaction log backup files in the destination folder sequencelly (identify that by date of files) to secondary sql server.

3- If it's possible to access primary database server take a (Tail log) backup (BACKUP LOG-WITH NO_RECOVERY) this leaves the database in the inconsistent state (restoring state) . and then restore (tail log) backup to secondary SQL server with a (RESTORE LOG-WITH RECOVERY)

after these steps the primary database in unavailable and second database is online now, it's time for administrator now to redirect users to second server.

Conclusion

Log shipping is a way from ways that can achive a level of high availability, design a suitable topology with right configuration with prerequisites to have a higher power benefits of log shipping.

Resources

Failing Over to a Log Shipping Secondary
http://msdn.microsoft.com/en-us/library/ms191233.aspx

SQL Server 2008 Log Shipping
http://blogs.technet.com/b/josebda/archive/2009/04/02/sql-server-2008-log-shipping.aspx

SQL Server Log Shipping

Log shipping? Why are we talking about lumber? Actually, log shipping is a powerful yet simple way to ensure failover capability in the Enterprise edition of SQL Server. Baya Pavliashvili gets you started by discussing the advantages and drawbacks of log shipping, and then shows you how to set up log shipping and how to fail your database over to the standby server.

Log shipping is one of the failover solutions offered by SQL Server 2000. In this context, failover means substituting primary server with a backup (sometimes also referred to as standby) server if the primary hardware becomes unusable. Failover solutions can also be used to provide close to 100% uptime—during primary database or server maintenance and during software upgrades you could use the standby server to continue serving your customers.

Failover solutions can be automatic or manual. With automatic failover, the backup server detects when the primary server is not available and takes over without any intervention from the database administrator. An example of an automatic failover solution is clustering. With manual failover, the database administrator (DBA) has to perform some steps to bring the standby server online. In SQL Server 2000, log shipping is a form of manual failover.

NOTE

Only Enterprise and Developer editions of SQL Server support log shipping.

How It Works

Log shipping implementation is straightforward:

Full backup of the database is taken on the primary server and copied to the standby server.
Standby server maintains a copy of the database.
The database is not operational; it can stay in either read-only mode or no-recovery mode.
Transaction log for the "log-shipped" database is backed up on the primary server periodically. Note that only databases that are in FULL recovery mode can be log-shipped.

Transaction log backups are copied to the standby server.
Transaction log backups are applied to the database on the standby server in the order that they were taken on the primary server.
Either primary server, standby server, or a separate server can be used to monitor log shipping. If you use a separate server for monitoring, it does NOT have to have Enterprise Edition of SQL Server; any edition (other than MSDE) will do.

If the primary server becomes unavailable due to disk failure or some other reason, DBA can take the following steps to fail the database over to the standby server:

Perform one last backup of the transaction log on the primary server (if possible).
Copy all transaction log backups to the standby server and apply them in the same order they were taken on the primary server.

Transfer any logins that exist on the primary server to the standby server. Only the logins that must have access to the log-shipped database must be transferred.

During the initial configuration of log shipping, you can allow the standby database to assume the primary role. That means you can ship transaction logs from the standby server to the primary server after you have failed the primary database over. So if primary server (server A) fails over to standby server (server B), servers can switch roles so that you can fail server B back to server A if needed.

To implement the Log shipping or mirroring right click on any database --> Go to Properties and under Select a page click on Transaction Log Shipping and enable it by clicking on "Enable this as a primary database in a log shipping configuration" check box.

Now Under the Transaction log backup click "Backup Settings"â€¦.. a pop-up window appeared "Transaction Log Backup Settings" as Under:
If we are creating backups on a network share enter the network path or for the local machine we can specify the local folder path and click "OK".
When we click on OK button the above window will close, then from the "Database Properties window" click on Addâ€¦ button to configure the Secondary Server instance and database.

Note: we can add multiple servers if we want to setup one to many server log-shipping.
A window "Secondary Database settings" will appear now; in this step we can specify how to create the data on the secondary server. There are three options: create a backup and restore it, use an existing backup and restore or do nothing because we have manually restored the database and have put it into the correct state to receive additional backups.
Click on Connect.
Enter the Secondary Server Name and its Password to connect to it, so that our secondary database setting shows both the "Secondary Server Instance" and "Secondary Database".
In Copy Files tab, specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.
In Restore Transaction log tab, specify the database restoring state information and restore schedule. This will create the restore job on the secondary server. Click "ok", and we will see that in our "Database Properties" window a secondary Server has Added:
Now we will configure Log Shipping Monitoring which will notify us in case of any failure, click on "Settings" a "Log shipping Monitor Settings" window will be opened:

Note: Log shipping monitoring configuration is optional.
Click on connect and enter the Username and password of your Primary Server, click "OK".
Again Click "OK" on Database Properties Window, then you will get the following success result:

How to bring a log shipping standby database online?

On the failure of Primary database in a log shipping setup, we need to bring the secondary database online for use, which can be standby or read-only. To make the standby available, break the log shipping and bring secondary database online WITH RECOVERY.To bring the standby database to the current state of the source database, if possible try to perform a final log backup on the source database, then run the log shipping backup job on the source server and copy the backup file to the log shipping share. If there are files remaining in the log shipping share, then use SQL Backup to restore the logs on the standby database by specifying a wildcard (*.sqb) filename, using the option WITH RECOVERY.

Note: If the source database is damaged, a final log backup may fail. In this case, try appending the NO_TRUNCATE option to the BACKUP LOG command.

If there are no remaining log files to restore, use the following SQL query to bring the database on-line:

"RESTORE DATABASE [PatientInfo] WITH RECOVERY".

SQL Server Log Shipping

NOTE

Only Enterprise and Developer editions of SQL Server support log shipping.

How It Works

Log shipping implementation is straightforward:

Full backup of the database is taken on the primary server and copied to the standby server.
Standby server maintains a copy of the database.
The database is not operational; it can stay in either read-only mode or no-recovery mode.
Transaction log for the "log-shipped" database is backed up on the primary server periodically. Note that only databases that are in FULL recovery mode can be log-shipped.

Transaction log backups are copied to the standby server.
Transaction log backups are applied to the database on the standby server in the order that they were taken on the primary server.
Either primary server, standby server, or a separate server can be used to monitor log shipping. If you use a separate server for monitoring, it does NOT have to have Enterprise Edition of SQL Server; any edition (other than MSDE) will do.

If the primary server becomes unavailable due to disk failure or some other reason, DBA can take the following steps to fail the database over to the standby server:

Perform one last backup of the transaction log on the primary server (if possible).
Copy all transaction log backups to the standby server and apply them in the same order they were taken on the primary server.

Transfer any logins that exist on the primary server to the standby server. Only the logins that must have access to the log-shipped database must be transferred.

R S R KARAN SQL-ORACLE-DBA HANDS ON

CLOCK

Thursday 10 April 2014

Questions

Answers and Clues

Truncate Mirrored Database Log File

If you are running asynchronous database mirroring, then there could be a backlog of transaction log records that have not been sent from the principal to the mirror (called the database mirroring SEND queue).

The transaction log records cannot be freed until they have been successfully sent. With a high rate of transaction log record generation and limited bandwidth on the network (or other hardware issues), the backlog can grow quite large and cause the transaction log to grow.

Mirroring – Role of Witness Server and Quorum

Log Shipping Requirements

What is Log Shipping in Sql Server ?

Log Shipping FAQ

SQL Server 2008 Log Shipping

SQL Server 2008 Implementing Log Shipping

SQL Server Log Shipping

How It Works

SQL Server Log Shipping

How It Works

Popular Posts

Popular Posts