Some changes are not replicated to the subscribers on Transactional Replication SQL Server 2005

You can insert the deleted row in the subscriber database( if you have a backup of table)

Then start the replication agent , It will fix the problem.
http://support.microsoft.com/kb/954054

Replication in SQL Server

Replication is one of typical functionality in SQL Server. Basically Replication lets you to copying and distributing data and database objects like table, stored procedure, view and UDF to one publisher database to multiple subscription database/server on different location.


There are three types of replication

1. Snapshot
2. Transactional
3. Merge.

Replication need a special database called distribution to copying data to another server.
Main components in Replication

Distributor : Main functionality is distributing data between Publisher and Subscriber.

Publisher : Publisher is main Database Server which has database to replicating data to subscriber
You can create many publications from Publisher. There is limit for publishing articles. Maximum number of articles can be published under one publication is 256.

Subscriber : Always receiving data from publisher and in merge replication subscriber also sending back data changes to publisher

Articles : Articles nothing but Tables

There are five agents are there

1. Snapshot agent
2. Log Reader agent
3. Queue Reader agent
4. Distribution agent
5. Merge agent

sp_change_users_login to fix Orphaned SQL Server users

The stored procedure sp_change_users_login used  to fix Orphaned SQL Server users.

Microsoft says this feature will be removed in a future version of Microsoft SQL Server.  so instead of using this sp start using  ALTER USER command.

FILESTREAM

The new FILESTREAM funcationality  in the SQL Server 2008 allows you to configure a VARBINARY(max) column, so that the actual data in stored in the file system/storage area rather than within the database.

To enable FILESTREAM feature you must configure SQL Server Service for that instance.

sp_configure 'filestream_access_level' , 2


go
Reconfigure

Note : Database Mirroring is not compatiable with FILESTREAM data

The mirror database has insufficient transaction log data

When you Setting Database Mirroring if you will get the error message like  "The mirror database has insufficient transaction log data".


The mirror database, "Northwind", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

Error : The mirror database has insufficient transaction log data
Solution :
1.. Take the Transaction Log backup from Principal server database and
2. Restore Log on Mirror database
 Restore Log on Northwind from disk = 'c:\mssql\backup\DBP_TLog.TRN' with NORECOVERY
3. Now try to setting up Database Mirroring again from Principal Server

Microsoft SQL Server 2008 Service Pack 2

You can write in Query window to find which version of SQL Server 2008 is running ?


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

(or)
SELECT  @@Version
Microsoft SQL Server 2008 Service Pack 2 (SP2) is now available for download.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8fbfc1de-d25e-4790-88b5-7dda1f1d4e17

Version                                   Build
-----------------------------------------------------
Service Pack 2 for SQL Server 2008     10.0.4000.00
Service Pack 1 for SQL Server 2008     10.0.2531.00
RTM                                    10.0.1600.22

Dedicated Administrator Connection (DAC)

The DAC allows an dba to access a SQL Server to troubleshoot problems. You can connect using either SQLCMD or SSMS.

DAC is working with port 1434 to allow client connection from Remote computers

To enable DAC for a remote computer,

sp_configure 'remote admin connections', 1;
go

In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named UKDEVSQL\HRMS,
type ADMIN:UKDEVSQL\HRMS

Data and Log Files configuration

1. There are two types of data files in SQL Server  Primary and Secondary

2. You can have one Data Primary file and 32766 Data Secondary file in SQL 2005
3. Transaction log records database modification you can create multiple log files per database to facilitate faster recovery
4. Filegroup is a logical structure that lets DBAs group data files and mange them as a logical unit
5. Filegroup contains data only, you can not create log file
6. Configuring database files with RAID(Redundant array of inexpensive disks) for better peformance
7. RAID 1 known as disk mirroring, provides a redundant copy of the selected disk, RAID1 improves read performance but can degrade the performance of write operations
8. RAID5 stripes the data across the disk of the RAID but it also adds parity information to provide fault tolerance.
9. RAID10 offer better availability and performance than RAID5
10. RAID10 is recommended RAID system for Transaction log, Data files and Index files
11. Always place Transaction log in RAID10 and Data and Index files on RAID5
12. Do not put Data files on the same drive that contains OS files
13. Put the Transaction log file(LDF) on separate drive from data files
14. Put the Tempdb database on a separate drive possible preferably on RAID 10 or RAID 5

Microsoft SQL Server code-named “Denali” – the next version of SQL Server

This is an exciting time for the SQL Server Team. After Ted Kummert announced the next release of SQL Server, Microsoft SQL Server code-named “Denali”, yesterday at the opening keynote of PASS Summit 2010, today Quentin Clark, General Manager of the Database Systems Group, gave PASS attendees a closer look into some of the exciting new features of code-named “Denali”.


SQL Server code-named “Denali” will help empower organizations to be more agile in today’s competitive market. Customers will be able to efficiently deliver mission-critical solutions through a highly scalable and available platform. Industry-leading tools will help developers quickly build innovative applications while data integration and management tools help deliver credible data reliably to the right users and new user experiences expand the reach of BI to enable meaningful insights.

Today, we showcased the following areas of code-named “Denali” including new capabilities that will be delivered to customers:

Enhanced mission-critical platform: An enhanced highly available and scalable platform through the new SQL Server AlwaysOn for greater flexibility in achieving increased availability and data protection and new Column-Based Query Accelerator for huge performance gains in data warehousing.

Developer and IT Productivity: A new unified development experience for data developers called SQL Server Developer Tools code-named “Juneau”, new beyond relational enhancements including FileTable for file storage within the SQL Server database, easier of use delivered via SQL Server AlwaysOn, data integration tools and features such as security & supportability.

· Pervasive Insight: Expand the reach of BI to business users via Project code-name “Crescent”, a highly interactive, web-based data exploration and visualization tool built on the breakthrough analytical performance of the VertiPaq technology. Meanwhile, holistic data integration and management tools through enhanced Master Data Services and new Data Quality Services will help ensure organizations can deliver the right data to the right users at the right time.

We are also making available the first Community Technology Preview (CTP) for SQL Server code-named “Denali”. You can download SQL Server code-named “Denali” CTP today!

The first public CTP provides an opportunity for early adopters to submit feedback on an initial set of capabilities including:

· Select capabilities of the new integrated high availability solution, SQL Server code-named “Denali” AlwaysOn, including availability groups, single active secondary for offloading read-only workloads and multi-site clustering

· Flexible server roles to allow administrators to create custom roles for ease separation of duties
· Simplified development and standardized deployment, configuration and management for SQL Server Integration Services

Here are a few things you can do to get to know SQL Server code-named “Denali” a little better:
1. Visit the code-named “Denali” web page
2. Download the first CTP
3. Reference Books Online

Download the CTP, play with it, and let us know what you think. We’re excited about SQL Server code-named “Denali” and we hope you are too!

http://blogs.technet.com/b/dataplatforminsider/rss.aspx

SQL Server Top 10 Secrets of Tempdb database

1. Tempdb is re-created every time when SQL Server restarts
2. Tempdb is always SIMPLE recovery mode
3. Certain database options can not be set for tempdb like OFFLINE, READONLY & CHECKSUM
4. You can not drop or backedup tempdb database
5. You can not run DBCC CHECKDB against Tempdb
6. You can not drop the guest user from Tempdb
7. Snapshot database can not be created against Tempdb
8. Shrinking Tempdb is not recommended
9. Move Tempdb database to new location after installation of sql server
10. Create a multiple Tempdb files give you better performance one file per CPU( Microsoft Best practice guide line says, It may be good for SAN), You need carefully plan this if you server really want it)

Managing Data Files and Transaction Log files

a. If you have more than one Data Files( MDF) make sure to keep same size of all data files e.g( AdvenDb_Data1.MDF, AdvenDb_Data.NDF)

b. Place the Transaction log file(LDF) on separate Drive

c. Disable auto shrink option

d. Alter database to increase size to make more space for data files

e. Auto growth should be configured correctly

f. Enable Auto Update Statistics &  Auto Create Statistics

g. Monitor disk space on Data file and Transaction Log file Drive

h. Monitor database size and Transaction Log file size set threshold level to get notification

i. Page Verify option set to CHECKSUM

Snapshot Database

You can not create snapshot database using SSMS instead you should use Query window to write T-SQL
If any of your user database has a snapshot database you can not dropped , restored, detached the user database.
Snapshot database can not be created against system databases like master , model and tempdb
Snapshot database can not be backup/restored.