Powershell – How to rename and move files ?

Scenario:  Recently, I came across a situation where in we had a a folder called Job_logs which is used for an output for SQL server agent job logs. All the jobs that are scheduled by SQL agent like SSIS packages, TSQL scripts, etc have their output files written to this Job_logs folder (this is done for better logging).

Now the situation is that when the output file for SQL agent is set to append the log file, the output file which is a .txt file gets bigger in size in long run.

Now when the output file is big lets say more than 5000KB, it downgrades the performance as the output file has to be opened up and then written !

Approach: To overcome above situation, I thought of writing a handy script that will do 2 tasks:

1. Rename the file that is above 5000KB.
2. Move the renamed file to another folder called Archive.

  As we are on Windows Server 2008, the first thing came to my mind is using Powershell. It make life easier !

Now the question that come to mind is: “Is 5000 a magical number?” The answer is NO. Our servers are very heavy in terms of CPU (8 cores) and RAM (64GB). So after doing some testing, I came to the conclusion of going with 5000KB files.

Below is the Powershell script that I wrote to achieve what I wanted:

## set the file location where the job log files are
$file = "F:\temp\Job_logs"
## this will get the current date and format it and store in the variable
$ext = get-Date -format MMddyyyyhhmmss
## Loop through all the .txt files in the job_logs Folder
foreach ($file in gci $file -include *.txt -recurse)
{    ## using the length property of the file
     if ($file.Length -gt 5000KB ) 

   { ## will rename the file as file_datetime.txt
    rename-item -path $file -newname ($file.Name + "_$ext.txt")   
       }
};

$file = "F:\temp\Job_logs"
$archive = "F:\temp\archive\"
foreach ($file in gci $file -include *.txt -recurse)
{   
     if ($file.Length -gt 5000KB )
   { ## Move the files to the archive folder
    Move-Item -path $file.FullName -destination $archive 

  }
}

How to test whether your SQL server is available or not without installing anything?

Technorati Tags: Data Connection

Scenario: Recently, I was asked what if I want to test connectivity to my SQL server from a web server or from my local desktop machine ?

Approach: Well, this seems a much obvious question that can be answered on the spur as use PING <server IP> from the command prompt.

But PING just does the basic test and tells that whether your server is available or not. What if you want to see that your databases that your SQL server is hosting are available or not ?

There is a more smatter and easy way of doing this. The idea is to use UDL (Universal Data Link) files (i.e  .udl files).

Overview of UDL files:

UDL files are an easy, efficient and fast way to test connection to a server from your work station or from web server eliminating the need to install any application (e.g dtcping) or SSMS (SQL Server Management Studio). Also, its a good way to quickly test that the credentials you specified will connect to a server and to build a connection string.

How do I do this ?

1. Create an empty .txt file. Name it for example MyConnection.txt .
2. Now rename the extension and save it as MyConnection.udl .
3. Below is the image of the MyConnection.udl file.

4. Now double click the MyConnection.udl file to see the below screen.

5. Click the Provider tab and select SQL Native Client and then click Next.

6. Enter the data source: localhost or IP address of the server. Here I have used Windows authentication, but choosing the second option allows you to use SQL server authentication wherein you have to provide username and password. (Note: Here there is an option to save the password as blank or Allow saving password option and choosing this option will save the password as clear text on the disk which is a security risk !). Next you can choose to connect to the specific database by clicking the drop box and selecting the database name. Click test connection button to verify that the connection is successful. Below is what you will see:         

7. Press OK when you are done.

8. Now we can examine the contents of our MyConnection.udl file by renaming it back to MyConnection.txt

9. Below are the contents of the file:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=localhost

10. You can use the above connection string in your application code directly !

Hope this helps !

Thanks,

\\K

How to rename Distribution Job in an automated fashion ?

Technorati Tags: Replication

Situation: Recently I came across a situation wherein I have to rename the distribution jobs that are created when replication is recreated. The need to recreate replication is due to the fact that we do every 3 or 4th week staging refresh in our environment.

Our environment is on Windows server 2008 (64bit) with SQL server 2005 enterprise edition SP3 with Transactional replication involved.

Approach: We have a automated process called a Master Job (I will blog this in my future posts) that does the staging refresh job. This job is named as per what it does, meaning it drops replication, does restores and recreates replication with just one click ! Isn’t that cool !

But this job has some manual steps to rename the distribution jobs that are created when the replication is recreated. So to fully automate the job, I thought of automating some how to rename the distribution jobs.

First I would try to explain how the jobs are created when replication is created or recreated.

When transactional replication is created either using GUI (wizard) or recreated using scripts, the respective agent (snapshot, distribution and log reader) jobs are created with their default naming conventions. For example:

Snapshot Agent:
[PublisherServerName]-PublisherDB-PublisherDB- [# of time replication script/wizard run on this server]

Log reader Agent:
[PublisherServerName]-PublisherDB- [# of time replication script/wizard run on this server]

Distribution Agent:
[publisherserverName]-publisherDB-SubsriberServerName [# of times subscribers created run on this server]

[Ref: http://rdbmsexperts.com/Blogs/?p=40 . Thanks to Vinay for his help !]

This shows that there is no control of creating our own job names (for better management and maintenance).

There are two ways of renaming the distribution jobs:

1. On the distribution server, double click the respective job names and then rename them. Uh! that’s a manual way of doing and it can be tricky or prone to mistakes and being a DBA, its not smart way to do things !
2. The second way is creating a custom script as below:

In the distribution database, there is a system table called dbo.MSdistribution_agents that gives the info about the distribution agent with respect to sysjobs.

Below is the script that I used to rename the distribution jobs ( we have multiple publisher – subscribers.)

declare @jobID1 uniqueidentifier 
select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db='[pub_db_name]'  and
a.subscriber_db='[subscriber_db_name]' and
a.subscriber_id=s.server_id and
s.name='subscriber_server_name'

print @jobID1  -- this will print the jobID of the distribution job 

select job_id, name from msdb.dbo.sysjobs where job_id = @jobID1   -- this will give the jobID as well as job name of  the distribution job that we will be renaming

–-rename the distribution agent job that is created to new job name

exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’

To sum up above method:

-- Get the job ID for the distribution job created
declare @jobID1 uniqueidentifier

select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db='[pub_db_name]'  and
a.subscriber_db='[subscriber_db_name]' and
a.subscriber_id=s.server_id and
s.name='subscriber_server_name'

–-rename the distribution agent job that is created to new job name

exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’

In the same way we can rename the job for snapshot and logreader agent as well with MSlogreader_agents & MSsnapshot_agents tables respectively.

Hope this helps !

Thanks,

\\K

Strange issue with database mail in clustered environment - not working tried everything

Scenario: We have a 4 node clustered environment having windows server 2008 64bit with SQL server 2005 SP3 enterprise edition.

Problem: The issue was that out of 4 servers in a clustered environment, on only one server the database mail was not working.

Approach:

Step1: Check that the Database mail is enabled using Surface Area Configuration. Then I rebuilt the Profiles and Accounts within the SQL Database Mail. I sent test messages after the rebuild and didn't see anything logged. I used the troubleshoot option on the test message send page and found some stored procedures which allow you to check to see if the queue is active and to start it. I both confirmed it was active, and sent it another start command, but still don't see email getting sent. I did see that the DB Mail start command was logged in the event logs, but nothing other than that.

Below is what was done:

--The following query will show database mail is working correctly
SELECT * FROM msdb.dbo.sysmail_sentitems order by sent_date desc

No results are seen after running the above command.


SELECT * FROM msdb.dbo.sysmail_allitems order by sent_date desc

WHEN I RUN above COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "failed" send_status

--The following query will show Database mail is started and running
SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc

WHEN I RUN THIS COMMAND I CAN SEE DB MAIL IS STARTED, BUT I DON'T SEE ANY SUCCESSFULLY SENT MESSAGES LIKE I DO WHEN I RUN IT ON ANY OF THE OTHER DB SERVERS

--If Database mail was having problems, we would see failed items in this view.
SELECT * FROM msdb.dbo.sysmail_unsentitems

WHEN I RUN THIS COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "unsent" send_status

Then the FIREWALL and SMTP relay were working fine or not were checked and they were all working fine !!

Then I GOOGLED and found "http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/fd210efa-9c01-439e-8573-4f25a4177b08", but this did not work !!

Then the decision was taken to open a ticket with MS... spent 2.5 hours on the phone with Microsoft trying to figure out what's going on with DB Mail.

At this point they took some traces, other diagnostic data ... and yes they were able to figure the REAL problem ....

When you select MSDB and run the following to show DB Mail config:

------------------------------------------------------------------

USE msdb

select * from sysmail_configuration

------------------------------------------------------------------

...We can see that the following is set (which MS says would have been done manually as the default is to NOT use a config file):

------------------------------------------------------------------
ReadFromConfigurationFile 1 Send mail from mail server in configuration file
------------------------------------------------------------------

With this option enabled there MUST be a configuration file that tells the DB Mail exe what Server\Instance to send the mail to. This file existed (I don't know if it has existed from the beginning, who created it, etc...), but it was not in the correct location.

The file was in:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

...and needed to be in (MSSQL.3 since registry shows [server_name] being the 3rd instance):

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn

Once this file was moved into the proper location, we were able to define the correct server name:

------------------------------------------------------------------
Contents of config file: "DatabaseMail90.exe.config"
------------------------------------------------------------------

configuration
appSettings
add key="DatabaseServerName" value ="[server_name]" /
add key="DatabaseName" value ="msdb" /
/appSettings
/configuration

------------------------------------------------------------------
Once this had been properly configured and the file put into the correct location we were able to successfully send mail

Since this is a 4-node SQL Cluster we also had to copy the "DatabaseMail90.exe.config" file over to the other 3 nodes to:

\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn
\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn
\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn

...so that if this instance fails over to any other node it will continue to function.


This is how the Strange database mail issue was fixed ... Ah.. it was with the config file .... and lastly got resolved !!!

Terminal serrver has exceeded max allowed connections

When RDPing to remote server, this is a most common message displayed - " The terminal server has exceeded maximum allowed connections"

To overcome this message, following should be done.

start -> run -> mstsc /v ipOfServer /admin ===> for using RDP 6 client or above !

here ipOfServer = ip address of the server you are trying to connect.

instead you can use name of the server too.

Change password on remote server when using RDP

RDP (remote desktop) key combinations:

* CTRL+ALT+END: Open the Microsoft Windows NT Security dialog box (CTRL+ALT+DEL) --- to change the password on the server

* ALT+PAGE UP: Switch between programs from left to right (CTRL+PAGE UP)
* ALT+PAGE DOWN: Switch between programs from right to left (CTRL+PAGE DOWN)
* ALT+INSERT: Cycle through the programs in most recently used order (ALT+TAB)
* ALT+HOME: Display the Start menu (CTRL+ESC)
* CTRL+ALT+BREAK: Switch the client computer between a window and a full screen
* ALT+DELETE: Display the Windows menu
* CTRL+ALT+Minus sign (-): Place a snapshot of the entire client window area on the Terminal server clipboard and provide the same functionality as pressing ALT+PRINT SCREEN on a local computer (ALT+PRT SC)
* CTRL+ALT+Plus sign (+): Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer (PRT SC)

Moving databases between different servers when replication and full text catalog are defined !

I have come across many articles on web that describes how to move databases from one server to another. But I didn’t find much information pertaining to my situation.

Recently, we carried out a server consolidation project and I was required to move databases and cubes between different servers. Moving databases is fine and is pretty straight forward task (you can use Detach/Attach method). But when the databases you are moving are involved in replication or a Full text (FT) catalog is defined on a database, there things become tricky !

In my situation, transactional replication was involved and the databases were having FT catalog defined. So to move databases, I followed below mentioned steps:

1. Use the below query to find what files are involved with the database.

USE [database_name]

go

select name as logical_name, physical_name, type_desc, state_desc

from sys.database_files

This will give you the details of the files associated with a particular database. (Note: here you can use SP_HELPDB ‘db_name’; but this will not give you the location of FT file.)

2. Then script out the replication. (One of the cool feature of SQL server 2005 SSMS is the ability to script out things: rt click the publisher à generate scripts à select create scripts and give a proper location where the script file will be put).

3. Also, to remove replication generate Drop scripts.

4. Then you can use the Detach/Attach method of moving databases form one server to another.

--Detach the database.

USE master

Go

Sp_detach_db [database_name]

Go

At this point, Copy the mdf, ldf, ndf (if any) and the FT directory to the new server.

--Physically move the full-text catalog to the new location.

--Attach the database and specify the new location of the full-text catalog.

CREATE DATABASE [database_name] ON

(FILENAME = 'S:\data\[database_name]_Data.mdf'),

(FILENAME = 'X:\Logs\[database_name]_log.ldf'),

(FILENAME = 'S:\myFTCatalogs\AdvWksFtCat')

FOR ATTACH;

GO

5. After the attach is done, you can verify that the files that are attached are online or not by using:

USE [database_name]

go

select name as logical_name, physical_name, type_desc, state_desc

from sys.database_files

6. Then you can run the create replicaiton scripts to recreate replication. (Note, as you are moving databases to different server, the scripts are to be edited for the new location of the server!).

GOTCHAS!

1. If replication is not dropped, then detach database will fail.

2. While attaching the database, if the full text catalog path is not provided correctly, then the full backup will fail with following error:

"Backup failed for Server 'ComputerName\SQLInstanceName' " or "The backup of the file or filegroup 'sysft_FullTextCatalog' is not permitted because it is not online"

This can be resolved as follows:

ALTER DATABASE [database_name] SET OFFLINE

ALTER DATABASE [database_name] MODIFY FILE ( NAME = [logical_name_of_FTCatalog] , FILENAME = 'S:\FullText\catalog_folder_name')

ALTER DATABASE [database_name] SET ONLINE

This should bring the FT catalog ONLINE and you are good to take full backup.

If that does not bring the FTcatalog Online, then you have to rebuild the FT catalog or delete the old one and recreate from scratch.

Ref: http://support.microsoft.com/kb/923355; http://msdn.microsoft.com/en-us/library/ms345483.aspx; http://msdn.microsoft.com/en-us/library/ms174397.aspx; http://social.msdn.microsoft.com/forums/en-US/commserver2007/thread/307b9141-9063-4788-9673-e23b49fe6aeb

Hope this helps !

\\K