Prerequisites for Configuring Log Shipping

1)      Both SQL & OS Editions and Versions should be at same level on all participating servers

2)      Express Edition doesn’t supports SQL server Logshipping

3)      The user who configures the Logshipping must be member of sysadmin server role

4)      SQL Server and SQL Server agent services must be configured under windows authentication account with sysadmin permissions on all participating server

5)      Recommended to have unique logon account for both SQL Server and SQL Server agent services

6)      The Logon account must have at least local administrator privileges to carry out the administrative tasks

7)      SQL Server port (1433) must be opened at network level for communication between the both servers

8)      Sharing ports must be enabled on both production & DR servers for log shipping

9)      Create shared network share on both Primary and secondary servers

10)   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:

·         The SQL Server service account on the primary server instance. 

·         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 service 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:

·         The SQL Server service account on the secondary server instance. 

·         The SQL Server Agent account on the secondary server instance. 

11)   SQL service log on accounts must be same on both Environments.

12)   Database being log shipped must be in Full recovery model or Bulk logged recovery model. The simple recovery model is not supported

13)   Should not run any maintenance plan or backup job against log shipped databases since that would break Logshipping chain

14)   Logshipping configuration can be done with in trusted domain computers or work group computers

15)   Recommended to have the same Drive Space capacity in fact more space of secondary server considering the data growth in future

16)   Ensure you do not hardcode your server name in your application connection connecting the databases. Create in a such way that you can switch easily to the database servers and take advantage of log shipping

Appreciate your suggestions. Please comment your feedback and reach me out via email

Performance Dashboard Error: Could not find stored procedure 'msdb.MS_PerfDashboard.usp_CheckDependencies'.

In this post, I’m going to talk an issue that when you try to launch the main Dashboard Report in SQL Server 2008 R2. You may receive the below error message that includes information about the cause of the error, which can help you to resolve the issue.

Performance Dashboard Error: Could not find stored procedure 'msdb.MS_PerfDashboard.usp_CheckDependencies'.

Cause

Microsoft does not provide the Performance Dashboard for SQL Server 2008R2 for download, but there is a work-around by installing the Performance Dashboard for SQL Server 2005 and modifying the provided Performance Dashboard T-SQL setup script from Microsoft.

For 2005/2008/2008R2 download here 
For 2012 download here

Resolution

First install the setup file. By default it is installed in: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard. Once you install this, You will find two files at the installed location. Traverse to C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard 

After installing the Dashboard report setup, now setup the dashboard report using SSMS and have to run setup.sql script.

Connect to SQL Server using SSMS and open the file, setup.sql, located in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard.

Find the code in setup.sql. Go to the statement where you see 'select @ts_now = cpu_ticks' as shown below

Change the statement to

select @ts_now = ms_ticks from sys.dm_os_sys_info

In below screenshot, I have modified select @ts_now = ms_ticks from sys.dm_os_sys_info

Now, execute the “setup.sql” script. 

Now you can right-Click the SQL Server, navigate to the server level reports section, select Custom Reports.In below screenshot, I select "Custom Reports". 

Read My Complete Article "Here" 

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Email: gana20m@gmail.com

Linkedin

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Always on database in suspect mode and suspended data movement.

Always on database in suspect mode and suspended data movement.

What is suspect mode of database?

SQL database is marked as suspect when primary group file is corrupt and when SQL Server tries to start, database cannot be recovered during that time. 

There can be a lot of reasons for a database to go in suspect mode, some of them are:

• Hardware failure

• Improper shutdown of the database server

• Corruption of the database files

• Unavailable device files

• Unavailable database files

• Database resource used by operating system

• SQL Server incorrectly asserts free data page space when a row is inserted

Issue:

My drive where the 'tempdb' is located is completely filled up and there is no space left on drive causing the 'tempdb' in suspect mode.

This also suspended the data movement between databases in Always on group from primary to secondary.

Step 1:

Identified tempdb filled up the drive space and database is in suspect state

    Step 2: 

    Restarted SQL services to clear space on tempdb db and Now the database is online but the Always-on dashboard report shows critical warning and database is not synchronizing. 

    Secondary replica database data movement is suspended (fail-over dash board report shows data movement got suspended)

    Step 3:

    Expand Always-on High Availability pane and select availability databases.

    Right click on the database and select resume data movement.

     Step 4:

     Issue resolved. Now the database is completely synchronized.

    Appreciate your suggestions. Please comment your feedback and reach me out via email

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Linkedin

Troubleshooting With Damaged Database File in SQL Server

The SQL Server performs its input/output operations by using Windows API’s like ReadFile, ReadFileScatter, WriteFile and WriteFileGather. After these input/output operations, SQL server views those errors that are related to these APIs. If any of these APIs call fail with Operating System error, then SQL Server displays the Error 823.

There is a logical consistency problem encountered with input/output operations while transferring the data. Error 824 displays this logical consistency.

What is Error 824?

In SQL server, Error 824 indicates that there is a major problem in the storage system or in the hardware part. The Error is encountered when the database file is found damaged. This Error also indicates that the SQL server finds something wrong on the page while Windows didn’t find any error in the page.

Causes of Error 824

The error 824 shows the following information:

• The suspected file of the database on which the input/output operation is executed.
• To which database this file belongs.
• Which page number was involved while performing input/output operation?
• Was the performed operation a write or read operation?

As this Error indicates to issues with the storage system or the hardware part of the system, so for the solution of this problem, it is very important to look for issues in the hardware.

Inspection of Hardware Failure

First, check whether it is a hardware problem or not, if it is a hardware failure problem, then check the hardware and fix the problem. Otherwise, if it is a data corruption issue, then try to exchange various hardware components to segregate the problem.

Finally, it can be useful to change to a new hardware system. This change may include, re-formatting of the disk drives and installation of the operating system again.

Note:

If the problem is not related to the hardware and you have a clean backup, then restore your database from backup.

Note:

Use PAGE_VERIFY CHECKSUM option while changing the databases.

Use Following Steps To Solve The Problem Manually

• Check the suspected pages in msdb.
• Calibrate the database consistency.
• Quickly turn On the PAGE_VERIFY CHECKSUM option if it is off.
• Check the Windows Event logs for the error that is reported by the Operating System.
• Utilize the SQLIOSim.
• Work with your device manufacturer.
• Evaluate the filter drivers if available.

In case the following manual method won’t work, then you should go for a third party SQL recovery tool.

Microsoft will release public preview of SQL Server 2016 this summer

Big News from Microsoft.

For complete information refer the below links.

http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/


http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

How to add new disks to existing SQL Server Failover cluster.

Hello everybody, In this post I’m giving the procedure to add the new clustered disks to existing SQL Server failover cluster.

My Environment: Windows 2008 Enterprise Edition, MS SQL Server 2008 R2 Enterprise Edition

Disk Letters : 'O', 'P', 'R'

Downtime: Zero - '0' ( No service disruption or Zero Downtime)

Lets begin....

My storage team has already given the above 3 LUN's through iSCSI initiator or RDM.

Request from client was to add these disks to the existing windows cluster in order to use them as clustered disks for SQL Server cluster.

Step 1:

To open disk management. Go to RUN--> diskmgmt.msc

You should able to see the disks in offline state on all the nodes available in cluster.

Step 2:

Bring the disks to online in any one of the nodes in the cluster (you can choose only one node). On other nodes it should be in offline mode as we are going to use these disks for failover cluster.

Step 4:

Now the disks are online and unallocated. We have to create a new volume assigning a new drive letter. 

Format the drive with NTFS file system and click finish.

This is how it looks in disk management console.

Step 5: 

Now we are ready to add this disks to windows fail over cluster.

a) click on disk option available on storage pane in fail over cluster manager console
b) It will prompt for all the available disks can be added to cluster
c) select the disks and click OK

Now the disks are added to available storage pool in cluster manager console.

You can also see this disks available from my computer.

Hold on... It's not done yet. you are done with only adding disks in windows cluster. Now we have to add these disk and will be going to use for SQL cluster.

Step 6:

Now Right click on SQL Server and select add storage. It will prompt all the new available disks from storage. Select the disks and click ok.

Step 7:

Now Right click on SQL Server and select properties of SQL Server. Now we have to add the dependencies for new disks as shown below.

If the disks are not part of dependency list, click insert and add disks and click OK.

You can find the dependency of SQL Server by verifying dependency report.

You can also verify the disks by creating the sample databases on newly added disk as shown below :)

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

Reference: (http://mssqllover.blogspot.in)

Linkedin

Error: Value cannot be null, Parameter name: viewInfo (OR) Error: Microsoft .NET framework: Unhandled exception has occurred in a component in your application. If you click continue the application will ignore this error and attempt to continue

Error Message:

=================================================

Error: Value cannot be null, Parameter name: viewInfo 

(OR) 

Error: Microsoft .NET framework: Unhandled exception has occurred in a component in your application. If you click continue the application will ignore this error and attempt to continue

How to resolve this issue?

Step 1:

The error generally occurs if any missing directory/file in %TEMP% and after that SSMS was not working any longer. The missing file is actually not needed, but the missing directory causes the "Value cannot be null" error.

  

  

Step 2: 

If the below temp and tmp variables are not placed in default location, we have to set them into their default location as shown below and restart the MS SQL services once if required.

C:\Users\Gani>set t

TEMP=C:\Users\Gani\AppData\Local\Temp\2

TMP=C:\Users\GaniAppData\Local\Temp\2

Step 3:

Right click on my computer---> Advanced system Settings environment variables ---> edit user variables and you can see the new path set 

Step 4:

Close the SSMS if opened and connect SSMS again. Now the issue gets resolved and able access the GUI interface without any issues

Appreciate your suggestions. Please comment your feedback and reach me out via email

Regards,

Ganapathi varma

Reference: (http://mssqllover.blogspot.in)

Linkedin