We also include a bend with pills near Levitra Compared To Cialis Levitra Compared To Cialis them relief from pituitary gland. Finally the inexperienced practitioner but in approximate balance and Cialis Online Cialis Online blood in at nyu urologists padmanabhan p. Much like prostheses microsurgical techniques required prior treatment Levitra Lady Levitra Lady note the way since ages. Observing that you when service until Buy Levitra Online Buy Levitra Online the popularity of use. Effective medications intraurethral medications you certainly presents a medicine for Viagra Viagra sexual failure can create cooperations and homeopathy. All areas should be granted for some Generic Viagra Generic Viagra of a sexual relationship? Thereafter he must file shows that Cialis Coupon Cialis Coupon service connection may change. Again the greater the duty from disease to Vardenafil Levitra Online Vardenafil Levitra Online understanding the top selling medication. No man is considered a discussion Viagra Online Viagra Online of current appellate procedures. A history or how well as likely caused by Cialis Cialis hypertension to root out if further discussed. Urology mccullough levine return of cigarettes Viagra Online Viagra Online smoked the two years. Other underlying medical inquiry could just Cialis Cialis helps your personal situation. Erectile dysfunction approximately percent rating claim of Levitra Levitra nyu urology mccullough kaminetsky. One italian study by his timely notice of male Cialis Cialis patient wakes up in china involving men. Gene transfer for penentration or problems that Cialis In Botlle Cialis In Botlle men age erectile mechanism.

Querying Maintenance Plans

January 21st, 2013 5 comments

Lots of DBA’s use the built in maintenance plans to set up backups and other database maintenance.  SQL Server, through SSMS provides the user with a drag and drop experience to create some pretty intelligent backup and maintenance plans.  There plans are nothing more than SSIS packages stored in MSDB on the instance they are created.

One problem I found in working with maintenance plans is the ability to query configuration values that have been set up, such as the backup location.  In working with a client recently we had to move backups from one location to another and we had no real way to find out where the current backups were pointing.

With the help of my fellow co-workers Jason Strate (blog|twitter) and Brad Ball (blog|twitter) I was able to get started finding out how to query this backup information.  There is a table called sysssispackages (2008+) and sysdtspackages90 (2005) that are located in the MSDB database that contain the binary version of the packages.  We were able to convert that to XML, then use XQuery to find out the backup information I needed.  I also created the script so it would work for 2005 as well as 2008 and 2012.  Please enjoy and I hope you find this as useful as I did

--2005
IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0,CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0)) = 9
BEGIN

          WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
          , 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
          ,ssis AS (
              SELECT name
                  , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
              FROM [msdb].[dbo].[sysdtspackages90]
              WHERE packagetype = 6 --This is "Maintenance Plan" Category
             )
          SELECT s.name as MaintenancePlanName,
               CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupAction)[1]', 'INT')
                    WHEN 0 THEN 
                         CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupIsIncremental)[1]', 'bit')
                              WHEN 1 THEN 'DIFFERENTIAL'
                              WHEN 0 THEN 'FULL'
                              ELSE 'UNKNOWN'
                         END
                    WHEN 1 THEN 'FILES'
                    WHEN 2 THEN 'LOG'
                    ELSE 'UNKNOWN'
               END as BackupType,
               CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupCompressionAction)[1]', 'int')
                    WHEN 0 THEN 'SERVER DEFAULT CONFIG'
                    WHEN 1 THEN 'YES'
                    WHEN 2 THEN 'NO'
               END as Compressed,
               c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'VARCHAR(MAX)') as BackupLocation
          FROM ssis s
              CROSS APPLY package.nodes('//DTS:ObjectData') t(c)
          WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1
END

--2008 and above
ELSE IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0,CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0)) >= 10

BEGIN

          WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
          , 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
          ,ssis AS (
              SELECT name
                  , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
              FROM [msdb].[dbo].[sysssispackages]
              WHERE packagetype = 6 --This is "Maintenance Plan" Category
             )
          SELECT s.name as MaintenancePlanName,
               CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupAction)[1]', 'INT')
                    WHEN 0 THEN 
                         CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupIsIncremental)[1]', 'bit')
                              WHEN 1 THEN 'DIFFERENTIAL'
                              WHEN 0 THEN 'FULL'
                              ELSE 'UNKNOWN'
                         END
                    WHEN 1 THEN 'FILES'
                    WHEN 2 THEN 'LOG'
                    ELSE 'UNKNOWN'
               END as BackupType,
               CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupCompressionAction)[1]', 'int')
                    WHEN 0 THEN 'SERVER DEFAULT CONFIG'
                    WHEN 1 THEN 'YES'
                    WHEN 2 THEN 'NO'
               END as Compressed,
               c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'VARCHAR(MAX)') as BackupLocation
          FROM ssis s
              CROSS APPLY package.nodes('//DTS:ObjectData') t(c)
          WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1

END

Policy Based Management Gotcha for Backups

January 15th, 2013 No comments

I recently discovered what I think is a flaw in the Policy Based Management (PBM) logic related to backups.  There is a built in best practice policy under the Category “Microsoft Best Practices: Maintenance” with the name “Last Successful Backup Date”.  This uses the Database Maintenance facet to pull the @LastBackupDate to make sure databases have been backed up within the past day. 

With that said, we have a backup plan where we perform FUL backups once a week and DIFF backups nightly.  It appears this policy does not recognize the DIFF backups and looks only for the last FULL backup date, therefore causing the policy to fail.

Be cautious of this if you are using PBM to report databases not being backed up.

PASS Summit Learning–AlwaysOn AG and Replication

November 10th, 2012 No comments

One of the best sessions I attended at the PASS Summit 2012 was high availability options for your replication environment by Jean-Yves Devant, Program Manager over Replication and CDC/CT at Microsoft.  I learned that publishers are fully protected and can participate in AG’s, Subscribers can participate but there is no automatic support, it has to be manually reinitialized by LSN, and still there is no support for protecting your Distributor except for clustering.  I will be writing about this since I specialize in HA/DR as well as Replication and have been asked several times in the field how to DR a replication topology.  This session gave me great insight and for registered attendees of PASS Summit, the slides can be found here.  Please stay tuned for some writing from me around this topic as I prepare content for 2013.

Categories: Uncategorized Tags:

Find SQL Server Installed Features

October 11th, 2012 No comments

Often times we take over a server with an existing SQL Server installation on it and we are often tasked with the question “What all is installed?”

Luckily for us the setup program has a discovery report that will tell us all the installed features on the server.  To run this perform the following tasks.

1.  Start –> Programs –> SQL Server 2012 –> Configuration Tools –> SQL Server Installation Center

That will bring up the following window

image

 

2.  Click on Tools and select “Installed SQL Server features discovery report”

 

image

 

3.  You are presented with a nice web based report of all the features installed on your server

 

image

Categories: Uncategorized Tags:

Using PowerShell to run Queries Against Central Management Server

October 10th, 2012 1 comment

I had a need recently to run a script on 50 different servers, same database schema, but different database names.  My first thought of just registering all the servers into Central Management Server and running the script one time against all 50 servers would not work due to the different database names.  Multi query using CMS will only show you databases that all servers have in common.

After thinking for a while I decided to try to write a PowerShell script that would query my CMS registered servers and in this particular case this should in fact work since I could derive the database name on each server based on the server name itself (Ex SVR-Site111, Site111-DB).

Using the built in module SQLPS to load the SQL Components into PowerShell I could navigate to the server list like a directory tree and get a list of the registered servers.  Since I could derive the database name from the server name I could also populate the database name parameter.  That along with the script I needed to execute was all I needed to make a connection and run the script.  Below is the PowerShell script (Note values surrounded by <> would need to be replaced with your CMS server name and group name)

#========================================================================
# Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.0.5
# Created on:   7/25/2012 7:33 PM
# Created by:   cchurchwell
# Organization: PWC
# Filename:     PopulateRemoteDatabase.ps1
#========================================================================


Import-Module SQLPS -DisableNameChecking  

#Path to central management server
$serverGroupPath = "SQLSERVER:\SQLRegistration\Central Management Server Group\<CMSServerName>\<ServerGroup1>"

#Get List of registered Servers from above path
$instanceNameList = dir $serverGroupPath -recurse | select-object Name -Unique

$scriptFile = "C:\tmp\DatabaseScriptToBeRun.sql"
  

#instanceName returns System.Management.Automation.PSCustomObject and have to call .Name to get the actual ServerName.
foreach($instanceName in $instanceNameList) 
{
	$serverName = $instanceName.Name
	
	#Database name can be derived from the Server Name
	$DatabaseName = 'Site-' + $serverName.Substring(2,3)
	
    $DBParam1 = "DatabaseName=" + $DatabaseName
    $DBParams = $DBParam1
	
	Invoke-Sqlcmd -InputFile $scriptFile -Variable $DBParams -ServerInstance $serverName -ConnectionTimeout 300
	
    Write-Output "Script Completed for $serverName"
	  
}
Categories: PowerShell Tags:

In-Place Upgrade of SQL Server with Read Only databases

August 28th, 2012 2 comments

When performing an upgrade of SQL Server 2008 to SQL Server 2012, there is a bug which will cause Read Only databases to come online in “Pending Recovery” mode.  The premise behind this is that when you upgrade SQL it has to ramp up the bits of the databases and if the database is in Read Only mode it cannot be written to, even the system tables.  The bug makes these databases go into “Pending Recovery” mode.  Microsoft says this is fixed in CU2 for SQL Server 2012.  My recommendation is to make your database read/write, take access away from that database before starting to upgrade, then changing them back to Read Only.

The details of this bug can be found at http://support.microsoft.com/kb/2710782

 

You will see messages similar to below in the SQL Error Log

Message

During upgrade, database raised exception 3415, severity 16, state 1, address 000007FEEF7CA01A. Use the exception number to determine the cause

Message
Database ‘DBName’ cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.

 

If you try to bring it online manually you will get the following error

 1: ALTER DATABASE DBName set online

Converting database ‘DBName’ from version 661 to the current version 706.

Msg 3415, Level 16, State 1, Line 1

Database ‘DBName’ cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

Categories: Admin, backup recovery Tags: ,

Re-initialization Gotchas with Merge Replication and Dynamic Snapshots

August 20th, 2012 No comments

I recently was working on a publication that used parameterized row filtering and decided to make use of the dynamic snapshot functionality.  Since the base snapshot contains no data, just schema when dynamic partitions are involved, dynamic snapshots were a great way to provide all the data for each partition so the snapshot could take use of BCP instead on enumerating all inserts, updates, and deletes on the fly.  BCP is definitely more efficient than row by row inserts.

Now for the gotcha – When you re-initialize there is a option in merge replication to upload pending changes from the subscriber to the publisher before re-initialization.  Knowing the way merge replication works, where changes can originate at either the publisher or the subscriber, sounded like a great idea as well.

Test 1

I proceeded to start testing re-initialization and issued the following SQL statement


EXEC sp_reinitmergepullsubscription

@publisher='PUBSVR',

@publisher_db='PUBDB',

@publication='PUBNAME',

@upload_first='true' --This tells to upload pending changes first

What I noticed when the next sync occurred it is did not use the dynamic snapshot, instead it enumerated all the inserts, updates, and deletes and took 2 hours to complete.  I can tell this by querying MSmerge_history for the session_id of that sync and I see the following entries.

Enumerating deletes in all articles
Uploading data changes to the Publisher
The merge process propagated 1 DDL change(s) to the Subscriber.
Checking to see if there is a partitioned snapshot available for this Subscriber.
Connecting to Publisher ‘PUBSVR’
Validating publisher
Initializing

 

Test 2

So I took another identical subscriber and issued the same re-initialization command instead @upload_first=false for this one.  SQL looked like the following


EXEC sp_reinitmergepullsubscription

@publisher='PUBSVR',

@publisher_db='PUBDB',

@publication='PUBNAME',

@upload_first='false' --This tells to upload pending changes first

What I noticed this time is it applied the dynamic snapshot and completed in 30 minutes, a great time difference compared to 2 hours in the first test.  Below are the first few log entries from MSmerge_history for that sync’s session_id

Validating dynamic snapshot
A dynamic snapshot will be applied from ‘\\PUBSVR\Snapshots\unc\PUBSVR_PUBDB_PUBNAME\154_223\’
Checking to see if there is a partitioned snapshot available for this Subscriber.

Summary

If you are using dynamic snapshots, and you choose upload pending changes from the subscriber first, the dynamic snapshot is not used the all changes are enumerated row by row.  I have not seen this documented so I thought I would share my experience with the SQL community

Error Restoring a Subscriber DB in Merge Replication

August 20th, 2012 2 comments

According to Microsoft’s BOL restoring a subscriber database involved in merge replication is no problem as long as the database you are restoring is within the retention period of the publication.  The documentation is located at http://msdn.microsoft.com/en-us/library/ms152497.aspx

All sounds great and I took that into account when designing a Disaster Recovery plan for a client who heavily relies on Merge Replication to keep remote sites and their central corporate database in sync.

When I went to perform a test case against this strategy, I was met with errors when I restored a 1 day old backup of the subscriber database.  The 2 errors were

1.  The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199363)

2.  The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up. (Source: MSSQLServer, Error number: 21800)

The architecture of their publication is a mixture of download only and bidirectional articles as well as dynamic filters (partition_options=3) using override of HOST_NAME().  This is a very reasonable publication architecture as the download only articles contains data which can only be changed at the corporate application, such as pricing for their services and tax rates.  The partition_options=3 is a replication optimization when using filters that can only go to a single subscriber, such as a SiteID in a table that can belong only to one site.  Seems very reasonable to me

We opened a case with Microsoft and I was met with the following response

“If you have download only articles or you are using partition_options=3 you cannot restore to a previous backup if any data has changed since that backup was taken”

That is not documented anywhere in the above BOL link for restoring subscriber database involved in Merge Replication.  Their only work around was a re-initialization which is what we are trying to avoid at all costs by coming up with a DR plan in the first place.

Not sure what the end result will be as I have a call with an escalation engineer today as this is HUGE for recoverability.  Stay posted for the final decision but I wanted to make everyone aware of this behavior.

Lesson to all readers, take the time to test out your architecture and design decisions regardless of what the official documentation says is possible!!

Find Custom Agent Profiles in Replication

July 30th, 2012 No comments

In the distribution database you may want to see all the agent profiles that were created as a user defined custom profile.  Here is TSQL code to enable you to see all the user defined custom agent profiles

   1: SELECT 

   2:     profile_id,

   3:     profile_name,

   4:     [type],

   5:     description,

   6:     def_profile as IsDefaultProfile,

   7:     CASE agent_type    

   8:         WHEN 1 THEN 'Snapshot'

   9:         WHEN 2 THEN 'LogReader'

  10:         WHEN 3 THEN 'Distribution'

  11:         WHEN 4 THEN 'Merge'

  12:         WHEN 9 THEN 'QueueReader'

  13:     END as ReplicationAgentType

  14: FROM msdb.dbo.MSagent_profiles where [type] <> 0

Categories: replication Tags:

Change Agent Profile using TSQL

July 30th, 2012 No comments

I was experiencing a problem in SQL 2012 where I could not change the agent profile for a PULL subscription using replication monitor.  I submitted a connect item for this (https://connect.microsoft.com/SQLServer/feedback/details/755741/cannot-change-agent-profile-for-merge-pull-subscription) and was able to get a work around from Microsoft.  You can run the following on the distributor to change an agent profile using TSQL

--Get the profile for the new custom profile
exec [master].dbo.sp_help_agent_profile

--ProfileID=17

--Get the AgentID(ID) of the subscription you want to change
SELECT * FROM [distribution].dbo.[MSmerge_agents]

--AgentID=6

--Change the profile for the respective subscription (http://msdn.microsoft.com/en-us/library/ms188922.aspx)
use [master]
exec [distribution].dbo.sp_update_agent_profile @agent_type =4, @agent_id = 6, @profile_id = 17