February 25, 2011

Move distribution database to another drive

Move distribution database to another drive:

There are scenarios in SQL server Replication that we need to move datafiles and logfiles of distribution database to another drive/folder.

Pl follow below steps to move it without having to redo the whole replication process.

1. Start and Stop a Replication Agent

To start and stop a Snapshot Agent or Log Reader Agent from Management Studio

Connect to the Publisher in Management Studio, and then expand the server node and the Replication folder.

Expand the Local Publications folder, and then right-click a publication.

Click View Snapshot Agent Status or View Log Reader Agent Status.

Click Start or Stop.

To start and stop a Queue Reader Agent from Management Studio

Connect to the Distributor in Management Studio, and then expand the server node.

Expand the SQL Server Agent folder, and then expand the Jobs folder.

Right-click the job for the agent, and then click Start Job or Stop Job. The name of the job for the Queue Reader Agent is in the form []..

To start and stop a Snapshot Agent, Log Reader Agent, or Queue Reader Agent from Replication Monitor

Expand a Publisher group in the left pane, expand a Publisher, and then click a publication.

Click the Agents tab.

Right-click an agent, and then click Start Agent or Stop Agent.

2. ALTER DATABASE distribution SET OFFLINE

3. Move the data and log file to the new location


ALTER DATABASE distribution MODIFY FILE ( NAME = distribution , FILENAME = 'C:\MSSQL\Data\distribution.MDF')
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_log , FILENAME = 'C:\mssql\Data2\distribution.ldf')
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_02 , FILENAME = 'C:\mssql\Data2\distribution_02.ndf')

4. ALTER DATABASE distribution SET ONLINE
5.Start the Log Reader Agent and Distribution Agent

February 03, 2011

SQL server DBA Frequently used Checklists

1. how to check msdtc status on sql server 2005 server

i) Go to Services.msc and start Distributed Transaction Coordinator

ii)Go to Dcomcnfg.exe
My Computer -> Properties -> MSDTC Tab -> Security Configuration
Network DTC Access is checked
Network Administration is checked
Network Transactions is checked
Network Clients is checked
XA Transactions is checked

2.

Creating DataFrames from CSV in Apache Spark

 from pyspark.sql import SparkSession spark = SparkSession.builder.appName("CSV Example").getOrCreate() sc = spark.sparkContext Sp...