Encrypted DTS packages - Find DTS Package name

Happy New Year 2013 to all of you... I shall now start off with some techie stuff.

Most of us have been working on DTS\SSIS packages. Even though we get to hear very little about DTS packages now a days; sometimes it becomes inevitable to know some of the legacy left behind by older versions.

To identify which 'Encrypted' DTS package is being referenced by scheduled job.

Before I explain the method of doing the above task, let me tell you the reason why the 'DTS package calls' are 'Encrypted' at first place? I am sure many of us are already aware of the reason... but still... here it is...

Most of us are addicted to follow easy methodology; right click the DTS Package (SQL 2000) and schedule a job. The created SQL Job will have an encrypted name for DTS package in SQL job step.

For example:DTSRun /~Z0x99810D5EE6B8FC6BFEB92F1D9EB5849.....

the above value would be much more! I have cut short to keep it simple ;)

So, now the task; to identify which package is being called by scheduled job step.

For each Job which makes an encrypted call to DTS, from SQL Job Step, refer to the following steps:

1. To the end of Command (DTSRun) line, add /!X /!C
   For example:
   DTSRun /~Z0x99810D5EE6B8FC6BFEB92F1D9EB5 /!X /!C
   /!X means 'do not execute' and /!C means 'copy results onto Windows Clipboard'
2. Copy complete command DTSRUN with /!X /!C
3. Run the command from command line. One thing I noticed: The command can be either run from target server where the JOB is or from your local machine if it is the same network as of target server. I hope I did make some sense!
4. Open notepad, paste the clipboard result onto notepad. So, there it is... name of the package!!

SQL Agent Tokens

Consider a situation where in one needs to have SQL Jobs to be more independent of machine/instance and/or the main job itself. In such cases we can utilize one of the features of SQL Server called "TOKENS".

When a Job step is written using tokens it gives the same flexibility that "Variables" provide in software programs. I hope that makes sense! Many of us do understand the meaning and usage of Variables...  so will not explain the same.

When a token is used in a job step script, SQL Server Agent replaces the token at run time, before the job step is executed by the Transact-SQL subsystem.

Note: I have tried to explain the usage of tokens by making use of the following example.

For example: Consider a situation wherein we are saving the output logs of a Job from multiple servers (Development, Testing, and Production) to one central location. In such cases, we have two options of specifying the output path.

1. Manually setting the path for job on each of the Servers.

2. Or making use of "TOKENS" which makes our life easier.

Let’s make use of the tokens.

This centralization of output log location allows the use of common scripts for all environments.

Servername can be replaced by using SQL Server Agent Token [MACH], [DATE] and configure output to a common location shown below.

\abcd\Prod\SQLServersJobs\LOGS\

 The server name differentiates the environment context of the log.

Note:  The token templates used are different for SQL versions.

<Template>

SQL2000

[MACH]\[INST]_<jobname>_Step1_[DATE].log  -- Where <jobname> is the name of the job.

Example: SQL2000

\abcd\Prod\SQLServersJobs\LOGS\[MACH]_[INST]\MyJob_Step1_[DATE].log

The above would be converted to: (see that default instance returns as MSSQLSERVER)

 \abcd\Prod\SQLServersJobs\LOGS\A2MDEV101_MSSQLSERVER\MyJob_Step1_20091005.log

  SQL2005

$(ESCAPE_NONE(MACH))\$(ESCAPE_NONE(INST))_<jobname>_Step1_$(ESCAPE_NONE(DATE)).log --Where <jobname> is the name of the job.

Example: SQL2005 (SP1 & higher) & SQL2008

\abcd\Prod\SQLServersJobs\LOGS\$(ESCAPE_NONE(MACH))_$(ESCAPE_NONE(INST))\MyJob_Step1_$(ESCAPE_NONE(DATE)).log

 The above would be converted to: (see that default instance returns as MSSQLSERVER)

\abcd\Prod\SQLServersJobs\LOGS\A2MPRD151_LOGGING\MyJob_Step1_20091005.log

And that is all for now.

For more on tokens one can refer to the following web link:

http://msdn.microsoft.com/en-us/library/ms175575.aspx

SQL Server Fixed Server Roles

Have we ever wondered why does SQL Server provide many fixed server roles? What all“Server roles” are available for grabs? What of this each help us in? And why do we need these many?

Well the simple answer is ‘there are ‘N’ number of users accessing and viewing data from the database’ which makes ‘SECURITY’ a major concern area for any Database administrator.

There are different levels of ROLES defined in SQL Server - Server roles and Database roles.

For now let us consider ‘Server roles’ and understand more on the same. From SQL Server Management Studio – Under ‘Security’, and then under ‘Server Roles’ we have the following roles listed. Each of this role helps manage the permissions on a server. There are in total ‘NINE’ fixed server roles. These are system level server roles and cannot be changed at any point in time. Until SQL Server 2008 we did not have the flexibility of creating user defined server roles, SQL Server 2012 does provide the functionality of user defined server roles.

Each of the fixed server role and description is as explained below: (From MS Books online)

Fixed server-level role	Description
sysadmin	Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin	Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin	Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins. Security Note The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.
processadmin	Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin	Members of the setupadmin fixed server role can add and remove linked servers.
bulkadmin	Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin	The diskadmin fixed server role is used for managing disk files.
dbcreator	Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public	Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public. Note public is implemented differently than other roles. However, permissions can be granted, denied, or revoked from public.

For more reading – we can refer to Brian Kelley article on MSSQL Tips
ReferenceLink

Replication Architecture - Part 2

Let me get straight into the topic "Replication Architecture - Part 1"discussed last time; about the working of replication and then the entities involved in any replication model for that matter. We also did a understanding on publishing industry in comparison with replication which helped us in identifying the entities.

Following are the same:

• Publisher
• Distributor
• Agent
• Subscriber
• Articles
• Publications
• Subscriptions

So, let us get into each of this with respect to Replication.
A SQL server instance can be configured as Publisher or Distributor or subscriber.


Publisher: In simple terms, Publisher is the server on which the "Source" database resides. It is the main source for data and also identifies what data should be distributed across.

Distributor: gathers all the published data and holds the same until it sends it across to all the registered subscribers. It is also referred to as "Bridge" between publisher-subscriber and can support multiple publisher and subscriber concept.

Subscriber: Is the final destination on which data has to be transmitted.

Articles: This is the basic unit of replication. An article identifies a database object that is included in a publication. A publication can contain different types of articles, including tables, views, stored procedures, and other objects.

Publication: A publication is a collection of one or more articles from one database. The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.

Subscription: A subscription is a request for a copy of a publication to be delivered to a Subscriber. The subscription defines what publication will be received, where, and when. There are two types of subscriptions, push and pull.

Agent: An agent is a median which identifies the changes at publisher and transfers those changes to subscriber.

There are different agents in replication,

• Distribution Agent
  
• Log Reader Agent
  
• Merge Agent
  
• Queue Reader Agent
  
• Snapshot Agent

Replication Architecture - Part 1

Replication was something which I always wanted to explore... luckily had the opportunity of doing just that. To start off with anything that is new, we need to understand the "Architecture" and also working.

I have tried to put my understanding in words on the "Replication Architecture".

First of all, What is Replication?
In simple terms: Replication is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users.  This allows to make changes to local copy of the database and later synchronize the changes to the source database.

Next Question would be... Why do we opt for Replication?
Again, there can be many reasons for this; I have tried to capture few very valid reasons.
The primary concern for any organization is often the protection and availability of its data. Without reliable access to secure and relevant data, the smooth operation of a company comes to painful, unprofitable halt. Data should not only be secure, but also be accessible after a system or catastrophic failure. Replication - offers this accessibility.
In addition, Database replication can also supplement disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, applications can switch to the replicated copy of the data and continue operations.

Now, we understand What is Replication and Why do we opt for Replication... The next would be to understand "How does Replication work", "What are the common entities used in deciding the type of replication?", "What are the types of Replication available or rather provided by MS" and much more...

So, let's move on understanding "How does Replication work":

Microsoft SQL server uses publishing industry model to represent the components and processes in replication architecture. The working is also similar to how publishing industry works(we can think of "Daily Newspaper as an example).
Publishing industry publishes Magazines/Books; there are Distributors and Agents who carry these publications to the Subscribers. Subscribers of the magazine obtain copies of the publication and read the articles of interest to them; this is exactly how the SQL Server Replication model works.

We can identify the following Entities for the SQL Server replication model.

Publisher
Distributor
Agent
Subscriber
Articles
Publications
Subscriptions

We will start getting into each of the above listed entities in my next blog... till then enjoy reading and also get some kind of know-how on types of replication provided by MS SQL Server!