Telerik Reporting has come a LONG way….

I have been a fan of Telerik WinForms and ASP.NET controls since 2007. When Telerik decided to enter the Business Intelligence (BI) development, I was ecstatic. I immediately downloaded and began using Telerik Reporting v.1 I must confess, I was a little lost for words. The great Telerik had finally let me down. The first edition of Telerik Reporting seemed nothing more than a “revamp” of Microsoft Visual Studio embedded reports. So, unfortunately I sort of pushed it aside and continued using Visual Studio for reporting needs.

Fast forward to 2012……………

Today I downloaded the new Telerik Reporting and was VERY impressed and pleased with the newest rendition of the product. The integrated Client Install, is an easy way to “pick and choose” what you would like to “try” and actually gives you access to licenses as well.

I am very familiar with the integration with Visual Studio for a Win Forms or Web applications, but because, I currently work with existing Microsoft SSRS 2008 server and over 100 reports in several Report Projects, I wanted to know explicitly how it could help me in managing existing SSRS hosted reports. So I installed with the Visual Studio 2008 support and immediately opened a new SSRS report project. Bummer; well it seems Telerik and SSRS Projects are still very separate entities, which I am not surprised. I was hoping for the ability to edit deployed SSRS report projects and for some more “integration” between the two; especially since I am now primarily a BI developer.

Now on the flip side; the ability to integrate reports into your application, either Windows or Web, is made easier with Telerik Reporting. The Controls are very straight forward and easy to understand. The ability for Silverlight controls is also an added plus for those applications developers.

I also do a few “side” development jobs one of which required embedded SSRS report. Having Telerik could have made it a more enjoyable experience and quite possibly better to render.

As a BI developer using SSRS, my “customers” are always wanting unusual, challenging and even down right (what I thought was) impossible reports. Because of these challenges and the somewhat short-falls of SSRS 2008; I may actually consider proposing a “reporting” ASP.NET website with Telerik Reports and show my bosses the POWER of TELERIK!

Determining specs for a new SQL Server

I have been tasked for “specking” out the requirements for a new OTLP SQL Server and I would like some feed back on this as well. 

Here are my initial requirements, we have to use SQL 2008 R2 because the application that will be using these databases have not been certified for SQL 2012 yet (boo)

Some background, the current server has over 1 TB of databases and is highly active with over 100 concurrent connections. These connection process UPDATE, INSERT, DELETE statements like they were going out of style. And one of these databases is a repository for data used by SSRS Reports.  Not an ideal situation, but I have to follow application manufacture guidelines.

here’s what I have:

• OS: Windows Sever 2008 R2 x64
• SQL: SQL Server 2008 R2 Enterprise Edition x64
• Processors: 2 Quad core processors
• Memory: as much as can be budgeted, but minimum of 64 GB
• Local Drives:
• 1 – System Drive for OS & Applications the minimum free space needs to equal the amount of RAM installed
• 1 — for TempDB, Full-Text Indexes, other resources and System databases
• Database drives: SAN Connections 2 drives: one drive for MDF and one drive for LDF

 

Please let me know if I missed something or if I am TOTALLY off my rocker with this setup.

Thanks!

SQLBackupAndFTP (a product review)

After reading Pinal Dave blog post on Best Practices to Store the SQL Server Backups, I noticed he mentioned an “easy to use” SQL Backup 3rd party software that allows network storage, even FTP transporting.

So, I thought I would give SQLBackupAndFTP a try.  The download is a relatively small foot-print and installation was a breeze, however I wish there were a x64 bit version.  I know x64 bit machines will run the software, but it is my personal “best practices” to not use x32 bit software with x64 bit machines.

Dave was right, this is most definitely an “easy to use” software.  I can see it coming in real handy with applications that require SQL Express.  With the database sizes (almost 1TB of data on one server) I work with, remote storage and FTP are not a viable option.  Setting up the network storage was a breeze.

I was a little worried when the error message popped up concerning Remote SQL Server backups, especially since it mentioned “beta”.

I believe this would be an excellent development/test environment backup solution because it can be installed on the server and managed on each server.  However, in a production environment with over 90 SQL Servers, a remote backup solution  would be ideal.  I am afraid this would not be the solution.  I could be wrong.  I am a little fearful to place it into production right now.

However, that all being said, I will definitely be putting it to use on my development servers, workstations and test servers.

Where is the key to the LOCK?

This past Friday evening, a simple data import process (that has been working for several months), decided to not work correctly.  Because there was no “early warning” type of notifications systems on the SQL 2005 server, the SQL Agent Job was actually running when Monday morning rolled around.

Using Idera’s SQL Check, I quickly noticed that there was a process that had a lock on a table that was causing some MAJOR problems.  Four different SQL processes was waiting on this lock.  And of course my supervisor was getting calls left and right from end users because their applications were not working.

I had to figure out what was going on.  I was able to get a list of processes and which one was causing all the problem with the sp_who2 command.

sp_who2'active'

However, this returned a “bias” HOST NAME.  I am not really sure why. But I confirmed with networking that the returned hostname and IP address do not exist on our network, but that is for another day.

I needed to know an accurate way to get all the information I needed at that moment when a job wrongs long.  This code is probably providing way more information than I really need, but sometimes more is better.  Even though the “host_name” is returning a “bogus” host name; the client_net_address is not!  This valid IP address is very important to locating the culprit machine.

SELECT 
    C.session_id
    ,C.client_net_address
    ,S.host_name
    ,S.Client_version
    ,S.client_interface_name
    ,S.login_name
    ,S.original_login_name
    ,db_name(R.database_id) AS [DB Name]
    ,R.status
    ,R.command
    ,R.blocking_session_id
    ,R.wait_type
    ,R.percent_complete
    ,wait_resource
    ,C.net_transport
    ,C.protocol_type
    ,C.auth_scheme
    ,C.last_read
    ,C.last_write
    ,S.cpu_time
    ,S.memory_usage
    ,(S.total_elapsed_time/1000) AS [Total Sec]
    ,S.lock_timeout
FROM sys.dm_exec_connections AS C
JOIN sys.dm_exec_requests AS R
    ON C.session_id=R.session_id
JOIN sys.dm_exec_sessions AS S
    ON C.session_id=S.session_id
ORDER BY session_id

Using Thomas LaRock’s  HOW TO: Find Currently Running Long SQL Agent Jobs, I setup up a simple 2 step SQL Agent job to check for 1) long running jobs and 2) session information (using the code above).  A simple “save results to txt file” allows me to export the results to and study them later.

USE [msdb]
GO
/****** Object:  Job [job_WH_MonitorDatabase]    Script Date: 10/30/2012 13:57:18 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [xx] Script Date: 10/30/2012 13:57:18 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'xxx' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'xxx'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'job_MonitorDatabase', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'’, 
        @category_name=N'xx', 
        @owner_login_name=N'INFOSYS\xx', 
        @notify_email_operator_name=N'xx', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Monitor]    Script Date: 10/30/2012 13:57:18 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Monitor', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec usp_LongRunningJobs', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RecordProcesses]    Script Date: 10/30/2012 13:57:18 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RecordProcesses', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select
    sysp.spid AS [SPID]
    ,sysp.status AS [Status]
    ,sysp.hostname AS [Host Name]
    ,cn.client_net_address AS [Client IP]
    ,db_name(sysp.dbid) AS [DB Name]
    ,convert(sysname, rtrim(sysp.loginame)) as [Login]
    ,sysp.program_name AS [Program]
    ,sysp.cmd AS [CMD Status]
    ,sysp.cpu AS [CPU usage]
    ,sysp.physical_io [IO]
    ,sysp.blocked AS [Blocked By]
    ,sysp.waitresource AS [Wait Resource]
from master.dbo.sysprocesses AS sysp
INNER JOIN sys.dm_exec_connections AS cn 
    ON sysp.spid=cn.session_ID
WHERE sysp.dbid > 4 AND sysp.cmd <> ''AWAITING COMMAND''
ORDER BY sysp.hostname', 
        @database_name=N'master', 
        @output_file_name=N'F:\SQLAgent_Logs\RunningProcesses.txt', 
        @flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=10, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20121030, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Thanks for your time!  I will let you know how it turns out.

SQL Saturday Baton Rouge #150

On Tuesday, I was notified that my presentation, “Making the CASE for a full-time DBA” got accepted on the schedule for SQL Saturday Baton Rouge #150.  WooHoo, my first SQL community event presentation!

I am thrilled and nervous at the same time.  I have many years experience speaking in front of people, that’s a no-brainer for me.  However, I have never spoken in front of peers on “work related” topics. 

My presentation is targeted to those “Accidental DBA’s” who find themselves administering and managing SQL Servers. 

• Developers, Computer Technicians, System Administrators and other IT personnel who were not hired as a DBA, but find themselves doing the job.
• individuals who scratch their head, Bing and Google-it to find answers.
• individuals with less than 2 years of DBA experience.

The “who, what, where and how’s” of getting simple information out of SQL server and discovering the “major” problems and no-no’s in the SQL world.  Hopefully, I can provide some simple ways, steps and information that will make your “Accidental DBA” career easier. 

Once you can better understand SQL, just maybe you might like the work and want to be a full-time DBA.  How do I change career paths, where do I find more information, do I need certifications?  I will also briefly discuss how to “pitch a full-time DBA position” to your Manager, Director or VP.

There will be some humor, laughing (probably at me) and hopefully some informative information for those non-DBA types, the DBA newbies or DBA “wanna-bies”.

Thank you again for the SQL Saturday team, I hope I don’t disappoint.

 

UPDATE:  Because of my work, unfortunately I was unable to participate and had to withdraw from presenting.  I was crushed.  But there is always next year!!!!

SQL Padre’s Monitoring Solution–Intro

As time goes by and software evolves, SQL Monitoring solutions are becoming more and more robust and more and more effective. Two of my favorite solutions Red-Gate’s SQL Monitor 3.0 and Idera’s SQL Diagnostic Manager.  Yes they are direct competitors, but I tested beta version, trial versions and I think both solutions are stellar. However, as time goes on and as the economy continues to struggle, businesses are showing more and more reluctance on buying “un-necessary” 3rd party software.  Of course “un-necessary” is a relative term, some of us DBA find these types of software critical for 24 x7 operations.

So in an endeavor to provide some of their functionality, I am planning on building my own SQL monitoring solution.  Yes I know many have done such things, but call it a personal mission that I have wanted to accomplish for some time.

The hardest part was to determine if I wanted a SQL Solution (SSIS, SSRS) or a application solution (.NET, web GUI, application GUI, windows services, etc.).  I believe I am going to make a hybrid of the two.  I hope to use SSIS,  custom stored procedures, SQL Agents jobs to collect data and store that data in a database.  Then create a .NET 4.0 Web Application leveraging SSRS technology as well. If the Web app, is on a public server, or in the DMZ, then theoretically it could be available away from the office as well!

In the next chapter, I will map out the very basic information I want to collect from a MS SQL Server Instance and provide the T-SQL to get that information.

Weekly Question/Poll

In the future, I hope to learn as much about and from the SQL Community as I can. And one of the ways to do this is to have an easy question for you to answer. On the left bar of the blog will be a weekly (ok, it may actually be a bi-weekly) question or simple poll in hope of just gathering data.

Please, it only take a few seconds to answer the questin!

Thanks!

Back to the Basics (B2B: Intro)

On occasion, an IT personnel (non-DBA) is tasked with providing SQL support.  Either with installation, upgrading, backups, restores, moving databases or other tasks that normally would not fall under their “job description”.  Trust me I have been there.  We call these “Accidental DBA’s” and sometimes a strange thing happens, the person actually ENJOYS working with MS SQL.  At least that is what happened to me, I was a .NET developer in a corporation with no DBA and over 20 instances of MS SQL in our network.  As the developer, I did create databases, tables, views, etc.; but I didn’t lean SQL management until much later.

So if you are one of those accidental DBAs, where do you find more information on how to do DBA work.  I have always found www.sqlservercentral.com, www.mssqltips.com, and MS MSDN invaluable resources to find answers to questions.  I following some “gurus” of the industry on twitter and their blogs are never boring and always informative.  Some of my personal favorites, to just name a few:

1. Brad McGehee
2. Steve Jones
3. Brent Ozar, PLF (which in reality you get 4, Brent Ozar, Jeremiah Peschka, Kendra Little, Jes Schultz Borland)
4.  Penal Dave
5. Thomas LaRock

Every non-DBA needs to know the simple and basic steps to manage MS SQL.  Although I am a fan of GUI interfaces, I will attempt to provide both T-SQL and GUI images to help along.  Yes, I know the evil GUI, most professionals will tell you that knowing and using T-SQL is the only proper way to manage a SQL instance.  However, for those of us that are used to a “point-and-click” environments sometimes learning the point-and-click methods is the quickest and easiest way, especially for those of us who are visual learners.  That being said, I encourage you to always script it out, so you can learn the T-SQL behind the GUI.

During this series I will hope to provide the following information:

1. How to install SQL; standalone vs. network, there is a difference!
2. Working with SQL Server Management Studio SSMS
3. Documentation: Why, I can remember the name of the server? 
4. Documentation Part 2: Didn’t realize the database was that big.
5. Maintenance plans (they are not just for backups)
6. Definition of and creating a Disaster Recovery plan

As of now these are the topic I plan to cover in my Back to Basics series.  Although I reserve the right to edit this, I believe if an “accidental” DBA would learn these, then they could probably drop the “accidental” form their title.

In the coming months

So in the next coming months I hope to begin 2 blog series.
1. Back to Basics –targeted to the Accidental DBA or just a new DBA

2. SQL Padre’s Monitor Solution — this will be a record of my efforts to build a home grown SQL monitoring solution.

Ambitious? Yes, but I have never been known to take on only one task!

So here’s to more active blogging and hopefully some little tidbits of information along the way.

Posted from WordPress for Windows Phone

Playing with SQL 2012…(and a rookie mistake)

So I thought I would install SQL 2012 Express with Advanced Services on a Windows 2008 R2 box in the hopes of playing with the new edition and using it as a platform for a new SQL Monitor Solution I am building.

First thing I learned, the hard way, was .NET 3.5 SP1 (which is required for SQL 2012) is not “enabled” by default on Windows 2008. It is installed, but not enabled.  Open System Manager and find “Features”.  Click “Add New Feature” and select .NET 3.5.  This will prompt you to enable IIS and all associated features need to enable .NET 3.5.

Once this pre-requisite was “installed” installation of SQL Express went without a hitch.  I used default settings in most places, except for Service Account information.  I setup a domain service account to run the SQL Services.  All was good.

I then opened SQL Server Configuration Manager to enable TCP/IP connection so I could have remote access.  Except one simple problem, I couldn’t connect.  I double checked all my steps, and still could not connect.  After some “Bing”-ing (I dislike that G company), I realized my “rookie mistake”.  Thanks to this 7 YEAR OLD blog post, realized I forgot to enable SQL Browser.    http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

Silly, silly me!