1 / 35

Thinking Outside the Box: Creating A Design Solution to Overcome Complex Business Needs

Thinking Outside the Box: Creating A Design Solution to Overcome Complex Business Needs. Speaker Background. Masters Degree in Computational Physics 20 + years of programming 11+ years of DBA experience. What are we talking about?.

ignatius-dayton
Download Presentation

Thinking Outside the Box: Creating A Design Solution to Overcome Complex Business Needs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Thinking Outside the Box: Creating A Design Solution to Overcome Complex Business Needs

  2. Speaker Background • Masters Degree in Computational Physics • 20 + years of programming • 11+ years of DBA experience

  3. What are we talking about? • Case studies involving some unique situations which used combinations of SQL Server 2005, Integration Services, and/or Reporting Services to overcome obstacles in order to provide a business solution. You may thank John Magnabosco!

  4. Why are we talking about this? With new advances in database technologies DBAs are increasingly being looked at as not merely administrators but Business Solution Providers! With that being said, it is beneficial if not outright necessary for you to embrace that role.

  5. Scenario #1 A Mid-West business is managing a client’s warehouse for them on the East Coast. The client is on the West Coast and would like a daily update of their data from a database on the East Coast location in order to fullfill reporting needs within the company.

  6. Caveats Business, Customer, and Warehouse are all on separate domains. Business & Customer are running SQL Server 2005 while Warehouse is running SQL Server 2000 Warehouse and Business domains cannot be connected directly. Information transfer can only occur at o’dark thirty. Data transfer consists of merely 2 million or so transactions daily.

  7. Solution • Non-Trusted Domain Transactional Replication. • Why? • Domains are not trusted and have multiple firewalls. • Clients will be adding in their own sets of tables, views, and such into the replicated database. • No availability of IIS for merge replication.

  8. Solution(Cont) West Coast(Subscriber) East Coast(Publisher) Mid West(Distributor) Domain Trust IP2IP via 1433 • This will work but you must get your network people involved. • This will (depending on the size of the database) take a couple of hours to set up correctly. • There are a couple of tricks that will be discussed in order to help you succeed.

  9. Things you must understand. There is no real magic to transactional replication. Transactions are read from the log file on the publisher and stored in the distributor. A job is run on the Distributor to take those stored transactions and run them as stored procedures on the Subscriber. The Distributor takes care of managing the process. Which transactions have been applied to which subscribers. BUT ….traditional Transactional Replication will not work in this instance.

  10. Solution(Cont) • First Steps • Create publication on the East Coast(SQL Server 2000) database using the Mid-West Server(SQL Server 2005) as the Distributor. • Get information and prepare for next step • Subscriber IP Address and Server Name • Verify connectivity by having Subscriber site set up shell database with sql account. • Run sp_scriptpublicationcustomprocs • Modify (if necessary) generated SQL

  11. Solution(Cont) Create an alias for the Subscribing server on the Distributor using SQL Server Configuration Manager

  12. Solution(Cont) • Preparing the script to create the subscription • This is run on the publication site to first authorize the subscriber and then to actually create the subscription • SQL Script Example

  13. Get it Running! Run SQL Script to create subscription Immediately take a backup of the Publication database. Zip and ship publication backup to Subscriber to have restored Subscriber restores the database and makes sure that the account being used for replication is set for access to the database as dbo. You can manually test the replication through replication manager.

  14. Notes to Remember We are not using Snapshots so changes to the publication must entail someone updating the stored procedures used during replication on the subscriber. Adjust the schedule of the clean up jobs for the distributor in order to provide an adequate window in case the subscriber must go down for some reason.

  15. Scenario #2(A Quick One) Finance business needs monthly reports from brokers concerning activity performed. Brokers are currently using an Excel template to enter in their information and forwarding to an administrative team member whom rolls up and consolidates the various lines of business into reports. The company would like to automate as much of the process as possible in order to streamline the timelines and store the data for future trending needs.

  16. Caveats Brokers are on remote networks and not connected into the financial institution. Brokers do not want to change the way in which they are currently entering the information. Management would like a way to mark and notify those brokers whom have not submitted. Management would like to have a short timeline for the report turnaround

  17. Solution Notify Delinquent Brokers Process Email(SSIS) Broker Send Email Place in common processing folder Reports are Generated and Sent to Management Information is Stored in SQL Server Database SSIS Processes Excel Files

  18. Automating Reports

  19. Automating Reports AH-HA! A stored procedure!

  20. Scenario #3(Another quickie) Consulting company needs a way in which to tracking the rolling progress of their consultants on customer accounts. Currently, consultants fill out accomplishment sheets on a daily basis which are kept on a common network file share location. Management would like to have an automated reporting solution that can address this need without changing the way in which the consultants do things.

  21. Caveats Management wants only some of the consultants to be reported on. Need the reports weekly. Need a way to tell whom has not submitted Consultants sometimes vary in their customer names AJ is lazy and wants the simplest way to do this.

  22. Solution A Simple Database

  23. Solution Notify Management of Rogue Employees Consultant Update Excel Sheet SSIS Processes Excel Files Reports are Generated and Sent to Management Information is Stored in SQL Server Database

  24. What is Unique? Answer: The way in which the data from the files is processed using SSIS. I use a stored procedure for the gathering the data based upon the database tables telling me which files are active files. The SSIS procedure is used for scrubbing my data before it hits the database.

  25. Stored Procedure Example ALTER PROCEDURE [dbo].[GetAccomplishmentData] AS BEGIN --DECLARE VARIABLES TO HOLD SYNTAX DECLARE @SQLSYNTAX as VARCHAR(2000) DECLARE @MESSAGE AS VARCHAR(4000) --CREATE TEMP TABLE IN ORDER TO HOLD DATA CREATE TABLE #HOLDINGTABLE( EmployeeID nvarchar(2), Client nvarchar(255), Date datetime, [Time] float, Task ntext) --DECLARE CURSOR IN ORDER TO CREATE AN ORDER LIST OF OPENROWSET QUERIES TO RUN DECLARE ACCOMPLISHMENTS_CURSOR CURSOR FOR SELECT 'SELECT ''' + CAST(EMPLOYEEID AS VARCHAR(20)) + ''' AS EMPLOYEEID,Client,Date,Time,Task FROM OPENROWSET' + '(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database=P:\Accomplishments\' + FileName + ''', ''SELECT * FROM [' + MonthName + '07$]'') ' FROM tblFiles f,tblMonth m where m.active=1 and f.active=1 FOR READ ONLY OPEN ACCOMPLISHMENTS_CURSOR

  26. Remember this!

  27. From BOL Transact-SQL Reference (SQL Server 2000) SET FMTONLY Returns only meta data to the client. Syntax SET FMTONLY { ON | OFF } Remarks No rows are processed or sent to the client as a result of the request when SET FMTONLY is turned ON. The setting of SET FMTONLY is set at execute or run time and not at parse time. Permissions SET FMTONLYpermissions default to all users.

  28. Reports And then we have pretty reports!

  29. Last One! (If there is time) Database Cloning ? Client needs a way in which to receive a backup from a client’s database. Then the clients tables of data need to be restored to an already existing database on the main production server.

  30. Caveats Cannot use traditional restore for this full backup. There will be additional tables,views, etc. that are being added to the production side. No replication No log shipping No fun!

  31. SMO to the Rescue! • SMO (SQL Management Objects) Dim dbSourceName As String = Dts.Variables("SourceDBName").Value.ToString Dim dbDestName As String = Dts.Variables("DestinationDBName").Value.ToString 'Connect to the local, default instance of SQL Server Dim srv As Server srv = New Server 'Reference the source database Dim db As Database db = srv.Databases(dbSourceName) 'Make sure the source database is 90 level db.CompatibilityLevel = CompatibilityLevel.Version90 'Reference the destination database Dim Destdb As Database Destdb = srv.Databases(dbDestName) 'TODO: Add in code for checking a particular tablename and then adding it to a restricted list 'Possibilities:IDW_ExemptionsList

  32. SMO(Cont) Public Sub CopyTable(ByVal table As String, ByVal dbSource As String, ByVal dbDest As String) Dim source As SqlClient.SqlConnection = New SqlClient.SqlConnection("Server=(local);Database=" & dbSource & ";Trusted_Connection=yes;") Using source Dim sql As String = String.Format("SELECT * FROM [{0}]", table) Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, source) source.Open() Dim dr As IDataReader = command.ExecuteReader Dim copy As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy("Server=(local);Database=" & dbDest & ";Trusted_Connection=yes;") Using copy copy.BatchSize = 10000 copy.DestinationTableName = table copy.WriteToServer(dr) End Using End Using End Sub

  33. 1 more trick Run an SSIS process from stored procedure --Run SSIS process DECLARE @SSISExec varchar(2000) SET @SSISExec='dtexec /SQL "\CopyTempDBtoProd" /SERVER "(local)" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET \Package.Variables[User::DestinationDBName].Properties[Value];'+ @DestinationDBName +' /SET \Package.Variables[User::SourceDBName].Properties[Value];' + @tempDBName + ' /REPORTING E' exec xp_cmdshell @command_string=@SSISExec

  34. So what was accomplished? • So what was accomplished • Streamlined process(Dramatically) • Cut-down TCO (Dramatically) • Totally handled within the Database Team using tools readily available within SQL Server 2005 environment. • Save $$$$$$$$ • Showed that DBAs ROCK!

  35. Conclusion • All slides will be posted on my blog • I will also try to blog in a little more detail and provide sample scripts. • http://www.programmersedge.com • Email: Arie.Jones@perptech.com • Questions

More Related