DUPLICATE INDEXES

============== exact duplicates ==============================
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

==================================================================
-----------------------------
Overlapping indxes
------------------------------
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'partialduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and (c1.cols like c2.cols + '%'
or c2.cols like c1.cols + '%') ;

=====================================================

SQL Server Profiler Stored Procedures

SQL Server supports the following system stored procedures that are used by SQL Server Profiler to monitor performance and activity.

sp_trace_create
sp_trace_setfilter
sp_trace_generateevent
sp_trace_setstatus
sp_trace_setevent

SQL SQRVER with SP's

http://sqlserverpedia.com/wiki/SQL_Server_Release_Date_Calendar#Checking_the_SQL_Server_Version

DBA WORLD: Microsoft CodeNames for Products

DBA WORLD: Microsoft CodeNames for Products

Microsoft CodeNames for Products

http://en.wikipedia.org/wiki/List_of_Microsoft_codenames#SQL_Server_family

Instant File Initialization Speeds SQL Server

What is Instant File Initialization?

In my first two examples, before instance file initialization was turned on, the reason it took so long for the database to be created, or the database to be restored (before a database can be restored, its space must first be pre-allocated, much like creating a new database), SQL Server had to go to every page in the 50 GB database and zero each one of them out. It can take a lot of time for SQL Server to go to every 8K page in a file (especially very large files) and physically zero out each page. When instant file initialization is turned on, SQL Server doesn’t have to zero out every 8K page that has been allocated. Instead, the space is just allocated to SQL Server by the operating system in one fell swoop, which is a very quick process, potentially saving you a great deal of time.


Read more: http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server

Bidirectional, Transactional Replication VS Peer-to-Peer Transactional Replicatio

With Bi-Directional replication if you want to make a schema change you must drop your publications, and then drop your subscriptions, make the schema change and then recreate the publications and subscriptions on both sides. Then you start up the agents and you are in business again. The best way to deploy subscriptions in bi-di transactional topologies is by restoring the subscriber via a backup.

Now if your users are banging away on your first publisher while you do this it will be hard to achive consistency between both nodes in your bi-directional replication toplogy, as these updates may or may not be in the backup. You can run validations after the fact but it hard to cobble together a consistent topology while you are live. It can be done, but its hard. Plus table locks are held when the publication is created. So you can run into a lot of locking.

With peer-to-peer in SQL 2005 you again have to quiesce the system, make your change, ensure it is everywhere and then let your users back on. Its the same problem.

WIth merge replication and bi-di transactional the model is a hub and spoke methodology. You have a central publisher and multiple subscribers all talking to this central publisher which is the clearing house for changes. Peer to peer is a mesh topology which means a<->b, b<->c, c<->a. b can drop off and a can replicate with c until b comes back on.

You can't do this using bi-directional transactional replication and merge.

More on
http://technet.microsoft.com/en-us/library/ms151196.aspx

======================================================

How to Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio)

http://technet.microsoft.com/en-us/library/ms152536.aspx

SQL Server 2008 Cluster Instalation

http://www.mssqltips.com/tip.asp?tip=1709

When using SQL Server Management Studio Express I get the error "SQL Server does not allow remote connections". How to resolve this error?

Question:

In SQL Server 2005 when trying to connect to a remote database with SQL Server Manager Studio Express, I get this error: "An error has occured while establishing a connection to the server. When connecting to SQL Server 2005,this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:Named Pipes Provider,error:40-Could not open connection to SQL Server))" How to resolve this error?

Answer:

In addition to the above error the user might get the following errors too:

*
SQL Server does not allow remote connections
*
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
*
Server does not exist or access denied

The errors are self-explanatory for a Database Administrator, but for a beginner or Developer these errors will be more frustating.

It should be noted that SQL Server 2005 Express is not automatically configured for 'remote access' during the installation. This is not a bug merely the default configuration. The simple solution for this problem is to enable the network protocols for the SQL Server Express instance using Surfact Area Configuration (SAC) utility for SQL Server 2005 programs group on your machine.

Surface area reduction is a security measure that involves stopping or disabling unused components. Surface area reduction helps to improve security by providing fewer avenues for potential attacks on a system.

For new installations of Microsoft SQL Server 2005, some features, services, and connections are disabled or stopped to reduce the SQL Server surface area. For upgraded installations, all features, services, and connections remain in their pre-upgrade state."

SQL Server Surface Area Configuration is available on the SQL Server Start menu:

*
On the Start menu, point to All Programs, Microsoft SQL Server 2005, Configuration Tools, and then click SQL Server Surface Area Configuration.
*
Choose SQL Server 2005 Network Configuration and select the installed SQLEXPRESS instance.
*
On right-hand pane select the required protocol either TCP/IP or Named-Pipes (or both), right-click with the mouse to set it to 'Enabled'.

In some special cases it may be an additional requirement to start the SQL Browser service, the usage and benefit of having this service run is that users connecting remotely do not have to specify the port in the connection string. It is a Best Practice in Security aspects to not to run the SQL Browser service as it reduces the attack surface area by eliminating the need to list on to an UDP port from client.

If your network is tightened with relevant firewall and security access then (in special cases) you might need to enable SQL Server and SQL Server Browser to be exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall or any other firewall software application that is used.

Lastly, you might need to reboot the machine in order for these network configuration changes to take affect as simply restarting SQLServer and SQL Browser services may be insufficient.

For more information on usage of Surface Area Configuration tool refer to updated SQL Server Books OnLine.

SQL Server Management Studio takes a long time to load, how to improve the load performance of this tool?

http://www.sql-server-performance.com/faq/sql_server_management_studio_load_time_p1.aspx

Good Link.

Microsoft SQL Sever management Studio: Failed to open connection dialog

Error : Microsoft SQL Sever management Studio: Failed to open connection dialog. Even after reinstalling the client tools still the error persists.

Solution:
This is a common question in the forums and newsgroups when any of the SQL Client tools gets corrupted. There might be chances that few times by reinstalling the client tools and if there is an issue with a corrupted or deleted (mistakenly) a .DLL file then the only way is to repair the tools.

You can either follow the GUI with SETUP of Management Studio tools or use the command-line operation.

Repairing SQL Server is a command-line operation. You can repair by inserting the installation media into the disk drive and typing the following:

start /wait \setup.exe /qb REINSTALL=SQL_Tools90 REINSTALLMODE=OMUS