SQL Server blocking occurs when one connection places a lock on a table (or selected rows, pages, extent) and another connection attempts to read or modify the data when the lock is in effect. Continue reading #SqlServer interview question: What is blocking?
Category Archives: Sql Server Tutorials
Database mail setup for Gmail,Hotmail,Yahoo or AOL
Scenario
One great feature of SQL Server is the ability to get alerts when there are issues. The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account. In some cases you may not have a mail server, but still want to setup alerts. In this tip we will walk through how you can setup Database Mail to use email services like Gmail, Hotmail, Yahoo, etc…
Solution
For this example, I have a SQL Server test environment configured and I want to test the alert mechanism using Hotmail. The following outlines the settings to do this. Continue reading Database mail setup for Gmail,Hotmail,Yahoo or AOL
Automating Tranzactional Initialization from a backup in SQL Server
Scenario
There are situations where you might have a large SQL Server database that you need to replicate. Since the database is very large we want to initialize the Subscriber from the SQL Server backup and not use a snapshot. How do we bundle all the steps together to achieve this without performing them manually?
Solution
I want to cover the basic steps needed to setup a “Push Type Transactional Replication”. This tip assumes you already have an understanding of SQL Server Transactional Replication and I will show how to bundle all the steps into one process. Continue reading Automating Tranzactional Initialization from a backup in SQL Server
How to change default snapshot folder for Replication?
Scenario
You’ve set up replication initially by using the GUI and accepting all defaults and now due to growth you need to change your default replication snapshot folder location to another drive since the current location is a local disk and cannot be expanded to accommodate the growth.
This concerns you as you cannot afford downtime to perform a re-initialization of all your existing subscribers as they are needed to run the business.
Solution
You can change the snapshot location without having to perform a re-initialization. The only caveat to this is you do need to generate a new snapshot. This snapshot will not be applied to your subscribers, but has to go through the generation process. I recommend doing this at night or weekends to minimize the impact of the snapshot agent locking tables and contention on the publisher database. Continue reading How to change default snapshot folder for Replication?
Peer to peer replication in SQL Server 2005
SQL Server 2005 has a lot of features and sometimes these additions are overlooked as a new way of configuring and managing your SQL Server environment.
One of these features is Peer to Peer transactional replication. In the past, people have used transactional replication for load balancing, to keep a read only version for reporting purposes or possibly for a failover solution. Continue reading Peer to peer replication in SQL Server 2005