My Experience

SSIS package runs locally but not via Agent Job (Postgres Driver ODBC)

2026-04-30T06:37:00.000-07:00

I built an SSIS package to pull data from YellowBricks Cloud dataware house using an ODBC connection and load it into SQL Server. On my local machine? Flawless. The DSN is configured, credentials are stored, everything runs without a hitch.

Then I deploy it to the server… and it falls apart.

The job fails with an AcquireConnection error.

I double-checked everything:

Postgres Driver installed DSN , connecting.
Created the exact same System DSN on the server (both 32-bit and 64-bit)
Matched every detail—server, database, username, password, port
Verified DSN names are identical everywhere
Switched the Agent job between 32-bit and 64-bit runtime
Built and tested in both Visual Studio 2017 and 2022.
32-bit checked in SQL agent job.
Validate external metadata checked off.

Also, tried fetch and declare option from the DSN options as the Google recomendation still broken.

Permissions shouldn’t be the issue—the SQL Agent job runs under a service account with sa privileges. Also considerign other jobs running fine.

My thoughts "Everything looks right. Everything should work. But on the server? It justwon’t connect"

Finally found 32 bit driver have limitations. Yes data limitation that the reason 64 bit drivers were created to handle big load. My source above 4.5 million rows and that 32-bit couldn’t handle the load.

That was the creating the issue. And just like that, it could finally breathe knowing how adjust the SSIS package to over the limitation.

Simple Python Calculator

2022-07-19T06:50:00.001-07:00

My background is C - system programming. Switching to Python with so much libraries and functionalities make it a little over whelming. I wrote a simple calculator that rhythms with code we normally write with C.

First I wrote a simple calculator function, very straight you can practice by changing if to switch statement

import os

def Calculation(a, b, operation):

if(operation=='+'):

return(a+b);

if(operation=='-'):

return(a-b);

if(operation=='*'):

return(a*b);

if(operation=='/'):

return(a/b);

Below is the similar to the main program of 'C'.

while(1):

print('Welcome to Sam''s Calculator \n')

print('Press Q any time to exit the calcultaor \n')

a = input('Please enter the first input = ')

b = input('Please enter the second input = ')

op= input('Please select the operation to be performed = ')

if(a=='Q'or a=='q' or b=='Q' or b=='q' or op=='Q' or op=='q' ):

print('Thank you for using Sam''s Simple calcultaor. Calculator is now exiting \n')

break

a = int(a)

b = int(b)

result= Calculation(a,b, op)

print("Result of the operation ",op," = ",result)

Tensor Flow error : module 'tensorflow' has no attribute 'gfile'

2022-06-06T08:50:00.000-07:00

Background: This gfile is still present in tensorflow. This occur due to version change in Tensorflow.

  File "/Users/MRJ/anaconda3/envs/python37-tf2.1/lib/python3.7/
site-packages/object_detection/utils/label_map_util.py",
line 137, in load_labelmap

with tf.gfile.GFile(path, 'r') as fid:
  AttributeError: module 'tensorflow' has no attribute 'gfile'

Solution:

We need to update the file with the new key word tf.io.gfile.GFile where the error is occurring.

version 1.xx points to tf.gfile.GFile change for new version 2.xx is tf.io.gfile.GFile

Normally this is updated in config_util.py. The error will point which file(s) need to be updated. Once file is updated and saved. Shutdown and restart the jupyter notebook to take effect.

SSIS: export to csv flat file destination task: The code page on input column error 1252 and is required to be 65001

2022-06-01T13:58:00.002-07:00

exporting data to flat files. We sometime see error for code page 1252 and is required to be 65001 or it can be vice versa.

ADDITIONAL INFORMATION:

Error at export data to csv [Flat File Destination [187]]: The code page on input column "..." (958) is 1252 and is required to be 65001.

Issue:

Data output from sqlserver by default (This can be updated to other compatible formats) come in ANSI - Latin file format i.e. 1252. 65001 is value for the unicode file (i.e. UTF-8)

The error caused in code pages because 1252 (i.e. Windows-1252) is output from SQL Server and 65001 (i.e. UTF-8) that your CSV file is expecting.

Solution:

There are two solutions:

1) Select the file connection in SSIS --> Open the properties window and update the code page to the required value as below.

2) Update the SQL code to the unicode format i.e. convert / cast all fields to nvarchar

e.g. select CONVERT(nvhar(10),GETDATE()) DATE

No bootable medium found!” error in Virtual Box

2019-05-02T08:52:00.000-07:00

I was using Ubuntu on Virtual Box, I was constantly getting errors
"Fatal: No bootable medium found! System halted."

I used VM to install and test new software programs before I put those in my daily work horse. Long story short.

Change the Setting to below it should work

User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’

2019-05-01T09:20:00.001-07:00

User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’

This error message is raised when your user account does not have the db_owner role of a database meaning you are not the owner of the database; and you try to open the properties dialog box of a database in SSMS 2008.

User ‘<User>’ does not have permission to run DBCC showfilestats for database ‘<Database Name> ‘.

Cause:

This happens when you right click on a database in SSMS and click properties of the database; In response SQL Server 2008 runs the DBCC SHOWFILESTATS command to fetch the UsedSpace property and the AvailableSpace property. Because the user is not the db_owner, the user does not have permission to run the DBCC SHOWFILESTATS command. Therefore, the user cannot open the Properties dialog box in SSMS 2008.

Resolution:

Processing Cube Dimension Incrementally

2019-05-01T09:19:00.001-07:00

I didn't find much about how to process dimensions incrementally in a cube. To be clear, I am focusing on processing cube dimensions not dimension table. I have seen and work with different setting of the cube and work with "Process Update".

This process the data incrementally in the Cube dimensions.

If you using SSIS drag and drop analysis service item and set the setting as "Process Update".

sqlmanagment studio: cannot find one or more components. Please reinstall the application

2019-04-29T11:35:00.000-07:00

sqlmanagment studio: cannot find one or more components. Please reinstall the application

This can

(after installing vs2013)

SSMS Depends on Visual Studio 2010 IDE, which if not installed the SQL Server Setup will do, but if any version is present, the setup will ignore it, even at repair

Re Install, I figured that the SSMS needs Visual Studio 10 IDE to run, which I've removed by mistake after uninstalling VS 2010.

So, I Opened the Setup Media and searched for Visual Studio Setup. The .msi file run quitly and Filled the missing parts, and the Management Studio Run OK.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3dd6f643-720f-4878-9594-894da73efd46/sqlmanagment-studio-cannot-find-one-or-more-components-please-reinstall-the-application-after?referrer=http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3dd6f643-720f-4878-9594-894da73efd46/sqlmanagment-studio-cannot-find-one-or-more-components-please-reinstall-the-application-after?forum=sqlexpress

I found that if you do not have the CD you can get the VS 2010 IDE component that SSMS 2012 relies on here:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=1366

It's called Microsoft Visual Studio 2010 Shell (Isolated)

After installing that, SSMS runs for me again, Microsoft needs to add this install into their repair logic!

Remove duplicates from SQL Table

2019-04-29T10:01:00.001-07:00

Alot of time duplicates needed to be deleted.

Case scenario, When one reocrd is touched it can have same data with only different dates and may end up with duplicates when joining with Id

I normally use the below script to clean it off.

with cte as (
select
Id ,row_number()over(partition by Id order by Id ) rn
from dbo.Table
)

DELETE from cte where rn>1

The Size Specified for a Binding too Small (SSAS Cube)

2019-04-27T18:46:00.001-07:00

Getting an error when processing the Cube (SSAS) "Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated. Errors in the OLAP storage engine.” This error is due to the size of the Attribute exceeds the Data Size defined in the attribute.

I have seen this error comes at two places that is
1) when data size in physical fact table is changed.
2) when data size in physical dimension table is changed.

Resolution for

1) If dimensions are processing successfully only seeing the error when Cube itself is processed. Open the data source view right click and refresh. This will fix the cube. Now deploy the Cube and re-process.

2) If error comes when processing dimensions. This can be solved easily by two ways.

A) If it is simple Dimension, delete and create new dimension link the keys with Fact.
B) If the above is not an option then check data type of each attribute of the dimension, match this with physical dimension table. and update the data type in the dimension attribute.

After updating Data types re-process the dimensions.
Have a happy day.

System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file

2014-04-25T08:54:00.001-07:00

I develop a package in SSIS, I need to use format excel sheets and I am sure many of you will using it because of business people needing data in pretty form. Any ways, till this part when I run from SSIS it works like a champ. But as soon as I scheduled in SQL agent on server it started to fail with below error.

Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'xxxxxlsx'. There are several possible reasons: ? The file name or path does not exist. ? The file is being used by another program. ? The workbook you are trying to save has the same name as a currently open workbook. at Microsoft.Office.Interop.Excel.Workbooks

After sometime when I was about to give up because the solutions I was seeing on the web either say go play with DcomConfig or hack registry keys yeah like some one is gonna let me do that on prod server.

Finally found MS document that hints the below procedure it works and best part don't need to mess with registry keys.

Solution(1)

Create folder called "Desktop" really dont know why but it works.

Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop” (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop” (for 32 bit Windows)

In my case I had created folder on both locations and give read write permissions on it

P.S

You will still see this error even if you have created the folder but the permissions are not correct.

Cache InterSystems Database And SSIS

2013-08-24T13:47:00.001-07:00

This Post is important as I got an experienced with Cache Intersystems Database and SSIS. Most of you who don't know about Cache database, this is not relational database. This Database is based on classes and objects and it reside in memory.

CACHE DB CONNECTION :

Cache DB is connected with ODBC connection only. You will find ODBC connection in ADO.NET component.

In my Case Tables were not loading into the SSIS. And I had no clue what are the table names so I have to use SQL command. If you are also in this situation then you can use Access DB it uses an easy way to connect and load to ODBC and it can show you all the tables. Another way is to use SSMS and use link serves to get to the ODBC. I personally would prefer to use Access because of easiness.

Follow the screen shots and once you setup the connection for Cache database in SSIS, Right click the ADO component and select properties and Set the connection manager.

This is should fix the Cache and other ODBC connections to SSIS.

Database Emails

2013-08-18T22:14:00.001-07:00

The most incentive stuff a new DBA would like to implement first is always emails. Email are very important part of SQL as it is the efficient way for DBA to know if any anything goes wrong.

But using Emails efficiently is another problem I like to share one story let give him a fake name ABC since I don't want to offend the guy lol:) . Now he planned his email in this way that he would get email of each and everything going on example he received email for each back up , differential backup and transition log. I was surprised when he complaint that his emails are too much for him. As I advised him if he can follow a reverse order example if something goes down then you received email. that going to surely reduce the frequency of sql mails.

Ok let start what to do when Database mails are not working.

First I am not explaining how to start the Database wizard I hope you already know.
What I am trying to focus is when you start thinking why your email is not working I identified those places.

First thing to check, Profile make sure if you are using the correct profile.
Second, Check the email address, Server and Port number.
I always keep it simple espcially when I know I am in secure intranet I don't use SSL certificate but you can optional.
And one Important part if your emails are hosted that mean some third party is taking care fro Microsoft exchange then you need to provide the credentials no exception but again if you are in intranet and consider you intranet to be safe keep it anonymous.

Now let say you already check above nothing happened.
Go and check you Anti-Virus exclude MSSQL and check port number if its block or unavailable.
You can check port by telnet or ping use command line.

MIrror Suspend Error 1583

2013-02-04T20:44:00.002-08:00

when I do resume the mirror it trys to synchronize but fails and suspends itself after around 30 seconds always with the same size of unrestored log - does this mean there is a bad part of the log?

And you dont want to break the mirror because if it happen then you need full backup of the live database and restore it to the mirror.

Sometimes suddenly we see that Mirror is suspended. This is a common problem, and this error normally comes up from the when either the Data Disk or Log Disk is getting full. So Clean the Disk and Let the Mirror Continue Easily. Remember to resume the Mirror by going Task->Mirror-> Resume.

And always start trouble shooting by looking at the logs of the SQL rather than looking at google first :)

Operating System error 5

2013-01-08T20:45:00.001-08:00

Operating System error 5, very common error normally this error is raised because user has right issue on the folders or been restricted by operating system. So contact your Network Admin and Settle all the rights issue and everything will be good to go.

The server principal " " is not able to access the database " " under the current security context. (Microsoft SQL Server, Error: 916)

2012-12-07T14:06:00.001-08:00

The server principal " " is not able to access the database " " under the current security context. (Microsoft SQL Server, Error: 916)

This is the solution type of solution I found on the websites. Question is does it work, I dont't know because it didnt work in my condition so I really have no idea.

To resolve this issue:

1. After starting Management Studio 2008, select Databases in the Object Explorer once.

2. If the Object Explorer Details are not already showing, select View from the toolbar then select Object Explorer Details.

3. Right click on any column header and make sure that “collation” is not checked.

4. Refresh the database list by clicking the Refresh button located at the top of the Object Explorer Details panel. You should have now have full access to your database at this point.

Enjoy Have Bless day with MS SQL.

Could not Obtain Exclusive Lock On Database

2012-12-07T06:54:00.001-08:00

Some time this error appears and First things comes in our mind is What has possibly gone wrong, I normally see this error when I am restoring the databases or overwrite a a dump database.

Since SQL is also a software and like every software it has its own way of operating and connecting with other entities. Let solve this issue, what steps will take care of this error.

1) If the above error appears, close your Studio Management Console. Because sometimes its Studio Management that is holding a old connection. First and normally it fixes the problem.

2) Second reason may come because of "Auto Close" property if turn "ON", Go in DB properties and turn off this property.

3) Check if any other window is open holding a connection to the specified DB.

Use below script to turn "AUTO Close" off

SELECT DATABASEPROPERTYEX('Your DB' , 'IsAutoClose' ) AS [AutoClose]

ALTER DATABASE [Your DB] SET AUTO_CLOSE OFF

Some Daily Database Tips

2012-12-06T21:03:00.002-08:00

Some Useful information for daily life DBA, Well I writing this post just to include things we as DBA need in everyday life you can find more information on other blogs and site this is something for information for new DBA guys. There are couple of commands that can help us. Let looks us at commands. and Why we use them.

1) sp_who

Provides information about current users, sessions, and processes.

      sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

          ACTIVE excludes sessions that are waiting for the next command from the user.

2) sp_who2

Shows all the sessions that are currently established in the database.
sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of sp_who2 only show the row or rows of the executing SPID.

3) Kill

Terminates a user process that is based on the session ID or unit of work (UOW). If the specified session ID or UOW has a lot of work to undo, the KILL statement may take some time to complete.

4) Sys

Sys is special system table as understandably by its name. What is purpose of Sys? Sys provide various functionalities like all table names in a database, schema level information , object and their information, etc. In short Sys is key player for for many system and database which you can find over here. Lets looks at some below.

5) Sys.Table

Returns a row for each table object, currently only with sys.objects.type = U.

6) Sys.Objects

Contains a row for each user-defined, schema-scoped object that is created within a database.

7) Information_schema

Information schema is another SQL built in table, it has schema level information.

An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

WAMP Server Not Starting

2012-10-16T20:19:00.000-07:00

For more than 3 hours I would like to put my head in the wall because WAMP server simply refuse to get online and I had no idea hat going on. So here comes action open google search and found out so many answers and guess nothing worked. I need some thing that can solve the issue I re-install the WAMP disable my IIS but no progress Finally I managed to make the WAMP work. And I will List you the steps and the some tips if that will help you. I am sure that will help and yeah we'll track down that rascal stopping WAMP to go online :)

First Thing,

1) WampServer > Apache > Service >Test Port 80. This give me the following message:

Some Message will pop up like this and show you which app is using port 80
------------- Your port 80 is actually used by :
Server: Microsoft-IIS/7.5 -------------
2) Open the Application close it and make sure when they start they start after WAMP.

3) Make Sure SSRS(MS SQL Reporting ) is shutdown if you are using WAMP .

4) Disbale IIS if nothing works because IIS and WAMP are sometime conflicting with each other

UPDATE : 02/06/2013
Ok I am writing an update for the same post because this time WAMP server again give me a pain in the @$$. But this Time nothing stated above worked. So I decided to trouble shoot more because I always believe one or other way these things will work. So Get Ready If the above steps fail in your situation also.

5) One thing is clear and must be remember problem is not your computer, its the software battle going inside the PC makes conflict and the better one win. So Remembering this strategy. I Focus my attention to Web products.

6) As I was scrolling down the Services, I found web deployment agent, And I disable that Guess My WAMP started to work like a charm. So Consider making it disable if WAMP is having problems.

P.S. If WAMP still not start then Take down Web services and and restart wamp again and again. One time you will see it must start all cost.

Take Care Guy if I found more about WAMP I will update over here

UPDATE : 08/16/2013
All of the above did'nt work so I finally banged my head into the wall. Nothing above work.
Before I finally decided to move to Linux I gave last shot and open the services, Promise to my self I'll close all services one by one it should work something is holding it.

1) I close SQL services
2) World Web publishing
And Guess it worked.

Execution cannot continue as the language dependent resource file

2012-07-19T13:52:00.000-07:00

Arrrrrrr!! Yes that the first reaction came from my junior DBA. And I think it necessary as he is learning. Ok what to do now. Solution is straight and simple.

As you receive this error you will be prompted for location where the SQL cannot find the required file.
Download those files easily available put in the folder specified and you are good to go.

Is nt this simple and straight? So Join our mailing for small but effective techniques.

DTS Packages in SQL 2005/2008

2012-07-19T13:49:00.001-07:00

I was given a task to import DTS packages in SQL 2008 which were imported successfully done. However when my team tried to edit them, they would not open. --The following error was displayed: 'SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. Microsoft.SqlServer.DtsObjectExplorerUI) .

This is so frustrating DTS package is imported and not opening I searched the web and didnt find much except people just trying to give their advice's for NONE that worked for me.

But afterwards I found an awesome article that can actually help many of the developer looking to import and convert old DTS into SSIS packages. But it didnt work well so I need to fix more things but that didn't mean the page was crap no it was good and gave alot of information. My blogger is having issue not uploading the link so pasting the information.

1) Install Sql Server 2000 DTS Designer Components using the following link

http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en

2) Install Sql Server 2005 backward compatibility components available with Microsoft SQL Server 2008 Feature Pack using the following link depending (x86,x64 and IA64)

http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

3) We need to change the Environment Variable for the Server so that c:\Program files(x86)\Microsoft Sql Server\80\Tools\Binn is ahead and all the sql 2008 paths comes at the end and we can verify it by opening the command prompt and firing the command “path”

C:\>path

PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\PROGRA~1\CA\SHARED~1\SCANEN~1;C:\Program Files\CA\eTrust Antivirus;C:\Program Files (x86)\Windows Imaging\;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\

If you are not able to change the path Environment variable you can follow the following steps

Manually Copy the following files

%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll

To the following destination respectively

%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlsvc.rll

To make the DTS designer work under BIDS for SQL Server 2008, user will need to manually copy the following files

To the following destination respectively

%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\semsfc.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlsvc.rll

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

4) Run the Repair for the sql server 2005 backward compatibility from Control Panel, Add/Remove Programs, Change
And now you should be able to open the DTS packages under SSMS.

For Trouble shooting if you have further problems Click this post

T SQL implementation of Database Mirroring

2012-07-17T22:02:00.000-07:00

So how to implement T SQL for Database implementation.

Only writing the code will not get you easily understand the concept lying between the using SSMS and TSQL. So let us write and analyze the code step by step.
For this I will recommend first to use SSMS and note the steps.
First we need to take full back up and transaction log of principal and restore to mirror. For this post
Please click here.

First we need to configure Mirroring End points. These End points are more like socket where we want our server to listen to this specific port in concise words.

1) /****** Object: Endpoint [Mirroring******/

CREATE ENDPOINT [Mirroring]

AUTHORIZATION [ALITEST\sqlmirror]

STATE=STARTED

AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = REQUIRED ALGORITHM RC4)

Now Let us see what I have written,

First line indicate creation of new end point name mirroing.

second line indicate user authorization to use this end point.

third line indicate server is allow to listen this end point.

Fourth line indicate parameters for the end point more like configuration.

Fifth line we indicating this end is for mirroing and role can be set to partner, witness but in this case I set all.

Same way create end points for all the server i.e. mirror and witness also

2) /****** Object: Endpoint [Mirroring******/

CREATE ENDPOINT [Mirroring]

AUTHORIZATION [ALITEST\sqlmirror]

STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = REQUIRED ALGORITHM RC4)

3) /****** Object: Endpoint [Mirroring] ******/

CREATE ENDPOINT [Mirroring]

AUTHORIZATION [ALITEST\sqlmirror]

STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = REQUIRED ALGORITHM RC4)

Now one Last step as noted in the SSMS.

This step only tells the servers who is who i.e. who is principal who is mirror which one is witness.

Setting Principal to be mirror partner

*** Run this on principal first.

Alter Database <DB name> set partner='TCP:\\Servername'

Setting mirror to be Principal partner

*** Run this on mirror .

Alter Database <DB name> set partner='TCP:\\Servername'

Setting principal to be witness partner

*** Run this on principal .

Alter Database <DB name> set witness ='TCP:\\Servername'

And if all goes well You have successively started mirroring via TSQL

P.S Run all the script separately on respective servers or this will fail.

Implementation of Mirror Database Using SSMS

2012-07-16T20:38:00.000-07:00

Implementation of mirror database consist of special set of step that enables the SQL to perform mirroring.
In this post I would be discussing the Mirror implementation from SSMS which is easy and in our next post we discuss about T SQL implementation.

Steps:

1. Take full full back up of principal database using right click -)Task-)backup-) full
2.Take transaction log back up of principal database using right click -)Task-)backup-) transaction log
3. Open Mirror Server right click Mirror database-)Task-)restore-) full
4. Open Mirror Server right click Mirror database-)Task-)restore-) transaction logs

5.Open Principal server go Task-) Mirror -) configure-)Set the principal-)Set the mirror-)Set the witness (if you want automatic fail over to mirror database in case principal database die)

And yes above Five steps are enough to start your mirroring using SSMS.

In our post we discuss about T -SQL implementation of mirroring

High Availability Solution in SQL

2012-07-15T11:51:00.000-07:00

Many times a novice DBA thinks what to do when his seniors and manager just make him struck with high availability solutions and he is given the task for ultimate solution design . This post normally deals with my situation as I join the company soon three experience DBA left and I was one of replacements for the expert DBA but having no experience is another blessing :) You learn a lot and feel the responsibility thinking no one is gonna pamper you. My manger told me to work on a high availability solution which was not available in our enviroment. My manger and other collegues were more of infrastructure guy so I start searching a good solution.

So, following are the solutions pretty common all over the web. But million dollar question which solution fit your environment the answer to this question belong to only one group or one person that may be you or that may be the one dealing with database him/herself.

I am not going in detail of what they are. There is plenty of Microsoft online documentation for that I shall be giving a briefly going over about these and then taking scenario's in which I used these technologies for better explanation.

High Availability Solutions for SQL
1) Mirroring
2)Clustering

P.S. Please click the Mirror link to go and access the information

Database Mirroring

2012-07-14T17:17:00.002-07:00

Database Mirroring

Mirroring was intorduce in 2005 by Microsoft. It is a low cost low maintenance solution when I say low maintenance it only means it has degree less complex than the cluster. In Mirroring, Principal database is duplicated exactly to a Mirror which is clear from its name.

Let consider pros and cons for which some people like mirroring and some don't.

Pros:
1. It easy to implement.
2. It can ensure no data loss (if using High Safety mode)
3.It provides a secure copy of database separate from the production so if production goes down Mirror database kicks in immediately without using time for manual restoration.
4.it provide three modes of operations. Asynchronous , Synchronous without witness ,Synchronous with witness.

Cons:
Most people think Mirror Server is actually a waste of resource. Reason because it waiting for principal server to die malfunction or gone for maintenance and then it can start. It something more then standby server and database.

Asynchronous : Principal database commit data without waiting for mirror database to commit. Data loss chance present.

Synchronous without witness : Principal database does not commit data before the mirror database. Therefore principal database waits for mirror database to commit first. No Data loss. Manual fail over to Mirror Database

Synchronous with witness:Principal database does not commit data before the mirror database. Therefore principal database waits for mirror database to commit first. No Data loss. Automatic fail over to Mirror Database.

Implementation of mirror Server will be discussed in the next and above will be linked with the post.