In this blog post I will drive you through configurations steps required (with GUI screens) to successfully implement SQL server’s new high availability option called “ALWAYSON” aka HADR .
If you haven’t read more about Always ON or Availability groups in SQL server code named “Denali” version, here are some articles that I would recommend you read before using this blog post to configure SQL server’s new and cool feature.
In this webcast we are going to install SQL Server 2008 Clustering on Windows server 2008. Ive already made my both the nodes clustered with Windows server 2008 and the details are below.
This is my first webcast for sql-articles.com. In this article we are going to discuss on Installing MSDTC as cluster aware service on Windows server 2008. We need this service as cluster aware because this is the pre-requisites when configuring SQL Server 2008.
I have planned to configure SQL Server 2008 clustering on Windows Server 2008 Clustering. To do this, first I need to configure Windows Server Clustering. Now a days DBA’s are not only working on RBMS platform, they should also have sufficient knowledge in their operating system on which their RDBMS is running. As a DBA we shouldn’t look into SQL Server cluster alone, we should also take part in windows clustering too. In this article Im going to configure failover clustering in Windows server 2008.
Last post from me on SQL Server replication was on How to validate subscribers in SQL Server Replication. This article shows how the same can be done using TSQL.
sp_publication_validation:
This system stored procedure does a validation for each and all articles in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.
sp_publication_validation [ @publication = ] ‘publication’
[ , [ @rowcount_only = ] type_of_check_requested ]
[ , [ @full_or_fast = ] full_or_fast ]
[ , [ @shutdown_agent = ] shutdown_agent ]
[ , [ @publisher = ] ‘publisher’ ]
@publication is the name of the publication.
@rowcount_only is whether to return only the row count for the table.
0 for perform a SQL Server 7.0 compatible check sum
1 for perform rowcount only
2 for perform a row count and binary check sum.
@full_or_fast is used to calculate the row count.
0 does full count using count(*)
1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.
2 does a count using rows column in sysindexes table if there is a difference then it does a full count.
@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.
@publisher specifies a non-sql server publisher.
You need to have sysadmin or db_owner role in the publication database to execute stored procedure.
sp_article_validation:
This system stored procedure does a validation for each article individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.
sp_article_validation [ @publication = ] ‘publication’
[ , [ @article = ] ‘article’ ]
[ , [ @rowcount_only = ] type_of_check_requested ]
[ , [ @full_or_fast = ] full_or_fast ]
[ , [ @shutdown_agent = ] shutdown_agent ]
[ , [ @subscription_level = ] subscription_level ]
[ , [ @reserved = ] reserved ]
[ , [ @publisher = ] ‘publisher’ ]
@publication is the name of the publication in which the article exists.
@article is the name of the article to validate.
@rowcount_only specifies if only the rowcount for the table is returned.
0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.
1 for perform a rowcount check only.
2 for perform a rowcount and binary checksum.
@full_or_fast is the method used to calculate the rowcount.
0 does full count using count(*)
1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.
2 does a count using rows column in sysindexes table if there is a difference then it does a full count.
@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.
@subscription_level specifies whether or not the validation is picked up by a set of subscribers. If 0, validation is applied to all Subscribers. If 1, validation is only applied to a subset of the Subscribers specified by calls to sp_marksubscriptionvalidation in the current open transaction.
@reserved identified for informational purposes only.
@publisher specifies a non-Microsoft SQL Server Publisher.
You need to have SELECT ALL permissions on the source table for the article being validated
sp_table_validation:
This system stored procedure does a validation for each table individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.
sp_table_validation [ @table = ] ‘table’
[ , [ @expected_rowcount = ] type_of_check_requested OUTPUT]
[ , [ @expected_checksum = ] expected_checksum OUTPUT]
[ , [ @rowcount_only = ] rowcount_only ]
[ , [ @owner = ] ‘owner’ ]
[ , [ @full_or_fast = ] full_or_fast ]
[ , [ @shutdown_agent = ] shutdown_agent ]
[ , [ @table_name = ] table_name ]
[ , [ @column_list = ] ‘column_list’ ]
@table is the name of the table to be validated.
@expected_rowcount specifies whether to return the expected number of rows in the table. If NULL, the actual rowcount is returned as an output parameter. If a value is provided, that value is checked against the actual rowcount to identify any differences.
@expected_checksum specifies whether to return the expected checksum for the table. If NULL, the actual checksum is returned as an output parameter. If a value is provided, that value is checked against the actual checksum to identify any differences.
@rowcount_only specifies what type of checksum or rowcount to perform.
0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.
1 for perform a rowcount check only.
2 for perform a rowcount and binary checksum.
@owner is the name of the owner of the table.
@full_or_fast is the method used to calculate the rowcount.
0 does full count using count(*)
1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.
2 does a count using rows column in sysindexes table if there is a difference then it does a full count.
@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.
@table_name is the table name of the view used for output messages.
@column_list is the list of columns that should be used in the checksum function.