Making SQL Server Replication work on Network Service Account

This is one of the topics i have been working on since last few days. There's not much information available on the web on how to do that so i decided to log it in this blog.

Problem Statement :-
Setup a SQL Server replication while running the SQL Server in "Network Service" Account.

Normal Convention :-
Most local SMEs i spoke to suggested that it is not possible to setup replication between SQL Servers using the Network Service account and suggested that i use the domain account instead. However the problem with that solution is that domain account password expires in some time and then we may even get a production downtime.

Proper Solution :-
The SQL Service like any other windows service can be run using Local Service, Network Service or a Domain Account credentials. Now if you want to setup a replication Local Service account is of no use since that is an account which has no identity over the network. Domain account is not ideal since passwords have to be changed at regular intervals resulting in downtime as well as maintainance costs. So Network Service account is the ideal way to go. This account presents the machine identity over the network.
So if we want to setup replication using this account the machine identities should have access to databases. We can create a security group in active directory and add all the machines which want to talk to each other in replication should be added to this group. Then give the permission to this security group on all the databases.
This way when the replication service on one machine sends the replication related instructions to another machine it presents the machine credential which has the permissions on the database, the replication has no security related problems.
so to summarize
1) Make sure that all the SQL Service and SQL Agent Service is running using Network Service credential
2) Create a security group in the domain and add all the SQL machines which would be part of replication to this group
3) Give this security group appropriate permissions on all the databases which would be part of replication.

Happy coding...

~Abhishek

Name of the server on which a query is executing

Sometimes while executing a batch of queries it is a must to find out what server the batch is executing on. e.g. while creating a linked server you might not want to create a linked server for the local server.
This can be achieved by querying the sys.servers table and check for the server with server_id 0.
so the query to do this is

select name from sys.servers where server_id = 0

Happy coding...

~Abhishek

Synonyms and Linked Servers in SQL Server

Someone in my project wrote a perl script which used to generated a SQL script to create synonyms to other databases.
Now this SQL Script to create synonym used to run after the required databases were deployed and it uses to work fine till we went into the pre production deployment phase of the project. And as soon as we reached to this environment the setup itself started failing. The only difference between this environment and the test environment was that the databases were hosted in different machines.
After some investigation we established that the generated SQL script didn't take this into consideration. It was working fine in dev environment since all the databases were hosted in a single machine. As soon as the databases were separated the names of databases we were referring to in the script became invalid.

Linked servers came to rescue in this situation. Basically we added a Linked server to the server we wanted to create synonyms in and qualifies the table names with the linked server names as well.

On digging further the Linked server is an important and useful feature in SQL Server since it can not only make one SQL server interact with another, it can actually make SQL Server interact with any other OLE Source.
We can even use it to do the distributed transactions across various data sources.

Quickest way to Add a linked server to the database is to use the wizard in SQL Server Management Studio. One can also use stored procedure sp_addlinkedserver in the master database. sp_serveroption can be used to configure the options on how to connect to this linked server.


More information can be found here.