DTSRUN: To Multi-tier Or Not To Multi-tier. That Is The Question...
Jul 20, 2005
Greetings,
We are trying to set up a set of "Leading Practices" for our
developers, as well as ourselves, and hope some gentle reader can
recommend some documentation in favor of what appears to be the right
position to take.
We do not allow third party applications to run on our SQL Servers. We
want to include DTS Packages under the definition of third party
applications, insisting instead that the developers save their
packages as COM Formatted files into their source code control systems
and run them from their app servers. The devlopers would like to hear
this from someone besides ourselves.
While strong recomendations to remove guest access to MSDB altogether
abound, I have been unable to find a straight forward discussion of
the advantages of structured file storage and app server off load of
DTS packages.
Can anyone suggest any articles, white papers, rants, etc attempting
to formulate a solution to the benefits of taking msdb away from
guest, with the advantages of running DTS from an App server or
workstation platform, with the packages protected in source code
control?
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
We are thuinking of installing Server 2008 in x64 mode on a new Application-Tier-Machine. But due to existing projects which use several databases the SQL-machine runs on a Server 2003 x32.
Would it be possible to install the Reporting Services x64 binaries on the app-tier but use a x32 SQL-Instance for the data?
Hello all, Q1:In all asp.net books the authors would tell you to seperate your presentation , business, and data layer but why does the wizards for the sql data source combine the presentation layer with the data layer? Q2:Is there a way to retreive a dataset after you execut the sqldatasource.insert() command? .......I have a store procedure(sp) that i am using for inserting records. The sp is where i 'm validating all my data (shop standard) for example, ssn length, last name <> 0 etc. If there are any errors i grab all the error code from another table and return it. I can't seem to figure out how to get the dataset....
I've just been doing some surfing on the pros and cons of multi-tiered environments. I see a lot of conflicting information out there.
I'd like to get everybody's opinion of the advantages and disadvantages of two-tiered vs three-tiered applications, specifically with a SQL Server back-end and all the functionality that comes with it (TSQL, triggers, user-defined functions, etc...).
Also, some definitions or principles regarding what tasks should be handled in which layers (especially business logic).
_____________________________________________using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration;
namespace BaResearch.Data.msSql {
public class Bid : SqlDataObject, IBid {
#region private member & variables
private int _BidID; private string _DateCreated; private string _CreatedBy;
private SqlDataAdapter sqlda = new SqlDataAdapter(); private SqlCommand _command; private SqlParameter[] _parameters = { new SqlParameter("@client",SqlDbType.NVarChar), new SqlParameter("@contact",SqlDbType.NVarChar), new SqlParameter("@sponsor",SqlDbType.NVarChar), new SqlParameter("@priority",SqlDbType.NVarChar), new SqlParameter("@bidstatus",SqlDbType.NVarChar), };
#endregion
#region public member variables - stored procedures
public virtual DataSet List(string filter) { DataSet result = new DataSet();
_command = new SqlCommand(); _command.Connection = this.Connection; _command.CommandText = @"SELECT * FROM bids_view_listall WHERE (clientname like @value) or (contactname like @value) or (sponsor like @value) ORDER BY datecreated DESC"; _command.CommandType = CommandType.Text;
public string Client { get { return _Client; } set { _Client = value; } }
public string Contact { get { return _Contact; } set { _Contact = value; } }
public string Sponsor { get { return _Sponsor; } set { _Sponsor = value; } }
public string Priority { get { return _Priority; } set { _Priority = value; } }
public string BidStatus { get { return _BidStatus; } set { _BidStatus = value; } }
#endregion
#region Properties
public int BidID { get { return _BidID; } set { _BidID = value; } }
public string DateCreated { get { return _DateCreated; } set { _DateCreated = value; } }
public string CreatedBy { get { return _CreatedBy; } set { _CreatedBy = value; } }
#endregion
}
}
_____________________________________________
Am I on a right path? I will greatly appreciate in any comments or suggestions for a design. I am creating an n-tier application and i don't know if my design is right. I dont have the proper schooling for creating this kind of applications and I am still on layer of data access and business logic.
I have created an application that I intended to be 3-tier, but I am not sure if I did it properly. I constructed it like this: I created a DLL that contains methods that validate the passed parameters, checks the data against business rules, and issues ADO.NET methods to access the data. The ASP.NET presentation layer uses Object Data Sources to link to these methods. With this architecture I consider the ASP.NET pages to be the presentation layer, the DLL to be the business layer, and the database itself to be the data layer. Now I am wondering if the standard practice is to have a further division? In this case, there would be a business layer DLL whose only purpose is to validate the parameters passed to it by the presentation layer, and to do business rules checking. There would also be a data layer DLL whose purpose is to accept parameters from the business layer and issue ADO.NET methods to access the database. In this scenario the database itself would be considered part of the data layer, or not considered to be a layer at all. Either one will work, but I would like to implement the architecture that is most accepted, and allows the easiest maintenance. What is the best practice for designing a 3-tier architecture?
I have a 3 tier merge replication architecture that I am having some problems making changes to. We have a 3 tier architecture with many laptop instances on the bottom tier. It is exceedingly hard to coordinate having all laptop instances online simultaneously during non business hours with users present for local administration. The requirement is to change the name of the top tier without effecting the replication from the middle to bottom tiers.
I have so far renamed the top tier's instance, reconfigured publication, created a new top tier publication, and a new middle tier subscription. The top tier article has been set to delete all data in the subscriber's table when applying the snapshot. Making data changes at the middle tier filters correctly to both the top and bottom tiers. However, any changes made at the top or bottom tiers filter to the middle tier but no further. At that point, there are no errors, but simply no data needing to be merged.
Does anyone know how dropping and recreating a top tier publication could cause a 3 tiered replication architecture to behave like this? Is there any solution short of having to recreate subscriptions on all the bottom tier laptops?
I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database. I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture. I have been following the principles of Scott Mitchell's tutorials. I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor. Thus where the first version had code: - Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'" Dim message As String = "" Dim Found As Boolean = False Try Using connection As New SqlConnection(GDB_AppSettings.Connection) Dim command As New SqlCommand(queryString, connection) connection.Open() Dim reader As SqlDataReader = command.ExecuteReader() If reader.Read() Then Found = True _USRid = reader("USRid") _USRPassword = reader("USRPassword") _USREmail = reader("USREmail") _USRTitle = reader("USRTitle") _USRName = reader("USRName") _USRRole = reader("USRRole") If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End If End If reader.Close() End Using Catch ex As Exception If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then Return "Cannot open database to logon" Else Return Err.Description End If End Try the new version is much more elegant: - Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter() Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid) If tbUser.Count <> 1 Then ' Anything other than 0 or 1 should be impossible Return "User not found" End If Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0) _USRid = userid _USRPassword = password _USREmail = trUser.USREmail _USRTitle = trUser.USRTitle _USRName = trUser.USRName _USRRole = trUser.USRRole _USRAgreedConditions = trUser.USRAgreedConditions However, there is a problem. The database field USRAgreedConditions is a Datetime field that can be null. The new version works perfectly when it is NOT null, but throws an exception: - System.Data.StrongTypingException was unhandled by user code Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull." Source="App_Code.wt2vzoc1" .... There is no point in writing: - If Not IsDBNull(trUser.USRAgreedConditions) Then _USRAgreedConditions = trUser.USRAgreedConditions End Ifbecause the exception occurs within the automatically-created code in the data access layer. I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String. Of course USRAgreedConditions is a Datetime field, so I can't change the property. It seems that my only options are: -1. To stick with the old query-building code. But this doesn't give me the advantages of a proper 3-tier architecture2. To change the generated code in wt2vzoc. This MUST be a bad idea - surely I should leave this code untouched. Besides, what if the table adaptor has to be regenerated when I change the table design?3. Code a Try block within the business layer: - Try _USRAgreedConditions = trUser.USRAgreedConditions Catch ex As Exception _USRAgreedConditions = Nothing End Try This seems to work OK, but seems less elegant than the original code in the old version: - If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End IfIs there a better way?
There doesn't seem to be consensus about when to put code in thedatabase or in the middle tier. There was a long discussion about thisin an Oracle newsgroup (message ID:ULcQb.466$KU5.37@nwrddc02.gnilink.net).Elsewhere there's been discussion about Microsoft SQL Server 2005adding the CLR to support stored procedures in languages such as C#. Ascan of the Web and discussion forums finds differing opinions aboutthis.Two authors have written articles that fall on different sides of thedebate."Keys to the Database"http://www.intelligententerprise.co...icleID=50500830"SOA, Multi-Tier Architectures and Logic in the Database"http://www.sqlsummit.com/Articles/L...TheDatabase.HTMJoe Celko wrote the first article, but his objections point toMicrosoft SQL Server 2005:"I have an article at WWSUG.com on how much I hate the CLR stuff thatMicrosoft is putting out."http://blog.intelligententerprise.c...ves/002419.html"The bad news is that SQL Server 2005 will you define your ownaggregatefunctions in a CLR language."Message id: Join Bytes!IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is thisa non-issue or are all three companies misguided?
We are trying to implement a 2 tier architecture for our inbuild SQL 2005 backed online application. We want the SQL Reporting Server Interface --- Reporting service website to sit on Server 'A' and the actual reporting server to sit on Server 'B'.
Can someone suggest or advise us how to proceed with this.
Server 'A' is our application server and has IIS & Application. Server 'B' is our DB server and has IIS,SQL 2005, Reporting Server,Intergration Service.
We have a 2-tier architecture with thick client (.NET 2.0) applications directly accessing the SQL Server database. We are struggling to manage lots of users while maintaining security. Granting lots of users directly to the database seems to be tough to manage. In fact, we would like to let supervisors without DBA previlege to add and remove users of our applications. Using SQL Authentication (a single account to access the database) is the other alternative but it is not considered as a secure solution.
I would appreciate if anyone gives me suggestions on how to handle this, without moving to a 3-tier architecture (dedicated middle-tier DB access layer running a custom user account).
Hi All, Im trying to migrate a N tiered solution written in Java and Berkeley DB(in memory DB product) to MS stack. ie the in memory DB is being used as a middle tier caching solution.
Im considering using SQLCE where Berkely DB was being used, im guessing that SQLCE being an inproc DB may be faster than using SQLExpress etc.
Has any one tried to do this before? and if so can you share your experience? Alternatively if you can provide any resources on the web that discuss SQLCE performance(ie not when run on WinCE etc)/ comparison of query performance between SQLCE and SQL Express/Standard that would be great.
I'm looking for outside opinion on how to implement the business layer of an application. The straight forward idea I know of would be to develop a component that encapsulates all the business rules under objects. Client applications would access this component, and the business component would talk directly to the database. This was great for me when I used Access databases.
Now I have been introduced to SQL server and the stored procedures. I think stored procedures are one of the best features because these procedures can enforce rules as sort of a "last line of defence". So this is where my question comes in. Where would the best place be to implement the business layer? Through a component, such as XML services that allow other clients to access the business rules OR stored procedures.
I do understand that components/dlls provide MUCH more flexibility then stored procedures.
But, if I do take the component route:
- Other then SQL queries that get called multiple times, is there any use for stored procedures? - For some case scenarios such as web sites that access a Business Layer, should the business layer use ONE sql server login/role to access the data or create a sql login/role for each major segment/service? E.g. one login for the accounting side, one login for the web site services, one side for HR and the business layer will have to decide which one to use depending on the service requested. - Would it be in the best interest of security that I allow the business components to send SQL queuries such as Insert/Delete/Update? I loved the idea of stored procedures because if for some reason, someone steals the business layer userId/password, they are only exposed to the assigned stored procedures and cannot do "Delete From Table". Unfortunately, this practice has led to an abundance of stored procedures (the list is HUGE).
If I do take the Sql stored procedure route:
- If someone steals any user Id/password, they will only be exposed to the assigned stored procedures and THOSE stored procedures contain the business rules. Thus the damage can be minimized.
- If some developer makes a mistake down the road or forgets some sort of business aspect (and is not going through the business layer), again, stored procedures would offer a last "line of defense". Lets face it, there are some applications that do not necessary require a business layer
I'd like to get everyone's opinion on how they partition the business rules with SQL server? Do you use SQL server stored procedures to only enforce formating rules (such as upper case, lower, etc) while the business rules are stored in a component?
Do you use stored procedures to enforce all business rules? None?
I want to create one bacpac file but it does fails, Need I delete all the descriptions before to begin? How odd! I don't understand why can we choose some scripting options such as we did on 'Generate Scripts' task..I get errors such as: dbo.table.field.Ms_Description is not supported when used as part of a data package
Hi Folks ...Question for everyone that I have not been able to figure out. I have an application that is spread across tiers:SQL Connection defined in Web.Config file that connects to SQLServer database.DAL layer created references SQL Connection in Web.Config file - have noticed this copies the connection string information to the local properties for each TableAdapter that is defined in the DAL layer.BLL Layer that references Table Adapters declared in the DAL layer.When the web site is called, the link will provide an encoded id.Sample call to website: http://www.mysamplesite.com/default.aspx?company=AE2837FG28F7B327Based on the encoded id that is passed to the site, I need to switch the connection string to use different databases on the backend.Sample connection string: Data Source=localhost;Initial Catalog=dbSystem_LiveCorp1;User ID=client;Password=live2006 I would need to change Initial Catablog to the following:Data Source=localhost;Initial Catalog=dbSystem_LiveCorp196;User ID=client;Password=live2006How do I do this and have the connection string reflected in all of the Table Adapters that I have created in the DAL layer - currently 100+ Table Adapters have been defined.As we gain new clients, I need to be able to have each clients information located in a different database within SQL Server. Mandated - I have no choice in this requirement. Being as I don't want to have to recreate the DAL for several dozen clients and maintain that whenever I make a change to the DAL to then replicate across multiple copies. There has to be a way to dynamically alter the SQLConnection and have it recognized across all DAL TableAdapters.I'm developing with MS-Visual Studio 2005 - VB. Any help would be greatly appreciated. Thanks ...David Any day above ground is a good day ...
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition --We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately --Recovery needs to happen within 1 hour (Not sure that this is realistic -- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable 2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances 3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all? 2) Should DTC be in its own resource group or within the instance's group? 2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
How to implement distinct storage tiers on SQL Remote BLOB Storage (RBS)?
I want to use this SQL Feature to move files(images, videos, pdf files) from a database to a distinct database dedicated to RBS. Then I want to have several storage tiers, where objects will be saved and moved according access frequency. Old data will be arquived in cheap storage, but it must be always accessible if needed.
Description: - 1st and main tier: new and frequently accessed objects stored in high performance storage; - 2nd tier: automatically move older or less accessed objects to an inexpensive and different storage tier; - in all cases, all objects must be accessible to all users, but accessing to archived objects(2nd tier) will be much slower;
Is it possible to create a stored procedure that can be used on multiple dBs??
For instance, I want to create a stored procedure and use it on DB1 and DB2. Right now, I can create a stored procedure in DB1 but only I can run it. I want to run the stored procedures on DB2 that I created in DB1..
I hope this makes sense.. Any insight into the workings of SPs would be most appreciated...
-- declare table declare @Table1 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table1select 1 , 2 union allselect 2 , 1 union allselect 7 , 2 -- declare table declare @Table2 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table2select 1 , 2 union allselect 5 , 1 union allselect 3 , 2 -- declare table declare @Table3 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table3select 1 , 2 union allselect 2 , 1 union allselect 3 , 2 -- declare table declare @Table4 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table4select 5 , 2 union allselect 2 , 1 union allselect 10 , 2 /*Is there anyway I can write one sql query which will give me the following result:IDs = All unique ID from all tablesSumOfAllValueFromAllTables =for the same ID, value from Table1+for the same ID, value from Table2 + so on ...IDs - SumOfAllValueFromAllTables===============================1 - 62 - 33 - 45 - 37 - 210 - 2*/--I have tried by the following way, but for more tables, it is becoming complicated, because I have 12 tables.--Is there any better way to do it? Thanks for the help.select coalesce(t1.ID,t2.ID) as IDs, coalesce(sum(t1.value),0) +coalesce(sum(t2.value),0) as SumOfAllValueFromAllTablesfrom @Table1 t1full join @Table2 t2 on t1.id=t2.idgroup by t1.ID,t2.IDorder by IDs
I have a search form that takes 5 inputs from textboxes or Drop Down Lists.
I built a query that works just fine in Analyizer, but the second i try to turn it into a sproc that takes parameters I can only get it to work with one parameter so far....
' Inner Join ftr_location On ftr_location.loc_id = ftr_file.loc_id' + ' Inner Join ftr_Status On ftr_status.status_id = ftr_file.status_id' + ' Inner Join ftr_doc_types on ftr_doc_types.file_type_id = ftr_file.file_type_id' + ' WHERE 1=1 '
If @ResultLocation Is Not Null Set @SQLString = @SQLString + 'And ftr_file.loc_id=' + cast @ResultLocation If @ResultLocation Is Not Null Set @SQLString = @SQLString + 'And ftr_file.file_date=' + cast @ResultFileDate
EXEC (@SQLString) GO
Any feedback would be most helpful, as I can get it to work with one parameter but not when I add teh second in the mix.
I have a dataadapter on my asp.net page and am having issues passing values to the sql statement. See below:
SELECT tblTemp.SensorID, tblSensor.SensorNum, tblTemp.TempTime, tblTemp.TempVal FROM tblTemp INNER JOIN tblSensor ON tblTemp.SensorID = tblSensor.SensorID WHERE (tblTemp.TempTime BETWEEN @From AND @To) AND (tblTemp.SensorID IN (@Sensor)) ORDER BY tblTemp.TempTime
@Sensor is populated in vb with the selected values from a checkboxlist control. I attempted to test this in the "Data Adapter Preview" window, but continually get an error. Is there a way to pass multiple values to a parameter? Any help would be appreciated, thanks!
I have an app that is critical to our business. It handles and syncronises several SQL Servers, checks integrety etc. I need to make the app so it can run a few things at once. Does anyone have any experience with this? Currently we use Delphi and ADO. I have been fiddling with DMO to get more performance - I am not sure ADO is very quick for some I tasks I need to do.
I suppose my main question *really* is does ADO/DMO multi-thread and has anyone tried it. If not how do people do it?
I and trying to get some help with Multi-Value parameters in Reporting Services 2005 in VS 2005. I’m new to the product and struggling with the TSQL syntax.
(I have simplified my SQL statement for the purposes of this question) I have a dataset below which has 1 parameter;
="SELECT * " & " FROM dbo.Table" & Iif(Parameters!Sex.Value = "ALL",""," AND dbo.Table.Sex = '" & Parameters!Sex.Value &"'" )
The Sex parameter dataset contains F, M, U and ALL – the above parameter allows me to select on any of the 4 options. I would like to use the multi-value parameter but am struggling to grasp the syntax. Believe me I have tried a few things.
Could someone provide me with an idiots guide on how to make my basic parameter a multi-value parameter?
I have a SQL2k5 table capturing transaction via an online vendor application. I need to join this data with cooresponding data that exist within a 2003 MS Access .MDB application. I've had recent training in SQL Serv 2005 T-SQL and programming. This is what I'm planning but not if this is the best/simple plan.
write After Insert trigger for SQL tbl to invoke a sp write After Insert trigger for SQL tbl to invoke a sp (OK with this) write a sp to select last inserted record from SQL tbl (OK with this) sp will use pk_col to pull data from linked MS Access 2003 using OPENQUERY or four part naming (is one way better than the other?) insert Parent record into MS Access parent tbl (after insert, how can i get Parent Key from this insert to use as Child FK col? will @@identity work?) insert Child record into MS Access child tbl (how can i get Child Key from this insert to use as GrandChild FK col? @@identity?) insert GrandChild record into MS Access GrandChild table
any help greatly appreciated! Thanks for being there....
Greetings - I see a few postings regarding problems regarding multi-value parameters, and see no solution responses.
Am attempting to filter on data using a multiple values drop-down parameter. Dataset has been created and runs correctly. The Multi-value parameter setting has been checked. When report is run and 1 value selected from drop-down, report runs fine. If multiples (or all) are selected, then receive "Query execution failed for dataset "mydataset" incorrect syntax near ','" error.
Can someone please advise a solution to this? Tks & B/R
