How do I use SQL Server
I've been following a series of blogs of real DBA's who are talking about how they use SQL Server. As a reformed Programmer/Analyst turning into a DBA is an interesting journey and I figure I'll try to add what little I can to the community. I echo the statements of others that it may be easier to say what SQL is not doing in my company, but I figured I'd join in the fun as well.
I work at a small/mid sized pharmaceutical marketing data company. The business leaders would have you think we do marketing, but really we deal in the business of data. We setup promotions where by patients receive the ability to get a financial reward for filling a prescription. This can be instant at the Pharmacy (on a large data network), or via a rebate. Payments from the rebate can be from a check that we cut here on site, or from a debit card that we can issue and place funds on.
The main thing that our customers are interested in are reports and data. We have lots of data coming and going, in and out all the time.
Our environment is ever changing in regards to the infrastructure. When I started, there was no DBA, no IT Managers, we had an off-shore coordinator and 3 teams of off shore developers who rolled a lot of their own code. So I inherited several SQL Server 2000 Standard edition servers all linked together using Linked Servers on some relatively weak hardware. There were performance issues galore, and one of the more interesting features was a home rolled "replication" of sorts that used triggers, sync tables, SQL Agent jobs and stored procs to push data from server to server to have some sort of High Availability. That wasn't so good. I had blocking, locking, deadlocking, long running processes and no one person who knew more than 33% of the picture.
Now we have moved to 2 SQL 2008 Enterprise clusters on windows 2008 (One cluster is on Windows 2008 R2, and I highly recommend that). We jumped SQL 2005 and I have limited knowledge of 2005 myself so moving from SQL 2000 to 2008 has been a huge learning curve for me, and I'm still coming in to knowledge of all the cool 'new' features that make life easier.
ETL is a huge part of our business, but highly over looked. We have some hybrid systems that utilize custom .NET code that then pushes to SQL Server to manipulate the data via stored procs. We have some DTS, some SSIS and actually are moving towards BizTalk.
Our OLTP is handled 100% by SQL 2008 at this time, there are 50+ databases on the one cluster for a grand total of half a terabyte of data. Not hugely impressive, but considering we do 50,000 transactions a day on just one of these databases the growth rate is rather impressive.
Reporting is handled by a mis-mash of technologies. We have a crude datamart using SQL 2000 enterprise that uses linked servers, views and stored procs to create 'fact tables' in a nightly process that runs 9-11 hours 6 days a week. We are moving to having another SQL 2008 cluster that is a transactional replication subscriber to the OLTP cluster. This reporting cluster will have a subset of the databases of the OLTP cluster because we don't report on all of our data. the app config databases, app security databases, error logging, system logging etc databases (almost all legacy) will not be replicated.
We will then create OLAP cubes and dimensions and what not (as I learn SSAS) to create a data warehouse and data marts.
We use 2 home rolled reporting solutions that are being slowly taken out of the enterprise and being replaced with SQL Server Reporting Services 2008. We have the internal SSRS 'portal' that we are using, it is what ships with SSRS and our internal folk use that for ad-hoc reporting, to schedule reports to be emailed or delivered to a folder to have another process FTP it to customers.
We are also developing a SSRS portion to our data portals for customers to directly link to their reports online. This means that our reporting database has to be very responsive and up 99.999% of the time.
I am currently using RedGate products to handle many tasks. RedGate SQL backup 6.0, RedGate SQL Response 1.2 I lean on most, along with some scripts that I have written myself or modified to maintain indexes and take care of system integrity checks. We also use other tools to monitor the physical boxes but that is a job for the sys-admin.
I also have a few SSRS reports I have written to keep tabs on data growth and VDI memory use.
I've been using SQL Server in one way shape or form for almost 15 years now dating back to SQL 6.5 on Windows NT 4, but really that was more from a programmer and Database Architect stand. Sure, I also was on very small teams where there wasn't a DBA so I did some of that (Backups and what not) but now that I'm in an area where data is what we do, I'm moving more in a DBA role and learning as I go. I suppose that is how most people become DBA's.
I work at a small/mid sized pharmaceutical marketing data company. The business leaders would have you think we do marketing, but really we deal in the business of data. We setup promotions where by patients receive the ability to get a financial reward for filling a prescription. This can be instant at the Pharmacy (on a large data network), or via a rebate. Payments from the rebate can be from a check that we cut here on site, or from a debit card that we can issue and place funds on.
The main thing that our customers are interested in are reports and data. We have lots of data coming and going, in and out all the time.
Our environment is ever changing in regards to the infrastructure. When I started, there was no DBA, no IT Managers, we had an off-shore coordinator and 3 teams of off shore developers who rolled a lot of their own code. So I inherited several SQL Server 2000 Standard edition servers all linked together using Linked Servers on some relatively weak hardware. There were performance issues galore, and one of the more interesting features was a home rolled "replication" of sorts that used triggers, sync tables, SQL Agent jobs and stored procs to push data from server to server to have some sort of High Availability. That wasn't so good. I had blocking, locking, deadlocking, long running processes and no one person who knew more than 33% of the picture.
Now we have moved to 2 SQL 2008 Enterprise clusters on windows 2008 (One cluster is on Windows 2008 R2, and I highly recommend that). We jumped SQL 2005 and I have limited knowledge of 2005 myself so moving from SQL 2000 to 2008 has been a huge learning curve for me, and I'm still coming in to knowledge of all the cool 'new' features that make life easier.
ETL is a huge part of our business, but highly over looked. We have some hybrid systems that utilize custom .NET code that then pushes to SQL Server to manipulate the data via stored procs. We have some DTS, some SSIS and actually are moving towards BizTalk.
Our OLTP is handled 100% by SQL 2008 at this time, there are 50+ databases on the one cluster for a grand total of half a terabyte of data. Not hugely impressive, but considering we do 50,000 transactions a day on just one of these databases the growth rate is rather impressive.
Reporting is handled by a mis-mash of technologies. We have a crude datamart using SQL 2000 enterprise that uses linked servers, views and stored procs to create 'fact tables' in a nightly process that runs 9-11 hours 6 days a week. We are moving to having another SQL 2008 cluster that is a transactional replication subscriber to the OLTP cluster. This reporting cluster will have a subset of the databases of the OLTP cluster because we don't report on all of our data. the app config databases, app security databases, error logging, system logging etc databases (almost all legacy) will not be replicated.
We will then create OLAP cubes and dimensions and what not (as I learn SSAS) to create a data warehouse and data marts.
We use 2 home rolled reporting solutions that are being slowly taken out of the enterprise and being replaced with SQL Server Reporting Services 2008. We have the internal SSRS 'portal' that we are using, it is what ships with SSRS and our internal folk use that for ad-hoc reporting, to schedule reports to be emailed or delivered to a folder to have another process FTP it to customers.
We are also developing a SSRS portion to our data portals for customers to directly link to their reports online. This means that our reporting database has to be very responsive and up 99.999% of the time.
I am currently using RedGate products to handle many tasks. RedGate SQL backup 6.0, RedGate SQL Response 1.2 I lean on most, along with some scripts that I have written myself or modified to maintain indexes and take care of system integrity checks. We also use other tools to monitor the physical boxes but that is a job for the sys-admin.
I also have a few SSRS reports I have written to keep tabs on data growth and VDI memory use.
I've been using SQL Server in one way shape or form for almost 15 years now dating back to SQL 6.5 on Windows NT 4, but really that was more from a programmer and Database Architect stand. Sure, I also was on very small teams where there wasn't a DBA so I did some of that (Backups and what not) but now that I'm in an area where data is what we do, I'm moving more in a DBA role and learning as I go. I suppose that is how most people become DBA's.
Labels: How I use SQL Server, SQL, sqlserverpedia
posted by TedS at 8:52 AM
0 Comments:
Post a Comment
<< Home