Blog Archives
SQL Features Discovery Report
While going through my preparation for the MCSA exam I stumbled upon this gem that I always seem to forget about so I thought I would pass it along to you. For a quick an easy way to determine what is installed on an instance of SQL Server, run the SQL Server Installation Center and select tools. Then select Installed SQL Server features discovery report, as shown in Figure 1. This will produce a nice report as shown in Figure 2.
Enjoy!
Figure 1 – SQL Server Installation Center
Figure 2 – Setup Discovery Report
Moving SSRS Reports
Yesterday I was tasked with migrating our Citrix EdgeSight database off of a 2005 SQL Server and migrating it to a new 2008R2 Server. No problem, I have done that a million times before. But wait a minute, that database has SSRS reports installed on that server and not our main reporting server. If you follow my blog, you will probably see that I have not had much experience with SSRS, my reporting experience has been with Crystal and other minor packages.
I started researching how to migrate these reports and was not pleased to see Citrix’s response. They recommended migrating the ReportServer and ReportServerTempDB databases to the new server and then reinstalling SSRS on the original server. So what happens then if you have other jobs on the new and old servers? This is not a good recommendation. After an exhaustive search the only option I saw within the tool was to download reports one at a time and upload them into the new server. This was also not an option with a fair amount of reports to migrate.
Enter Reporting Services Scripter from SQLdbatips.com. Run the program on the original server and select your Report folder and scripts are generated and placed into a folder of your choosing. These scripts can then be copied over to the new server and executed by command shell. In this particular instance we had to reopen the data source and enter new credentials. Problem solved, reports migrated successfully.
Enjoy!
Corruption and Verifying Backups
This morning I had a conversation with a Subject Matter Expert and Application Administrator who asked me quite simply for a verified backup before he does an install on Saturday. My response was that I could test it manually for them and I was willing to do so but currently with our infrastructure the nightly backups are not tested without manual intervention. I have tried to change this policy for some time and unfortunately it may not occur until something bad happens. With this in mind, I do the best I can to minimize the risk given that I cannot automate a solution to restore databases on a regular basis as I would prefer. How can you minimize the risk?
- Use the WITH CHECKSUM option on your backup scripts. Read Paul Randall’s The Importance of Validating Backups article on SQL Server Central for more information.
- Use the RESTORE VERIFYONLY command (although we use Ola Hallengren’s Backup Solution which is the verify option which runs the command).
- Use the WITH CHECKSUM on the RESTORE command to recheck the page checksums in the backup as well as the backup file.
- Automate or manually restore your databases from scratch on a regular basis. (This is where we are lacking and where I want to take my servers).
- Once you have restored, then run DBCC CHECKDB against the restored database.
If you are not doing all five then you cannot say confidently that your backups are verified. However, if you are doing all five keep in mind that there is no fool proof guarantee against corruption, this merely minimizes the destruction by having viable backups. I hope this helps….Enjoy!
Nifty Code to Update Collation
Today, we interrupt our countdown series in progress to bring you some actual T-SQL coding. I had to bring out this snippet and dust it off for my purposes, to change some column-level collation in a database. To give you some background, we have a vendor product that requires case-insensitive collation at the server and table level, but case-sensitive on the column level. Yes, I know that is not best practices and seems like a weird design choice but I have to support it as the DBA.
Normally this would require a script from the vendor because it is their code, but the product allows you to create some custom tables (they default to the table level collation) in the database and those are our responsibility. So why would you need to change it you say? The developers were trying to write some reports against these tables and as you would expect they received an error (Msg 468, Level 16, State 9, Line 4 Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.) It would take forever to go table by table and column by column to change the collation, hence the need for the script!
DISCLAIMER: I have had this little snippet for a while and I do not know who wrote it originally, probably found on MSDN. I also know that it is not the most updated way of doing this (you should use the sys schema) but it works and sometimes that is easier than rewriting the code.
The first thing you should do is right-click on your query window in SSMS and set your results to text so that the script outputs you a new script that you will run in another query window. Keep in mind with some of your tables you might have to drop and recreate the indexes in order to alter the collation. Always take a backup before changing something like this and know what you are doing and changing before executing any script.
SELECT ‘ALTER TABLE ‘ + syso.name + ‘ ALTER COLUMN [‘ + sysc.name + ‘]’,
syst.name + ‘ (‘ ,
sysc.length ,
‘) COLLATE SQL_Latin1_General_CP1_CS_AS’ ,
CASE sysc.ISNULLABLE
WHEN ‘1’ THEN ‘NULL’
ELSE ‘NOT NULL’
END + CHAR(13) + CHAR(10) + ‘GO’
FROM sysobjects syso ,
syscolumns sysc ,
systypes syst
WHERE syso.id = sysc.id
AND syst.xtype = sysc.xtype
AND sysc.collation = ‘SQL_Latin1_General_CP1_CI_AS’
AND syso.type = ‘U’
AND syst.name != ‘text’
AND syso.name NOT IN ( ‘SYSDIAGRAMS’ )
Enjoy and I hope this helps you!
I Want My MTV, I Mean SQL Server 2012!
I would love to upgrade my servers to SQL Server 2012. We’ve met with Microsoft and we discussed the licensing ramifications and I got really excited about upgrading after getting the OK internally. That was a couple of months ago. The one piece holding us up? Vendors!
We do have some SQL databases developed in-house, but being predominantly an Oracle shop, most of our SQL Server databases are vendor provided. We have set forth the initiative to do all new development solely in SQL Server for cost savings. Additionally, we have begun moving some of the vendor provided databases to SQL Server. However, those are also at the mercy of vendors. My latest project only supports SQL Server 2008, not even R2. This is very frustrating from an enterprise DBA standpoint. I want to move forward! Enjoy!
Removing a Perfectly Good Cluster, Part Two
Yesterday I started a new project to downgrade our two new SQL Server 2008 R2 clusters down to SQL Server 2008 clusters. The uninstall went off without a hitch as we removed both nodes and then removed the support tools. I find it interesting that when we went to install the 2008 server, there was still tempDB data files which prevented the new install from moving forward until we deleted them. I am not sure if the other system databases were there as the installer did not complain about those. In hindsight, I probably should have removed all of the directories and files before installing as a general best practice but I did reboot the server prior to the new install and thought it would be fine.
Because I do not spin up new clusters everyday (that would be a great job), I took screen shots during the initial 2008 R2 install to serve as a guide because I knew that I had a total of three clusters to build by the end of the year. In this scenario, documentation is an amazing thing (well it is amazing in most areas but most DBAs become complacent about doing it myself included). Originally I built the first two clusters back in January and since they were the first clusters that I had ever built I wanted to document it as I am responsible for many systems and quite honestly I would not have remembered the settings chosen on each screen. Having worked with SQL Server for sometime, I could have configured a stand-alone server in my sleep, but I was not as confident with clusters. My confidence is building at this point.
Now, it appears that building 2008 SP3 servers is almost identical to 2008 R2 servers from slipstreaming through the configuration for best practices. In a future post, I will discuss some best practice troubleshooting I did for these reinstalls. Enjoy!
Removing a Perfectly Good Cluster, Step One
Today we embark on a somewhat sad journey. A couple of days ago, I told you a story about a vendor and the miscommunication of specifications. Today the chickens have come home to roost. We must replace the SQL Server 2008R2 clusters with 2008 clusters. These were perfectly good clusters, fine tuned and ready to burst out of the starting gate and win the triple crown, well you understand what I mean. I’ve grown attached to these clusters as they were the first ones that I have ever personally built from the purchase order to production.
Today, we will uninstall both of the SQL Server nodes and then begin the reinstallation of the previous version. We will run the installer on the passive node first. In the installer go to the maintenance section and choose “Remove node from a SQL Server failover cluster.” After this is complete then we will go to the active node and go through the same process. Tomorrow we will look at the reinstallation of SQL Server 2008 SP3. Enjoy!
Vendor Rant, Starting Over
We interrupt this regularly scheduled blog series to bring you a rant. Now wait don’t click away just yet. There is a lesson here somewhere, I hope.
To give you some background, we have a vendor, henceforth known as XYZ, to whom we have worked with for several years on their financial package. The package is currently in Oracle and it was decided that we could reduce costs by upgrading to the newest version of their package but migrating it to SQL Server to reduce our Oracle licenses which makes sound financial sense. Being a predominantly Oracle shop, I have been masterminding the demise of Oracle for the year that I have been here quietly chipping away.
This project has been in the planning stages for several months. During which time we order four identical super servers to be clustered into a production and development/acceptance active-passive clusters. I cannot deny that I indeed was excited about this project whole heartedly because of the hardware as well as the chance to reduce the Oracle footprint and to champion SQL Server as the preferred database. Plus, I have never built clusters from the ground up.
We took our time setting this servers up with Windows 2008 R2 Enterprise making sure that everything was well tuned. Then we setup our SQL Server 2008 R2 Enterprise clusters on all four boxes even bringing in our Microsoft Premier Field Engineer to ensure a successful migration ensuring that best practices were in effect. Most would view bringing in help as an insult to their pride, but I welcomed the learning opportunity and it helped with the learning of our green junior DBA who has no server or SQL experience. In addition, whenever I can be around our PFE, I am the eager padawan and she is the jedi master especially since she has an extensive Oracle background as well.
Fast forward and these machines are ready to go and all of the specifications were discussed and communicated several times through planning meetings. I even spoke with their DBAs during the install process to ensure that our settings were commensurate with the project. Now on Thursday of this week, the day before we are to begin migrating some of the Oracle data to the development box, I discover on one of the documents that the only version supported is SQL Server 2008 SP3 running on Windows 2008 R2 Enterprise. Hold the phone!
Rewind, notice I said we communicated several times that we were going to install SQL Server 2008 R2 Enterprise clusters on our new boxes and the vendor was compliant offering assistance if we needed it. THEY NEVER MENTIONED THAT THEY DID NOT SUPPORT R2. Now we have to uninstall R2 and install plain jane 2008 in effect putting us two versions behind and this project does not go live for another year after extensive testing. We even offered to be a beta testing site so that they could certify and say that they supported R2 since we have a year of testing ahead of us. DENIED! They were not interested whatsoever since none of the third-party tools such as BOXI support R2, according to them. Now I have to uninstall my beautiful creations and go backwards, this is progress. 😦
The moral of the story is to get the vendor to verify and sign off that the version you are installing is indeed supported before you install it. My supervisor and I thought by telling the vendor in the meetings that this would be evident. Next time we will force the issue before proceeding.
SQL Nexus, Quirkiness Workaround
SQL Nexus Report Options
Yesterday, I wrote about the “Realtime – Server Status” report quirkiness where essentially the report does not work when you import SQL Server 2008 data and essentially it does not contain any information as to why it does not work, only a blank screen. Craig Purnell (Blog | Twitter) shortly thereafter sent me an updated report library file that works with 2008. That is exciting! I ran a diff on the two files and found that Craig updated two lines with the same change in order for this to work. The filename is “Realtime – Server Status.rdl” located in the Reports subfolder of your SQL Nexus installation folder. Below is the line that needs to be changed (remember to do it in both places):
Original line: SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
Updated Line: SELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info
Thanks Craig, now I have results that look like the following:
Realtime Server Status
Best Practicing, the Results Part Dos
This week, we have been talking about running the Microsoft Baseline Configuration Analyzer 2.01 with the SQL Server Best Practice Analyzer for 2008 R2. Today I want to talk about one of the errors that occurred on one of the new boxes I just stood up in a new cluster. You may encounter a prerequisite error stating that the user is not a member of the Administrators group on the remote machine or PowerShell remoting is not enabled on the remote server. This is easily remedied.
- Add the user as a member of the Administrators group, or
- Run Enable-PSRemoting in PoweShell with elevated privileges, AND
- Run winrm set winrm/config/winrs `@`{MaxShellsPerUser=`”10″`} in PowerShell with elevated privileges.
Once I made these changes the analyzer was able to proceed. The analyzer will give you these directions, but I figured if you are reading this beforehand you can make the changes proactively. Enjoy!
SQL Swampland 