Debug T-SQL or Stored Procedures Remotely in SQL Server 2005 and Visual Studio 2005

Transaction-SQL Debuger feature have been moved to VS.NET 2005 in SQL Server 2005 ,and return back SQL Server 2008 ,so inorder to enable debuging the only way to go through VS.NET 2005 SKU .
where is the setps to do :
Suppose we want to perform remote debugging to a different user's computer with the different user logged on to the computer, We have to consider two primary issues:
1- Permissions
By default, only administrators and the user running the remote debugger have access to connect to the remote debugger.
2-Connection
Visual Studio 2005 needs to know to which instance of the remote debugger you want to connect. If the same user is running the remote debugger and Visual Studio 2005, Visual Studio 2005 will be able to find the remote debugger without you telling it which user is running the remote debugger.
However, if a different user is running the remote debugger, Visual Studio 2005 must be told to connect to domain_name\user_name@remote_computer instead of just being told to connect to remote_computer.

How to set up the environment for remote debugging ??
Add the appropriate user to the Debugger Users group on the remote computer To do this, follow these steps:
1-On the remote computer, right-click My Computer, and then click Manage.
2-Under Computer Management, expand System Tools, expand Local Users and Groups, and then expand Groups.
3-Double-click Debugger Users-To add the appropriate user to the Debugger Users group, click Add in the Debugger Users Properties dialog box.
4-In the Select Users, Computers, or Groups dialog box, enter the appropriate user in the Enter the object names to select box, and then click OK two times. (For example, enter Domain1\User1)
5-Exit the Computer Management tool.
Note In Visual Studio .NET 2003 and Visual Studio .NET 2002, the user who is trying to perform remote debugging has to be a member of the Administrators group. However, in Visual Studio 2005, we don't need to add this user to the Administrators group.

Make sure that the Visual Studio 2005 remote debugging components are installed on the remote server.
To install remote debugging components ??
1-The Remote Debugger is available on the last disc of your Visual Studio installation set. Insert this disc in the remote computer.
For example, if you have four discs in your installation set, insert disc 4 in the remote computer. If you have a DVD instead of a CD, insert the DVD.
2-In Windows Explorer, open the CD/DVD. Locate the Remote Debugger folder (on the CD) or vs/Remote Debugger (on the DVD).
3-In the Remote Debugger folder, open the subfolder that matches your operating system (x86, x64, or IA64).
4-Start the copy of rdbgsetup.exe located in that subfolder, and follow the instructions to complete setup.
Start Remote Debugging:
When debugging T-SQL, the Remote Debugging Monitor will launch automatically during debugging.
1-Launch Visual Studio on the debugger host.
2-Use Visual Studio to attach to a program you want to debug on the remote machine or launch a program you want to debug on the remote machine as following :
A-From the Debug menu, choose Attach to Process. (If no project is open, choose Attach to Process from the Tools menu.)
B-In the Attach to Process dialog box, find the program you want to attach to from the Available Processes list:
1-If the program you want to debug is running on another machine, you must first select the remote machine in our case .
2-If the process is running under a different user account, check the Show processes from all users box.

C-In the Attach to box, make sure that either the type of code you will debug is listed or that Automatic: Managed code appears. If it doesn't:
Click Select.
In the Select Code Type dialog box, click Debug these code types and select the types to debug.
D-Click OK, then Click the Attach button.

ASP.net 2.0 Active Directory Membership provider

with Active Directory Membership provider you can Access anyweb site using AD membership provider through login control here is the code to do that :
<system.web>


<authentication mode="Forms">

<forms name="ADAuthCookie" timeout="10" loginUrl="Login.aspx" defaultUrl="Default.aspx"></forms>
</authentication>

<authorization>
<deny users="?"/>

<allow users="*"/>
</authorization>

<membership defaultProvider="DomainLoginMembershipProvider">
<providers>

<add name="DomainLoginMembershipProvider"
type="System.Web.Security.ActiveDirectoryMembershipProvider,System.Web, Version=2.0.0.0,Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

connectionStringName="ADConnectionString"
attributeMapUsername="sAMAccountName"

enableSearchMethods="true"
connectionUsername="User@Domain.com"

connectionPassword="Password" />
</providers>

</membership>


Dont for get to add asp.net 2 login control.

Attachable Subscribers in SQL Server Replication 2008/2005(Part 2)

This feature can be used when deploying a large number of pull subscriptions, which is common in merge replication. Microsoft has announced that Attachable Subscribers in SQL server 2005/2008 are deprecated features ,and this features will be removed in the next releases of SQL server 200x. for more information about this deprecated features information check http://msdn.microsoft.com/en-us/library/ms143550.aspx . Although that Microsoft tries to add alternative solution to MSF files that are created as result of copying the subscription databases (Databaese.msf file) after applying it ,it seems to be inaccurate and associated with incomplete solution with a lot of errors http://msdn.microsoft.com/en-us/library/ms152488.aspx .
- For informational and historical reasons I’ve add a referencing to the method of attachable subscribers in SQL Server 2000 to show the features that have been deprecated in SQL server 2005/08 you can skip this part and move to section B :

A- In SQL Server 2000 the scenario was as below:

On the publisher:
1. Create the database snapshot.
2. Back up the published database.
3. Create a temporary database and restore the published database on the temporary database.
4. Create a new 'no-sync' pull subscription from the temporary database to the published database.
5. Run the merge agent to synchronize the databases.
6. Copy the subscription databases (database.msf file)
7. Register the subscriber computer
8. Register the subscribing database using sp_addmergesubscription
On the subscriber:
1. Attach the subscription database copy (Database.msf)
2. Create a pull subscription using sp_addmergepullsubscription
3. Connect to the publisher using a dial up connection
4. Run the merge agent to synchronize the data.

B-In SQL Server 2008/05 :

-Subscribers could be initialized by using other subscribers replicated databases without direct publisher snapshot sachems:
1 backup subscriber database.
2 Attaches to a separated server backed up database with keeping replication information option check box checked to preserve replication information.

3 -Rowguid column in new attached Db show shared related publisher information.

4- Run generated scripts from old subscriber on a new subscriber to get publisher information such as name, server location, and login account to:
use [CommonDB_Sub]
exec sp_addmergepullsubscription
@publisher = N'ABDELRAHMAN', @publication = N'Pub_ItemSalesPrice',
@publisher_db = N'CommonDB', @subscriber_type = N'Local', @subscription_priority = 0,@description = N'', @sync_type = N'none'
exec sp_addmergepullsubscription_agent @publisher = N'ABDELRAHMAN',
@publisher_db = N'CommonDB',
@publication = N'Pub_ItemSalesPrice', @distributor = N'ABDELRAHMAN',
@distributor_security_mode = 0, @distributor_login = N'sa',
@distributor_password = N'nsg_ss_0103', @enabled_for_syncmgr = N'False',
@frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0,
@frequency_recurrence_factor = 0, @frequency_subday = 0,
@frequency_subday_interval = 0, @active_start_time_of_day = 0,

active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N'nsg_ss_0103', @use_interactive_resolver = N'False', @dynamic_snapshot_location = N'',@use_web_sync = 0

GO
Note : its important to set the prameter of both stored procdsure sp_addmergepullsubscription , sp_addmergepullsubscription_ agent,its important to set @sync_type = N'none' to create subscriber without publisher installation also set @alt_snapshot_folder a path of the replication data after copying unc folder from the publisher .

6 - New subscriber is generated that involve the publisher informations.

7 .In order to register the subscriber information to the publisher you need to run the following script :

use [CommonDB]
exec sp_addmergesubscription @publication = N'Pub_ItemSalesPrice',
@subscriber = N'ABDELRAHMAN\MSSQL2', @subscriber_db = N'CommonDB_Sub', @subscription_type = N'pull', @subscriber_type = N'local', @subscription_priority = 0, @sync_type = N'none'

This will create subscriber to the publisher database

8. start pull data between publisher and subscriber, in our case we need to pull data from publisher through start button in View synchronizations status:

You can see all agent history by press view Job History.

C-Testing Replication Synchronization in SQLserver 2008/05

1. Add new row to the attached subscriber and press view sync status and start sync agent.

SQL error msg 20 state 16 Encryption not supported on SQL Server

SQL server 2000 client handshak not able to connect to server resulted in the following error :
[Microsoft][ODBC SQL Server Driver][Shared Memory]Encryption not supported on SQL Server
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (PreLoginHandshake()).
to overcome this problem for unknown reasons unForce protocol encryption under Client configration utilitiy ,by unchecking Force protocol encryption ,after that you should be able to connect to all your sql servers .

Setting up Merge Replication in SQL Server 2008/05 (part1)

4.1 First of all you must add NT Network Service group to be Administrator group at both Publisher and subscriber if you have setup MS SQL server 2008 to use Network services Account as SQL server Service startup accounts.



4.2
Define a Server to be Distributed Server and Define Data Publisher as the following picture :

4.3 Select Publication Database .
4.4 Define which replication Model you want as described in Section 1.4 in our case we chose Merge publication.
4.5 Define the type of SQL server Subscribers this is for compatibility with earlier version of SQL server.
4.6 Select the Articles that are needed to be published to subscribers.

4.6 Add filter to the published table through limiting the amount of published data .

4.7 Choose between either to create snapshot schema directly or schedule a time where the schema are created .

4.8 specify the account under which SQL server snapshoot agent will create schema .

4.9 Finish the publication process by naming the publication name.

4.10 Press Finish.and see the publication created for you :

4.11 Now you can start by creating your own subscribers ,in our case we start creating local subscriber for simplifying the process :

4.12 Select the publisher at which you want your subscriber to get data from .

4.13 Select where the replication agent is to be run ,specify to run at distributor wish is called push subscription -or to let the agent work at the subscriber side ,this called pull data subscription (And by default ananymous Subscribtion).

4.14 Choose each the subscriber and create new subscription Database for, Add more subscriber or If the subscriber not exist it could be add.

4.15 specify replication agent Security under which subscriber agent synchronization start.

4.15.1 Choose to run under SQL server Agent services account that you had assigned during setting up SQL server 2008.
4.15.2 Choose to run under Windows security Account which must be member of machine administrator Group.
4.16 Specify synchronization schedule at which synchronization agent process the data .
You can choose either to let agent work continually according to the replication type once new record in publisher database add or to run on demand or to schedule time .

4.17 determine the first initialization of each subscriber to be immediate or to be initialized once the replication agent starts the synchronization process.

4.18 Merge replication offers two subscription types: server and client (referred to in previous versions of Microsoft SQL Server as global and local, respectively). Subscribers with a server subscription can:
· Republish data to other Subscribers.
· Serve as alternate synchronization partners.
· Resolve conflicts according to a priority you set.
Most Subscribers do not require this functionality and can use a client subscription. Client subscriptions still allow conflict detection and resolution, but Subscribers are not assigned a priority: the first Subscriber to submit a change to the Publisher wins any conflicts that might arise from that change.For each Subscriber, select Client or Server from the drop-down list box in the Subscription Type column. For Subscribers with server subscriptions, enter a number between 0 and 99.99 in the Priority for Conflict Resolution column (the higher the number, the higher the priority for the Subscriber).

4.19 Click finish button,
Now note tool tip that appears when your mouse get over the new created subscriber node .

What is SQL Server Replication ?

1- Reviewing SQL Server Replication (Historical)
Replication distributes data from the central database to one or more target databases, and merges changes from a target database into the central one. Both the source database and the destination database can be implemented as a SQL Server database or as any other data source, as long as an OLE DB provider is available to handle the nitty-gritty details of the particular data source you choose (such as ORACLE, Access, and so on).
The practical reasons to perform replication are to distribute workload and to
synchronize data among remote databases that you want to keep in sync. By maintaining identical data sets across multiple databases you can provide better performance.
Local users can connect to and use their own local databases, instead of
connecting to a remote central server. Your sales will be able to send sales leads back to a central database. Or you can use replication to keep a standby server in sync so you can switch to it should your primary server fail.
The replication architecture is quite complex, as it was designed to meet a variety of needs. Here are the basic concepts you ought to understand :

1.1 Basic replication terminology
Replication consists of two major components:

A - Replication components—SQL Server components used in replication:
Publisher
Distributor
Subscriber. (n) .

B- Replication Agents—Utility programs that assist in the replication process:
Snapshot Agent
Distribution Agent
Log Reader Agent
Queue
Reader Agent,
Merge Agent.


1.2 The replicated data are organized into the following categories:

1- Publication : wrapper for distributed data; a collection of one or more
Articles scheduled for publication (replication).

2- Article: basic unit of replication. It can be a table, certain columns in
The table, certain rows in the table, a view, or even a stored procedure.
More than one article together comprises a publication.

3- Subscription: Request to receive a publication. The two basic types of
Subscription are the Pull subscription (initiated by the Subscriber) and the
Push subscription (initiated by the Publisher).

1.3 Each server participating in the replication is assigned one or more of the following
Roles:

Publisher A source server for the distributed data. It maintains all the
Information about data specified for publishing.

Distributor An intermediary between the Publisher and the Subscriber;
it can also be both Publisher and Subscriber. Its role varies according to
the type of replication.

Subscriber The final destination of the distributed data. It is a recipient
of the publications it has subscribed to; depending on the type of replication,
it may also be able to propagate changes to its own set of data onto
the Publisher.

1.4 Replication Types:
Snapshot replication—This type of replication takes a snapshot of the
data in the Publisher database and replaces it with the entire data set of
one or more subscribers; subsequent replication again replaces the complete
data set in the subscriber database(s). Though notable for being virtually
foolproof in providing synchronous data sets, this type of replication
increases network traffic and, as intervals increase, data sets become less
synchronized.


Transactional replication—This type of replication is all about changes.
It propagates changes only to subscribers. It starts with an initial snapshot
Replication and then distributes selected transactions in the Publisher
database transaction log (marked for replication) to the target servers.
Snapshot replication is also regularly scheduled to ensure consistency of
the data. Its major advantages are more timely updates and much lighter
network traffic than you get with pure snapshot replication.

Merge replication—This type of replication allows subscribers who make
changes to their local copies of the data to merge these changes into the
source database. Merge replication is not transactional and relies on conflict
resolution to determine the precedence of the changes.

2- Selecting a Replication Model
2.1Central publisher—The most common replication model. It maintains
Publisher and Distributor databases on the same server, with subscribers
configured somewhere else.

2.2 Central publisher with remote distributor
The Publisher database is
on one server and the Distributor database on another; subscribers are by
default placed on remote servers (you do not need to maintain a Subscriber
on the same server as a Publisher).

2.3 Central subscriber—One Subscriber collects data from several publishers;
the data can then be republished, as nothing prevents the server from
Wearing several hats—it can be a Subscriber, a Publisher, and a Distributor
at the same time.

2.4 Publishing subscriber—Republishes received data to other Subscribers;
see the preceding description of the central-subscriber model.


3-Preparing for Replication
3.1 Snapshot replication:
1- Consider space requirements as because the data will be moved as a whole.
2- Timing, because replication is a strain on network resources as well as on database resources
3- Importance to replicated data.

3.2 Transactional replication:

Increase the size of transaction Log on publication Server to make sure that all replicated data are published to Subscribers before they are purge.

3.3 Merge replication:

1- Every table must have its Primary Key.
2 - Foreign key must includes referenced tables.