SQL SERVER Service Packs

SQL SERVER	RTM (Gold, no SP)	SP1	SP2	SP3	SP4
SQL Server 2014      codename Hekaton
SQL Server 2012      codename Denali	11.00.2100.60	11.00.3000
SQL Server 2008 R2      codename Kilimanjaro	10.50.1600.1	10.50.2500	10.50.4000
SQL Server 2008      codename Katmai	10.00.1600.22	10.00.2531	10.00.4000	10.00.5500
SQL Server 2005      codename Yukon	9.00.1399.06	9.00.2047	9.00.3042	9.00.4035	9.00.5000
SQL Server 2000      codename Shiloh	8.00.194	8.00.384	8.00.532	8.00.760	8.00.2039
SQL Server 7.0      codename Sphinx	7.00.623	7.00.699	7.00.842	7.00.961	7.00.1063

Password Generator Procedure

CREATE PROCEDURE usp_RandomPassword (@pass_len AS int, @password nvarchar(400) OUTPUT)
AS
DECLARE @ValidChar AS nvarchar(400)
SET @ValidChar = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890~!@#$%^&*()_-+={}[]\|:;"<>,.?/'
DECLARE @counter int
SET @counter = 0
SET @password = ''
WHILE @counter < @pass_len
BEGIN
SELECT @password = @password + SUBSTRING(@ValidChar, (CONVERT(int, (LEN(@ValidChar) * RAND() + 1))), 1)
SET @counter += 1
END;
GO


To use the stored procedure to get password:

DECLARE @new_password varchar(50)
EXEC dbo.usp_RandomPassword @pass_len = 10, @password = @new_password out
SELECT @new_password

Compare Replicated Tables for Differences

Problem:- There is a mismatch in the data which is replicated in a transactional replication.

Solution :-

Step 1:- Need to identify the tables
Step 2:- Compare using TableDiff Utility
Step 3:- Update the same on subscriber as publisher wins always. (applicable only if data is less)
else we need to drop and re- add the article.



Replication Diff Tool Command Line Options

        usage: tablediff

          -- Source Options --
 -sourceserver          Source Host
 -sourcedatabase        Source Database
 -sourceschema          Source Schema Name
 -sourcetable           Source Table or View
 -sourceuser            Source Login
 -sourcepassword        Source Password
 -sourcelocked          Lock the source table/view durring tablediff

          -- Destination Options --
 -destinationserver     Destination Host
 -destinationdatabase   Destination Database
 -destinationschema     Destination Schema Name
 -destinationtable      Destination Table or View
 -destinationuser       Destination Login
 -destinationpassword   Destination Password
 -destinationlocked     Lock the destination table/view durring tablediff

          -- Misc Options --
 -t             Timeout
 -c             Column Level Diff
 -f             Generate Fix SQL (You may also specify a file name and path)
 -q             Quick Row Count
 -et            Specify a table to put the errors into
 -dt            Drop the error table if it exists
 -o             Output file
 -b             Number of bytes to read for blob data types
 -strict        Strict compare of source and destination schema
 -rc            Number of retries
 -ri            Retry interval

Example 1:-


C:\Program Files\Microsoft SQL Server\110\COM>tablediff -sourceserver SQBILS -sourcedatabase MSTER_DATA -sourcetable CustomrCRM-destinationserver ACPB01 -destinationdatabase PCTER_DATA_REPL -destinationtable CustomrCRM

Example 2:-
tablediff -sourceserver PRADYOTHNAP02 -sourcedatabase TELIPHONECALLS -sourcetable student -destinationserver santhoshc -destinationdatabase TELICALL_REPLICA destinationtable Student

SQL 2008 R2 database Move to A New SAN

SQL  2008 R2 database Move

We recently moved our SAN to a new one on a Clustered SQL  Server.  First SQL server service could not start and after fixing that SQL Agent would not start for over a week and finally Microsoft's tech support helped to find and fix the issue. I found out that I had Missed the last step called Follow-up: After Moving All System Databases  in these instructions.  And if it is a cluster environment then you need to perform this on the active node.

 If this is your first exposure to this process as it was for me,  these instructions might be useful to you.

You can find step by step database (including System DB) move on Microsoft's website http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx 

They are very clear and extremely helpful and that is what used When we moved from old SAN to a new one. I added my own notes and some scripts that would help simplify some of the process such as detaching and attaching databases, specially if you have lots of DBs to move.

Make a full back of each Database and save it on a non-clustered drives/location.

1. Run and save the result to run at the end your process.  This generates  the attach db statements. You need to to this first as you will not have access to them once you detach the databases. Then you can replace the drive and the path to point to the new location.

-- *********** First change the Query Results to text  *************

 select

Case

When fileid = 1 then ' EXEC sp_attach_db @dbname = N''' + [name]+ ''',' 

+char(13) +

' @filename1 = N''' + filename + ''','

when fileid = 2 then ' @filename2 = N''' + filename + ''';'
+char(13)

 end as [Name_path]

from sys.sysaltfiles

                                    where dbid not in (1,2,3,4)  -- All dbs except system dbs

2. Detach all databases except, System DBs. You can run following statements to generate the detach syntax

SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''

+ Name + ''''FROM sys.sysdatabases

WHERE dbid not in (1,2,3,4) -- All dbs except system dbs

3. Change the System dbs file location ( except Master Db):

ALTER DATABASE  Model  MODIFY FILE ( NAME = modeldev, FILENAME = 'Drive Letter:\Path\model.mdf' )

ALTER DATABASE  Model  MODIFY FILE ( NAME = modellog , FILENAME = 'Drive Letter:\Path\modellog.ldf' )

ALTER DATABASE  MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = 'Drive Letter:\Path\MSDBData.mdf' )

ALTER DATABASE  MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'Drive Letter:\Path\MSDBLog.ldf' )

ALTER DATABASE  tempdb MODIFY FILE ( NAME = tempdev , FILENAME = 'Drive Letter:\PathL\tempdb.mdf' )

ALTER DATABASE  tempdb MODIFY FILE ( NAME = templog , FILENAME = 'Drive Letter:\PathR\emplog.ldf' )

4. Move all the .mdf and .ldf files to the new location

5. Detach Model, MSDB and Tempdb

SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''

+ Name + ''''FROM sys.sysdatabases

WHERE dbid in (2,3,4) -- Only system dbs except MASTER DB

Moving the master Database Procedure

Pasted from <http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx>

To move the master database, follow these steps. If this is clustered server then you need to perfrom these steps on all nodes

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.

4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf

If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf
-lE:\SQLData\mastlog.ldf

6. Stop the instance of SQL Server

 

7. Move Model, MSDB, Tempdb and Master data and log files to the new location

8. Restart the instance of SQL Server

If the msdb database is moved and the instance of SQL Server is configured for  Database Mail, complete these additional steps.

• Verify that Service Broker is enabled for the msdb database by running the following query.

  
SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb'
Important:

 

Follow-up: After Moving All System Databases

Pasted from <http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx>

If you have moved all of the system databases to a new drive or volume or to another server with a different drive letter, make the following updates.  If this is clustered server then you need to perfrom these steps on all nodes

Change the SQL Server Agent log path. If you do not update this path, SQL Server Agent will fail to start.

Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist.

 

Change the SQL Server Agent Log Path

From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.  If this is clustered server then you need to perfrom these steps on all nodes

1. Right-click Error Logs and click Configure.

2. In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file. The default location is C:\Program Files\Microsoft SQL Server\MSSQL10_50.\MSSQL\Log\.

 

Change the database default location

1. From SQL Server Management Studio, in Object Explorer, right-click the SQL Server server and click Properties.

2. In the Server Properties dialog box, select Database Settings.

3. Under Database Default Locations, browse to the new location for both the data and log files.

4. Stop and start the SQL Server service to complete the change.

 If  any reason you cannot modify the error log path using Management Studio you modify the registry: Again  if this is clustered server then you need to perfrom these steps on all nodes

1. Go to START --> Run and type Regedit

2. HKEY_LOCAL_MACHINE -> SOFTWARE -> MICROSOFT -> Microsoft SQL Server ->

 MSQL10_50.MSSQLSERVER -> SQLServerAgent and make sure it is pointing to the new location.

 

The concurrent snapshot for publication is not available because it has not been fully generated .

Problem:-

The snapshot files for a transactional publication are immediately removed after being generated by the snapshot agent.

Noticed that status of the publication articles are 3. Which is wrong either they should be 0 = Inactive, 1 = Subscribed or 2 = Active.

Solution :-

1) Delete the subscription and publication.

2) Check the tables to see if there are still rows for the publication database available:

select * from msdb..MSdistpublishers

select * from distribution..MSpublisher_databases

select * from distribution..MSarticles where publisher_db = 'distribution'

select * from distribution..MSsubscriptions where publisher_db = 'distribution'

3) Delete all rows that have still the 'publisher_db' from the just deleted publisher database:

delete from distribution..MSarticles where publisher_db = 'distribution'

delete from distribution..MSsubscriptions where publisher_db = 'distribution'

4) Re-create the publication subscriber and initialize.

Help Links :-

http://blogs.technet.com/b/claudia_silva/archive/2009/04/29/replication-snapshot-files-removed-after-being-generated.aspx

http://support.microsoft.com/kb/324401

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/baf99181-2fe1-4e6b-ba45-5d1d08103550/initialize-subscription-failing

http://stackoverflow.com/questions/1018339/the-concurrent-snapshot-for-publication-xx-is-not-available-because-it-has-not

Script to Enable/Disable Database for Replication

You can enable the database for replication using below script.

1 2 3 4 5

use master exec sp_replicationdboption @dbname = 'sqldbpool', @optname = 'publish', @value = 'true' go

If you have restore the database on test environment and you are getting the error that “Database is part of Replication”, you can clear/disable it by executing below query.

1 2 3 4 5

use master exec sp_replicationdboption @dbname = 'sqldbpool', @optname = 'publish', @value = 'false' go

List All Tables Starts With MDT

SP_TABLES

Syntax:-

sp_tables [ [ @table_name = ] 'name' ]
     [ , [ @table_owner = ] 'owner' ]
     [ , [ @table_qualifier = ] 'qualifier' ]
     [ , [ @table_type = ] "type" ]
     [ , [@fUsePattern = ] 'fUsePattern'];

Example :- 

Use Database
 go
EXEC sp_tables @table_name = 'mdt_%'