To cut the long story short, during a recent implementation of Cumulative Update 6 and 7 on a particular 'application domain' server we have had major application issues due to a mix up of application code & these SQL cumulative update fixes. Actually this server was not under our support structure that would definetly go through thorough testing cycles for any hotfix or service pack patch application, due to the road blocks and issues we were involved to provide recovery tasks.
I have highlighted and would like to stress that at any point of time it is always recommend that you test hotfixes before you deploy them in a production environment. Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Due to the application campaign previously the cumulative update package 6 wasn't applied to this SQL Server database, as per the list of hotfixes that are included in CU7 for SQL Server on the KBA 949095 information the application team were able to obtain the hotfix from CSS, that is another story. But the main problem occurred as they haven't tested the reporting part of the code that has caused this downtime to the application.
Being the reason of shared platform database server the meeting has included technical teams from Application, Database & Server based people and the discussion has brought up the suggestion of performing the 'Windows System Restore' on this Windows 2003 server. I would like to run through the basics behind 'system restore' concepts that were introduced from Windows XP time. Based on the home based work & tasks using Windows XP (with VIsta too) has that time machine facility such as when some awful thing gives your computer the staggers, it's easy to go back to the day before. Or the day before that. Or last week or last month. Few things to note for the users:
- System Restore creates points in time — called "restore points" — in which it takes a snapshot of Windows. It stores them on your hard drive. At any given time, you might have restore points going back a few weeks, or a few months.
-
System Restore only takes Windows and some programs backwards. This is important to know: It does not reverse any changes made in your documents.
-
The System Restore monitors the files that are important to the system's performance. These have such extensions as EXE, DLL and INI. When you select a date in the System Restore wizard, it restores these files to whatever they contained on that date. If you installed a program after that restore date, you might have to reinstall it.
-
Windows creates restore points under several circumstances. For instance, when you install something from a CD or floppy, it usually creates a restore point. It makes points every 24 hours. Or, if you don't use your computer that often, it creates restore points when you boot up.
-
Never forget the fact that such system restore chekpoints and tasks you need free space in hard drive (atleast 12%) of total disk size.
Coming back to the subject topic that suggestion of performing the 'Windows System Restore' on this Windows 2003 server is not possible directly unless you perform few tweaks to enable on Windows 2003 and I have totally opposed to this suggestion by System Administrators. As per the behaviour of System Restore which will not remove binaries from locations on your disk that Windows isn't deemed to "own" that is applicable to SQL Server binaries too such as shared applications folder. This will have major issue on applied SQL instance of causing unstable state or even corruption to system database where you will have major downtime to recover the complete server platform.
I have recommend(ed) and strongly propose to follow the process of using the CU uninstaller utility in Add/Remove Programs instead of trying to "roll back" method. Since the CU6 implementation and availability anyhow you have to approach CSS to obtain the hotfix, they will provide the steps to rollback/uninstall the Cumulative Update hotfix to your SQL instance. TO close this topic if you need to know the SQL Server 2005 builds that were released after SP2 for SQL Server 2005 refer to this KBA 937137 link.
Windows Server 2008 is on full fledged wave now after it has been launched in February and it is obvious that many questions will be flying around about supportability & manageability for existing SQL Server versions such as 2005 at the moment.
Think about managing the SQL Server 2005 Failover Clustering on the new Windows Server 2008, in this case you need to be sure about how to plan, implement and administer the SQL Server 2005 cluster. So to give more information to the users Microsoft Development team has lined up a webcast that discusses the above point in addition to the featuer such as how you can use to move the existing SQL Server 2005 failover cluster from Windows Server 2003 to 2008 version, not to mention about best practices.
This is a Level 300 session that will be presented by the host: Uttam Parui.
Uttam Parui has been working with Microsoft SQL Server for the past 10 years. He has been at Microsoft for more than 8 years and has worked with all versions of Microsoft SQL Server, starting with version 6.5. He is currently a senior premier field engineer working with major customers in the United States. His areas of expertise are high availability, scalability, and performance tuning. Uttam led the development and successfully completed the globally coordinated intellectual property for "SQL Server 2005 Failover Clustering" workshop. He is also a master trainer for Microsoft SQL Server 2000 and 2005 Performance Tuning and Optimization workshops. He is one of the technical editors for "Professional SQL Server 2005 Performance Tuning" book published by Wiley Publishing Inc. He has a master's degree in computer science and is a certified Microsoft Certified Systems Engineer (MCSE), Microsoft Certified Database Administrator (MCDBA), and Microsoft Certified Trainer (MCT).
To Attend this Live WebCast. click on the link and then after you can view using the same link.
Session Summary
Monday, June 09, 2008 10:00 AM Pacific Time
Applies to:
| • |
Microsoft SQL Server 2005 Developer Edition |
| • |
Microsoft SQL Server 2005 Enterprise Edition |
| • |
Microsoft SQL Server 2005 Standard Edition |
| • |
Windows Server 2008 Datacenter |
| • |
Windows Server 2008 Enterprise |
| • |
Windows Server 2008 Standard |
Here is the hot news, SQL Server Global Release Services (GRS) has revised their schedule of releasing Service Packs for SQL Server product by stating 'changed approach to Service Packs'. Now the big question how far you can stretch in deploying these service pack releases within your Enterprise database platform to deploy frequent service pack releases.
What has been referred on their blog is:
With our recent SP3 announcement we will be expanding the Incremental Servicing Model to now include Service Packs and we will pursue the following objectives:
· Smaller Service Packs which will be easier to deploy
· Higher quality of Service Pack releases due to reduced change introduced
· Predictable Service Pack scheduling to allow for better customer test scheduling and deployment planning.
If you are confused or not sure about Release concepts then refer to ISM-Concepts article. The four important factors for every release are:
· GDRs (General Distribution Releases)
· Hotfixes
· CU (Cumulative Update)
· Services Packs
Is it feasible for you to deploy the SP release within every 6 month period?
What is the current approach you have on Cumulative Update releases?
What is your high availability figure, can you achieve 99.999%?
Practically it is not possible to deploy the service pack release in every 6 months and moreover the practice should be to check whether that SP release is applicable to your environment or not by referring to the README.HTML file. Going further you can even test the pack by installing within your Test platform with a rigorous testing from the application to ensure it will not break out any layers of availability.
I have seen that release of service pack deployment is more wanted for the Customers that are satisfied with Service Pack release rather than Cumulative Updates, but think about availability and arranged downtime to apply these service packs. It may be good that you need not restart the server after applying, but to restart SQL Server services. Also it will be more feasible if Microsoft release team can provide with uninstall method with 'no strings' attached.
As a DBA you might be aware that since SQL Server 2005 database maintenace plans have given flexibility in deploying the workflow of tasks required to make sure that your database is optimized, is regularly backed up, and is free of inconsistencies in addition to the avoiding the fragmentation on indexes with optimization tasks.
This may be a good solution of smaller farm of databases deployment, but there is more to do with large-scale databases deployment such as adhoc mode of optimization and integration tasks to the selective databases, which is not possible to use with DB maintenance plans as you can create the core maintenance plan, but creating plans manually gives you to choose the number of databases or set of databases you have to take care. Again for the inclusion of database on selective purpose it may not be feasible to create 'n' number of plans that can confuse the way of managing plans in longer term. By default you have to be expert in SSIS as this maintenace plan internally uses Integration Services package, which is run by a SQL Server Agent job.
Its worth to mention that for the database BACKUP strategy (for all) you can easily deploy the maintenance plan route that will ensure to continue this important aspect of DBA responsibility, within my end I have no problem managing more than 1000+ databases via this process where I have differentiated the plans according the size of databases, such as database between 0 and 5gb will have 1 plan, between 5 and 15 gb databases would have seperate maintenance plan and so on. Also these plans will be running at different times in order to ensure they complete successfully and server is not hammered with this process at the same time due to the historic issues we have had on this database server.
During the recent times on the forums & newsgroups I have seen many people asking for a script or solution to perform the adhoc and selective process of integrity checks & optimization, as it is not that feasible to peforming with maintenance plan route. In this regard I have found an excellent blog entry from Ola Hallengren about performing SQL Server 2005 - Backup, Integrity Check and Index Optimization tasks with a script. I recommend to download and test his scripts in order to deploy flexible solution within your database environment.
You may be aware that or seen within your Database platform about slower disk performance than expect having multiple disks in Windows Server 2003 environment when you use a hardware-based redundant array of independent disks (RAID) or a software-based RAID. This could be due to one of the reasons such as starting location of partition is not aligned properly with a stripe unit boundary in the disk partition that is created on that RAID. Further information we can refer from one of the KBA929491 article that:
A volume cluster may be created over a stripe unit boundary instead of next to the stripe unit boundary. This is because Windows uses a factor of 512 bytes to create volume clusters. This behavior causes a misaligned partition. Two disk groups are accessed when a single volume cluster is updated on a misaligned partition.
Windows creates partitions that are based on a predefined number of sectors. The starting location for a disk partition in Windows Server 2003 is either the 32nd or the 64th sector, depending on the information that is presented to the operating system by the mass storage controller.
It is the default configuration from the disk partitions that they reserve the first sector for code and number of sectors with the starting sector information, thats how the next sector will consists the information about data part. In this regard Microsoft recommends to use DiskPart.Exe tool which can be used to create disk partition and specify the starting offset, before that it is recommended to findout what is the current size set of partitioned disks and you can get more information about the Diskpart.exe tool from this DiskPart-TechnetArticle and Exchange-Server_IO_Alignment article links.
Within the recent project tasks I have proposed to use disk partition as 64K blocks for the disks that are used for transaction logs, as there is no doubt it enhances the performance when writing the huge chunk of logs for a database (if required). Also I have discussed with Microsoft Development team about the recommendations they use internally with 64K for both data & log, further I suggest to refer Bob Dorr's explanation on how best system can make use of it during backup/restores link. One of my colleague (Ken) has raised the concern that "I can see that sometimes it would help but not always. My *guess* is that SQL won't always have 64k of data that needs to be written urgently (e.g. after a COMMIT). I guess it all depends on the algorithm's used and I've not seen them described in detail anywhere".
Further I have found that in the past when the Infrastructure team raised a query with Microsoft team it has been discourage to set 64K blocks to the drives other than Data disk. The usual disk layout seperation we follow for the SQL Server setup as follows:
- SQL binaries (System databases and software)
- Tempdb (and Tempdb logs)
- Application Database data files
- Application Database transaction log files
- Database Backups (system & user database)
Going forward I strongly suggest to use 64K blocks for Data, Log and backup drives in order to take maximum performance, to support this I would like to quote the text from this Pre-Deployment I/O Best Practices article as:
NTFS Allocation Unit Size
When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb. Be aware however, that using allocation unit sizes greater than 4 KB results in the inability to use NTFS compression on the volume. SQL Server, although it is not recommended that you use this, does support read-only data on compressed volumes.
Have you ever wondered about flushing databases?
Well not quite possible to flush it straight away and not a best practice too on the live server, so what it is about and you need to know about transactions state when they occur. Say when the changes occur in a database the changes are formed as transactions that are held in buffer pool until the transaction commits or aborts. If it goes for abort operation then obviously those changes are discarded and if not for commit process, its changes become visible to other users and transactions, but they might not be immediately written to the database. So in this case due to any issue on the server or database then even the transactions that have committed but whose changes have not been written to the database will be discarded, so this is where you need to consider having a thorough backup strategy for transaction log and WRITELOG operations.
Say to the extent of transactions that are written to database, in order. But still there may be some issues that can cause inconsistency on the database adn for instance which an application has committed transaction A, and then transaction B. If the application crashes or the device is reset, the database will be in one of three states, unchanged or changed by transaction from A or changed by both A and B! So always better to leave it to SQL Server to committ and write the order they are committed improves performance by reducing the number of times the database file must be written. Improved performance is particularly noticeable when there are many small transactions that have committed in a short time. In this case, all the transactions are written to the database file at the same time instead of each transaction causing a separate database write operation.
So more to the subject I have found this interesting blog post from PSS SQL Server Engineer's blog - CHeckpoint-flush-I/O by Bob Dorr, worth reading it.
Recently I have received an email about unable to drop schemas that are adopted in the database design within an environment, here it goes.
I'm setting up an automatic build process that also includes building one or more databases. Initially it was decided to used separate databases for various parts of the solution, but after a bit of discussion we landed on using schemas instead. However, that causes problems for the build process as drop schema does not drop all corresponding objects (like drop database does). I naively tried the following sql-statement, but it fails of course with 'Ad hoc updates to system catalogs are not allowed':
delete from sys.all_objects
where object_id in (
select
ao.object_id
from
sys.all_objects ao
inner join
sys.schemas s
on
ao.schema_id = s.schema_id
and
s.name = 'plonk'
)
It is obvious with the above error that within SQL Server 2005 you will not be able to modify the system catalogs like it allowed in previous versions, not directly but there is a way to modify as per this http://sqlserver-qa.net/blogs/tools/archive/2007/04/24/sql-server-2005-ad-hoc-updates-to-system-catalogs-are-not-allowed.aspx blog post here, but never attempt to do so which is a bad practice.
Coming to the subject discussion as per the design of schemas in SQL 2005 note that you must first drop the table that is contained by the schema. And also the quote within BOL that:
In SQL Server 2005 the behavior of schemas is changed from the behavior in earlier versions of SQL Server. Code that assumes that schemas are equivalent to database users may not return correct results. Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements has ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In a database in which any of these statements has ever been used, you must use the new catalog views. The new catalog views take into account the separation of principals and schemas that is introduced in SQL Server 2005.
So as it referred you have to check what kind of owners are associated within that schema using the TSQL below:
SELECT s.name SchemaName, d.name SchemaOwnerName FROM sys.schemas s INNER JOIN sys.database_principals d ON s.principal_id= d.principal_id
To drop the relevant schema either you have to alter the schema owner to any other PRINCIPAL or change the ownership of a object to another schema by using the following TSQL:
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [db_datawriter]
or
ALTER SCHEMA Target_schema_name TRANSFER object_name
Further you can go through the BOL code examples under ALTER SCHEMA statement. Also ensure that you have scripted the permission of that original object involved in that schema before transferring to new schema, because the permissions granted to the original object will be removed during the trasfer to authorisation.
No doubt that the blog subject will be a most wanted requirement on their databases, that is most responsible for a SQL Server database then arguably the most critical task that you absolutely MUST get right is your database backup procedures.
Where the backup sequence comes the database RECOVERY model comes as another important step, choosing best model is highlight for the transaction log too and this TransactionLog blog post refers best practices. To go through the history of transaction log importance we must refer through the FULL recovery model role, such as back up the Transaction Log regularly as well as the database. Obviously if you fail to do this the Transaction Log will grow and grow, that will cause disk free space issue on the drive where this log file is located. It is a general mis-conception of the users that when you choose SIMPLE recovery model it will take of the size, but its not! By default when you backup the Transaction Log it is 'truncated' at which point all completed transactions will be wiped from the log file and the space that they occupied will be made available for future transactions. The size of the Transaction Log is not changed by the backup process however, just the amount of 'free' space contained within it, shrinking the log size is different subject here.
So to cover up what you want to know changes happened within the database after the last good known 'successful' backup then our own SQL Storage Engine guru Paul Randal has given useful script to obtain information in a database to report the percentage of extents that have changed since last backup, refer to DatabaseChange-since-LastBackup post from his blog.
Ok here is the hot news for SQL 2005 users!!!
From the Are you ready for SP3 post here, it has been officially announced the SP3 for SQL Server 2005 will be available in CY2008 (calender year 2008) that means before 31st December 2008!
More from this DataInsider blog post.
Here is another candidate of 7 within Cumulative Update service for SQL Server 2005 SP2.
SQL Server Release Services team has announced (yesterday) that another instalment of Cumulative Update (series here) for SQL Server 2005 on Service Pack 2, it is not easy decision for User Community to apply with CU than going Service Pack release which is more stable as it proved from previous versions.
So the golden rule is always test this CU7 and to obtain the usual route (since CU5) was raising a online request (as referred on KBA949095):
..... it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. This cumulative update package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2005 service pack that contains the hotfixes in this cumulative update package.
To resolve this problem, submit a request to Microsoft Online Customer Services to obtain the cumulative update package. To submit an online request to obtain the cumulative update package, visit the following Microsoft Web site:
In addition to this if you need to know about previous CU releases refer to KBAs below (that was articulated on the above KBA):
CU#6 KB Article:
CU#5 KB Article:
CU#4 KB Article:
CU#3 KB Article:
CU#2 KB Article:
CU#1 KB Article:
CumulativeBlog subject posts.
Again the best advice is to refer the above KBAs and download it if you are affected by the speciified problem within those articles, though these are public announced one but not with available for free, overall it is best to test it before applying on live instance.
Do you have hundreds of SSIS packages to manage?
Do they run at various times to address the ETL solutions within your environment?
Let's say you have to modify the base table columns, so you need to consider what kind of impact this change would have affect on the various SSIS packages that are dependant within this base table. Have you ever seen the MetaData solution pack, that is one of the discovery tools you can use to explore the impact of a change like this.
Even I wasn't aware of this package until last month when I need to assess such a changes impact, and the approach that was taken in the SQL Server BI Metadata Samples Toolkit is phenominal as the solution can load up the package into memory as the runtime engine does and walk the package using the SSIS API. This solution pack was developed as a free set of utilities and samples called the SQL Server BI Metadata Samples Toolkit.
The impact analysis that provides will have the following files (that was referred in Technet article) that can be adopted based on your problem:
· DependencyAnalyzer.exe – Tool that evaluates and loads into a database the lineage on SSIS packages, Analysis Services and SQL Server. All the source code for this program is provided.
· DependencyViewer.exe – A tool that lets you graphically see the dependencies and lineage of objects in the lineage repository. Source code is provided for this program.
· Data Source View – A DSV that connects to the lineage repository (SSIS META database) that can be used by Reporting Services.
· Lineage Repository – A database called SSIS_META that can be used to house metadata from nearly any system.
· Reports – Some standard reports for impact analysis studies. You will find two key reports out of the box with several sub-reports.
· Report Model – A report model that you can use with Report Builder to allow end-users to create ad-hoc reports.
· Integration Services Samples – A few sample packages to start auditing and viewing lineage on.
The last one IS samples are very good to look at the audit solutions for your SSIS packages, with the help of report model can give further information on the assesment. For the sake of usage these files you have to create an empty database then you can populate by running the SSIS_META_creation.sql script that is provided along with this pack. Dependency Analyzer file can be used to scan the local default instance pakages by using as a scheduled job along with SQLAGent. To know more about the parameters for this pack you can run DependencyAnalyzer.exe /? to see all the additional switches you can pass to the program.
Further the DependencyViewer.exe executable ships with SQL Server 2005 BI Metadata Samples Toolkit to help you navigate through the dependencies. The tool helps you run a quick impact analysis graphically to determine what packages will be impacted by you making a change to a data source. In the DependencyViewer’s interface, click the Load button to connect to the local repository and browse through the tree. If you select a SQL Server table, you will see (blue colored) where in the package the table is used. The below example shows you some sample Anaylsis Services cube that ship with SQL Server and how the impact analysis would look on one of those data sources. If you select an object, the box turns blue and you can see the description of the object (if one exists) in the Object Properties window.
To download refer this "SQL Server 2005 Business Intelligence Metadata Samples Toolkit and to know more about this refer to this associated whitepaper, that will enable to test and make it possible to satisfy many of your metadata needs - simply and essentially for free!
Log or trace of activities will help you to get detailed information on what's going wrong, that too very useful for ETL based processes. In this regard within SQL Server 2005 using SSIS you can enable such logging within the package's runtime.
This will allow you to log the output to 5 providers, such as text file, SQL Profile trace, SQL Server table, XML file or a Windows Event log. Further you can take advantage of writing the same to 2 different providers, such as building custom logging provider. In this scenario the most common log provider to use is the SQL Server table provider, where you can simply integrate with your reporting system such as Reporting Services Reports that can be viewed or subscribed to see a package’s status. In this case you have to select this provider, you must select which Connection Manager you wish to write to and then the events you wish to log and based on your approach either you can log the warning or error or even all the package execution information, in this case make sure you have sufficient disk space availble where the log file is stored when you hundreds of SSIS packages to handle.
Internally the SQL engine retains this information under sysdtslog90 table that can easily grow large when you have such a multitude of packages, and its better to think about archiving the log information too for historic purpose. The reason being this table is full of operational data like when each task started and stopped and the success of each one. The most important column in the table is the message column that stores the verbose result of that step.
In this scenario the package logging can be enabled separately for each of the executables, such as tasks and containers, as well as the package itself, as you might expect, are components capable of generating and detecting events. Depending on your preferences, logging options are either shared or set independently, varying in the range of event types to be tracked or the level of details to record for each event type. The same applies to the format and destination of logs, which are determined by logging providers, assigned to each log. From the five specific providers mentioned above you can choose which one is suitable for you, more about them as follows:
-
SSIS log provider for Text files - the most straightforward option, redirecting entries to a comma-separated value (CSV) text file. In order to review them, you might want to consider importing the content of the file into an Excel (or other application capable of dealing with structured text files).
- SSIS log provider for SQL Profiler - stores logged entries in the format (as a .TRC file) that can be viewed in the SQL Server 2005 Profiler. This gives you the ability to correlate entries generated in response to monitored events with other types of data available via Profiler (such as stored procedure activities, performance or locking-related traces, etc.)
- SSIS log provider for SQL Server - loads event entries into a SQL Server database table called sysdtslog90 (with such columns as event, computer, operator, source, sourceid, executionid, starttime, endtime, datacode, databytes, and message, which match the elements of SSIS log schema), using the OLE DB connection settings (this connection gets associated with the provider at the time of its creation). This gives you the ability to extract logging information via T-SQL queries (for example, using Execute SQL Task).
- SSIS log provider for Windows Event Log - records event information in the Windows Application Log, which you can monitor (and configure to send alerts and take appropriate corrective actions) with a systems management software (such as Microsoft Operations Manager or any similar third party program capable of extracting this information). The entries point to SQLISPackage as their source and, depending on the content, might be labeled as Informational, Warning, or Error messages. In case the format and content of the event information created in this manner is not sufficient, you also have the option of redirecting SSIS log entries to the Windows Application Event log through a code invoked via a Script Task.
- SSIS log provider for XML files - dumps event entries into an XML-formatted file, which can be either viewed via an XML viewer of your choice, or displayed through a Web page. This approach also facilitates transferring relevant information to a third party system, which is not capable of working properly with the output from other logging providers.
Further read on from KBA How to interpret data that is logged by using a SQL Server 2005 & Custom Logging in SQL Server Integration Services Packages tip.
The subject say it all, as of today the mainstream support for SQL Server 2000 (SP4) and SQL Server 2005 (SP1) will be ended. That means for SQL Server 2005 service pack 1 there will not be any support and that can be obtained with a Custom Support agreement where few applications may have issues in upgrading to Service Pack 2 for SQL Server 2005, also the general suggestion is to upgrade to Service Pack 2 on SQL 2005 that has been stable since 1 year from its release!
Further going towards the major implementation on global basis,many enterprises out there are sitll functioning with SQL Server 2000 version (SP4 for discussion sake) and this clarifies a bit further that technical support continues till 2013 (hopefull until April 9th) and the mainstream support for any kind of hotfix or cumulative fix will be ending from today, the usual reply you would get from a Support team is to upgrade the SQL installation to SQL Server 2005 SP2 (atleast) and if you have any extended support agreement with CSS team.
This brings a big question how about Service Pack 2 for SQL Server 2005, the usual Product Lifecycle page on Microsoft confirms that such support ends either 12 or 24 months after the next service pack releases or at the end of the product's support lifecycle or whichever comes first. You can visit that page for more information on specified products.
Also related content from AndrewFryer's blog post.
Recently when I had a task to identify the learning materials (free) and web resources for SQL Server Integration Services (SSIS), then first place I have used is MVP private newsgroups. You will appreciate the immediate help I received from fellow MVPs in getting the list swiftly, they are as follows:
- Books Online (updated) - As the documentation will have much information about latest changes, such as from last hotfix or service pack.
- SQLIS website
- MSDN forums where you will get to see a good resource from the available threads/posts, rather than posting a question that might have answered there already. So it is better to have some patience in searching for required information.
- Jamie Thomson has developed a SSIS Live Search macros that will get you instant information and also this Online Beginner Resources link.
- Related articles in SQL-Server-Central website (for instance related ones):
- Brian Knight, MVP has given much insight on this SSIS-Video where you have to type SSIS to get relevant results.
I'm sure the above links should help the beginner to get a complete understanding about SSIS and DTS package migration within SQL Server.
Configuration of server such as disk and memory is an important factor for SQL Server Performance, there is all about questions within newsgroups and forums that how do I setup the disk layout for a SQL Server database (say, 2005 version). Not only this there will be more questions asked related to general guidelines or any other setup within the environment.
For the discussion sake let us assume the hardwar setup would be on SAN with the storage of 5 RAID 10 LUNs having seperate spindles, the usual setup would be D: drive for TEMPDB, E: for data files & F: for transaction log files. Further you should consider the CPU & Memory configuration, from those links. So going deep into the configuration of disk layous, important factor is size of the transaction logs such as setting them up with optimum values and not to leave with default AUTOGROW settings, and not to mention about RECOVERY model (FULL is recommended for point in time recovery). Not only that when it comes to the disks the backups are very important, such as the schedule and completion of tasks, having enough disk space for additional backups (if in case required).
Since the SQL Server 2005 started the TEMPDB will have priority in using any process on SQL Server, so in this case you must set a proper values and think about potential contention issues that are referred in this Technet WorkingWithTempdb article. Also the usual practice of creating multiple files for the user database is not required for the database sizes (nominal) near about 500 to 700 GB. This is applicable for the log files configuration too, do not create or think about multiple log files for performance, as they are not going help being the reason for sequential access and as usual better to keep the TEMPDB on the RAID 10 where you are going to put up the transaction log files. Not only to the disk configuration or server resources allocation you should refer to the other important tasks such as database maintenance practices and optimizing the database for better performance, refer to the links below:
SQL2005-IndexOptimization-BestPractices
Further to the above I have found the following links as 'useful' for the disk alignment purpose: http://seepia.dyndns.org/~pojanisu/diskalignment/diskalignment.html & http://seepia.dyndns.org/~pojanisu/tempdb/tempdb.html
Don't assume that setting up SQL Server on SAN or high resource server will be good enough to attain performance, the above factors are very important to keepup the performance.
