Wednesday, January 26, 2011

Rename .MDF File...

I have been working on a project where the database files had to be rename. The name of the files had to be consistent with the database. Initially I detached the database, renamed the files in Explorer, then tried to re-attach the database, I was getting an error. I came across the following steps (thanks to MSDN news groups) and implemented them, the renaming of the database worked well. This was done on SQL Server Version 2008 R2. In the example the db.mdf and db.ldf file will point to the newly named files.

Note: The following feature does not work in SQL Server 2000.

ALTER DATABASE databaseName SET OFFLINE

GO
ALTER DATABASE databaseNAme MODIFY FILE (NAME =db, FILENAME = 'C:\Program

Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf')

GO
--if changing log file name


ALTER DATABASE databaseNAme MODIFY FILE (NAME = db_log, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf')

GO
--Before you set the database online , you would have to manually change the filename at the OS level.

--Failing on this part will not let you to start up the database.


ALTER DATABASE databaseName SET ONLINE

GO

Friday, January 21, 2011

DTS Package on 64 bit SQL Server.

I was working on a server migration project and part of the migration was to move the DTS packages to the new SQL Server. Once the DTS packages were copied over, I started testing the DTS packages. The jobs which ran the DTS packages failed, the error was related to connecting to the source sql server from where the data was being copied. Did some research and found with the help of my colleague that DTS which is 32 bit is running on a 64 bit sqlserver. The DTS packages are 32 bit code and hence look for a 32 bit alias in order to resolve the server name. In order to solve the issue we had to create a 32 bit alias under SQL Native Configuration in the SQL Server Configuration Manager. There are 2 SQL Native Client Configuration available 32 bit and 64 bit, in order to solve the issue with DTS, i had to create 32 bit aliases. It was an interesting challenge  i ran into and it took me a while to get the hang of the issue, at the beginning i did not have 32 bit vs 64 bit issues in mind.

Thursday, January 13, 2011

SSIS - Access Mode...

I was working on a fairly simple SSIS package, as part of the package I had to move data from a SQL Server Database to another SQL Server Database. I used a Data Flow to do a simple copy of the data using OLE DB Source and Destination. When I executed the package, I found that the performance of the Data Copy was very slow. What was puzzling was that the number of rows to be copied were just around 1000 rows and the table was not very wide. Based on research from other blogs, there is a property called Access Mode in the OLE DB Destination Data Flow component. I Set the AccessMode to OpenRowset Using FastLoad and then re-ran the package. The data flow performed the copy of data a lot more faster and the package completed in a much quicker time.

Thursday, December 30, 2010

Welcoming 2011

The year 2010 has been interesting in a lot of ways, there were successes and failures. It has been a year where things are begining to recover after the recesion hit us. I sincerely hope the recovery momentum builds up in 2011. Lot of things are anticipated in the new year, will there be a paradigm shift in technology, it will be interesting to see how the events unfold. Some articles are predicting that smarphones will outsell the PC purchases (or has it already), there is a lot of talk about cloud computing, new releases of SQL Server are expected. Wishing every one a happy and prosperous New Year 2011.

Tuesday, November 23, 2010

Cloud based Tools-Microsoft...

One of the time consuming activities  for a DBA in the sql server world is to work with problems related to poor performance which are due to lack of proper configuration of a SQL Server.Usually DBA's have scripts/jobs/PowerShell scripts to detect performance related issues and fix them.Once a problem is detected, then one would deal with research of different KB articles/ use experience to fix the issues. As part of the SQL Server 2011 release, microsoft also released a cloud service which can be used to monitor different sql server's. Here is a link: explaining the basic premise of the Atlanta tool and the beta version is available for download.
http://www.microsoft.com/systemcenter/en/us/atlanta.aspx
Microsoft is embracing the cloud in a big way and making lot of investments in this area. Cloud is going to be an interesting area to watch out for in the years to come.

Monday, November 15, 2010

SSIS New Features...

With the PASS Summit winding down, there is a lot activity in the SQL Server blog sphere about the next SQL Server Release, SQL Server 2011 (Code Name: Denali). One of the areas of improvements which I have been following is the New Features in SSIS. There have been lot of wish lists request, one of the video's/demo which explain these very well is available at the following link: The content in this video is presented by Steve Swartz.
http://www.msteched.com/2010/Europe/BIN303.
The features are broken down into the following areas: User Interface/Designer Experience, Project Management (This explains features which enhance multiple package experience) and Administration.
In The Designer improvements some features explained are Undo and Redo, a new improved mapping editor. This allows one to export and import mappings, this is huge since now it allows the developers to store mapping s outside the SSIS packages. There is also an Zoom in/Zoom out feature which comes in handy while developing complex packages. As part of the Project management, there is a feature called Parameters being added to SSIS packages, communication betwen child and parent packages would improve with this feature.
As part of the Administration piece, a new SSIS server is being built out, this could be installed in the SQL Server instance. This SSIS Server is being built out with the DBA in mind, giving ability to monitor these packages in a more productive manner. In the Adminstration side, the deployment process has become more streamlined and allows one to set parameters and variables by pointing to differen environments. There is going to be a SSMS based SSIS package management feature and dependency analysis.

Saturday, November 13, 2010

SQL Server Denali...

Microsoft has released he CTP1 version of the next version of SQL Server Code named Denali. It is available for download, Quoting Softpedia.com,The first Community Technology Preview of SQL Server Codenamed "Denali" went live on the Microsoft Download Center earlier this week, as Ted Kummert revealed details of the next release of SQL Server at the opening keynote of PASS Summit 2010.
Quoting the http://www.theregister.co.uk/2010/11/09/denali_sql_server_cloud/ article,This version of SQL Server Denali introduces Juneau, what Microsoft is calling a "new-generation" Visual Studio development tool for SQL Server that will bridge the gap between building and deploying apps for on-site and on cloud. There has been a lot of momentum in the cloud space in the last few months. There are 3 major players at this point for the Cloud Initiative, they are Microsoft,Google and Amazon. Microsoft had released its cloud version of the SQL Server Database called SQL Azure close to a year ago and has been refining it ever since. SQL Azure details: http://msdn.microsoft.com/en-us/windowsazure/sqlazure/default.aspx