Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Multiple Servers

Author  Topic 

notmyrealname

98 Posts

Posted - 2009-04-14 : 08:38:31
Hi.

I am developing a VB.Net application that manages production information that is stored within two SQL database on the same server. The IT department would like to split up these databases and place one on its own server while leaving the other one where it is. This will create a server gap that i will need to bridge in order to read from both databases. IT suggested that the solution is to use a "stateless connection" with an "unbound control". They think that this is a better approach than using a Linked Server or Server Group (possibly for security reasons?). I have researched "stateless connections" online and cannot see how it applies to the multiple server problem in question.

Could someone please shed some light on this matter and let me know if this "stateless connection" and "unbound control" option seams viable?

Thanks.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 09:37:48
By stateless connections, do you mean as in stateless connection pooling? It has nothing to do with the situation. The better approach is Linked Server.

Also, if your application is properly designed, this should not be a problem. You should be using a data access utility to query data that connects to a SQL server based on a web.config key.

For an example:
SqlDataReader reader = DataAccess.GetReader("sqlServer1", "SELECT * FROM [table];");
and
SqlDataReader reader = DataAccess.GetReader("sqlServer2", "SELECT * FROM [table];");

sqlServer1 and sqlServer2 should be defined under Data Connections in web.config OR app.config (if it's a windows app).

Go to Top of Page

notmyrealname

98 Posts

Posted - 2009-04-14 : 10:06:34
Hi Whitefang.

Thanks for the reply.

Unfortunately i don't know what is meant by "stateless connection" either.

As far as the application being "properly designed"... I thought it was. The difference is that i'm not performing any queries in VB. All of my queries are stored in the database itself as stored procedures. I just connect to them with VB.

Initially i was on the fence between writing my queries in VB versus writing them in the SQL database itself. Do you think it's better to query in VB?

I have two databases that i need for this app. One is a third party database that I am only querying. No changes can be made to this database. The second is my own database where i have a bunch of tables, views, sp's and functions. These sp's and function get data from the other database. The problem is when these two database are placed on separate servers my queries will no longer function. I can't simply add the server name to the path. Is there any simple way to modify my queries to get data from the other database if it resides on a different server? I suspect this is when i have to create a linked server. My hands are kind of tied now and i don't think i will be allowed to use linked servers but i would still like to hear about this option. I'm supposed to use a "stateless connection" (per IT instructions) but i don't know how that applies. Does anyone have more info on how a "stateless connection" could help?

Thanks for your help.
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 10:26:17
The fact that they told you to use stateless connections with unbound controls means they think you have written your queries in VB (application layer). All it means is that the connection does not keep state (track of certain stuff) and returns to a connection pool when you close it. It's meant for short connection times. Unbound controls are controls that are not directly linked to the database so if you edit an unbound control, the change won't reflect in the database unless you manually update it using a query etc.
Because your queries are written in the database, and because the queries get data from another database, this is not possible without heavy modifications (stateless connections and unbound controls are on the application side, not the database side).

It is better to write queries in the application layer. Writing everything as a stored procedure is as outdated as Hungarian notation. Stored procedures are meant for very complex queries that need to be compiled or for large data processing operations.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-14 : 10:51:49
quote:
It is better to write queries in the application layer. Writing everything as a stored procedure is as outdated as Hungarian notation. Stored procedures are meant for very complex queries that need to be compiled or for large data processing operations.


Since when? Got a source for this, or is this your opinion?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 11:04:01
Having dealt with multiple enterprise applications and legacy apps....I have come to the conclusion that SP should be reserved for complex operations.

Try maintaining a very large enterprise application with 1000 SP ALL beginning with the name "msp". Not only that, each time you modify a SP, the SQL "browser" resets the cursor location so you have to find the SP again from the list of 1000 SPs. I'm sure you can get a better SQL manager than the default client but still, managing 1000 SPs is ridiculous.

In a properly architectured application, the only need to have SP would come from data complex operations. I have written many enterprise apps with 2-tier architecture and NOT ONCE have I needed to use SP for anything other than very large data processing operations. I can modify an object entity without worrying much because of the superior IDE and reflection capability, something you cannot get from SPs (sure, you can do a dependancy check, but is that enough?)
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-14 : 14:15:44
That is your opinion then. I will stick with SPs myself. Much more secure, It can reuse execution plans, I don't have to recompile my application if there is a change. Sending SQL code over the network just does not sit well with me.

Oh, and I have / do maintain a couple "very large enterprise applications". Scrolling through names of procs does not take that long. I can live with that.

Here is a nice short article on SP vs Inline.
Yes, there are hundreds of VS articles on this subject, I just thought this one was nice and succinct.





[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-14 : 14:28:33
whitefang, so does this mean if you have to make changes to your queries you will be redoing the front end?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 14:31:41
It's all debatable really. Good luck debugging and making modifications when a major change is requested by the client when you have to figure out how several SPs interact in a group of 1000 other SPs. Good luck deploying those changes and keeping reference to as WHICH SPs were modified.
Good luck hoping that you don't get any runtime exceptions because you couldn't compile and run QA like you could with an application.

More secure - Not really. The application still contains the login credentials to the DB.

Reuse execution plans - Benchmarks have shown over and over that there is no performance difference on dynamic sql vs cached SQL on the majority of the queries. Specialized queries that process large amounts of data benefit from SPs, that's the only case.

Sending SQL over the network - This is just a excuse for poor network security.

I'll close with this note: There is a common trend in IT staff using the "working harder instead of working smarter" mentality here. SPs are good if you are writing your "enterprise" application using notepad.
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 14:34:37
quote:
Originally posted by yosiasz

whitefang, so does this mean if you have to make changes to your queries you will be redoing the front end?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion



In all my applications, I model the database as closely as possible after the application architecture. If I need to make a change to a query, I make the change in the class file, recompile the app, and deploy the DLL (I'm using .NET). I get compile time protection in most cases.....not possible with SP.

Because of the way the application is "architectured", a change to the database query would mean the client requested a change to the application functionality. App gets compiled everytime regardless whether using SPs or not.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-14 : 14:47:31
Well, good luck figuring out your code and how several pages interact with one another, and several functions interact in a group of 1000s of others. Good luck keeping track of which ones you changed.

Just take your statement, and replace "SP" with your inline code. Same thing applies. I can use TFS to store and track my SPs. I don't have to recompile anything to make a change (Unless its a major change, and then its not going to be a 5 min change anyways).

You are talking like an application developer, not a database admin/dev.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

jhermiz

3564 Posts

Posted - 2009-04-14 : 15:02:24
quote:
Originally posted by whitefang

quote:
Originally posted by yosiasz

whitefang, so does this mean if you have to make changes to your queries you will be redoing the front end?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion



In all my applications, I model the database as closely as possible after the application architecture. If I need to make a change to a query, I make the change in the class file, recompile the app, and deploy the DLL (I'm using .NET). I get compile time protection in most cases.....not possible with SP.

Because of the way the application is "architectured", a change to the database query would mean the client requested a change to the application functionality. App gets compiled everytime regardless whether using SPs or not.



I definately DO NOT agree with Whitefang.
You got it wrong my man...if you are writing inline SQL I don't even want to see your code. Sprocs all the way...

DonAtWork is correct..no opinions should be made on this topic :).

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 15:10:18
quote:
Originally posted by whitefang

[quote]Originally posted by whitefang

[quote]Originally posted by DonAtWork

Well, good luck figuring out your code and how several pages interact with one another, and several functions interact in a group of 1000s of others. Good luck keeping track of which ones you changed.

Just take your statement, and replace "SP" with your inline code. Same thing applies. I can use TFS to store and track my SPs. I don't have to recompile anything to make a change (Unless its a major change, and then its not going to be a 5 min change anyways).

You are talking like an application developer, not a database admin/dev.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp




I use Visual Studio to manage my application. I don't put SQL, I don't put Architecture-based code, I don't put global variables in ANY OF my .NET pages. That's what the n-tier architecture is for. The .NET pages just contain the UI and UI based events. Only a UI change would require page modification. Functionality change would be in the object model, not pages (except UI func). I know exactly how entity objects interact thanks to UML diagrams directly within the IDE. I also have access to attributes, comments, and other superior documentation capabilities.

In my opinion, using SPs to implement application logic is going back to primitive times, you might as well use Pascal and Notepad.
Go to Top of Page

jhermiz

3564 Posts

Posted - 2009-04-14 : 15:10:33
quote:
Originally posted by whitefang

quote:
Originally posted by DonAtWork

Well, good luck figuring out your code and how several pages interact with one another, and several functions interact in a group of 1000s of others. Good luck keeping track of which ones you changed.

Just take your statement, and replace "SP" with your inline code. Same thing applies. I can use TFS to store and track my SPs. I don't have to recompile anything to make a change (Unless its a major change, and then its not going to be a 5 min change anyways).

You are talking like an application developer, not a database admin/dev.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp




I use Visual Studio to manage my application. I don't put SQL, I don't put Architecture-based code, I don't put global variables in ANY OF my .NET pages. That's what the n-tier architecture is for. Only a UI change would require page modification. Functionality change would be in the object model, not pages (except UI func). I know exactly how entity objects interact thanks to UML diagrams directly within the IDE. I also have access to attributes, comments, and other superior documentation capabilities.

In my opinion, using SPs to implement application logic is going back to primitive times, you might as well use Pascal and Notepad.



Not a good comparison at all.


Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 15:14:00
I'm maintaining a legacy app that another team worked on. They have 2000 stored procedures, some are obsolete, some are duplicates, some are just ridiculous. This on top of the 2800 sql views. There is a 800 page documentation book. The interact with each other that modifying one could cause a chain of errors. I got no compile time checking. There is no UML diagrams. There is no IDE. While modifying, I have to keep track what I modified.

They said that it started out as a neat project, overtime....slowly...this is the end result. Would never happen if they had a object model architecture.
Go to Top of Page

jhermiz

3564 Posts

Posted - 2009-04-14 : 15:20:28
quote:
Originally posted by whitefang

I'm maintaining a legacy app that another team worked on. They have 2000 stored procedures, some are obsolete, some are duplicates, some are just ridiculous. This on top of the 2800 sql views. There is a 800 page documentation book. The interact with each other that modifying one could cause a chain of errors. I got no compile time checking. There is no UML diagrams. There is no IDE. While modifying, I have to keep track what I modified.

They said that it started out as a neat project, overtime....slowly...this is the end result. Would never happen if they had a object model architecture.




Dont blame the sproc blame the developers...no one ever said they should not have diagrams, object entities, etc all defined.
That is more laziness then SPs being bad.

If you consider having 2000 sprocs bad thats not a good assumption.
if they are the same sproc or sprocs that are similiar that did not consider parameters or dynamic order bys then sure thats bad. But there are plenty of reasons to have A LOT of stored procedures.

Please don't assume because someone takes advantage of how GREAT sprocs are that they are a bad idea, quite the opposite. Naming convention and number of procedures is not an excuse for hating sprocs.

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 15:26:40
SPs are good for complex stuff. In the legacy app, they use SP for EVERYTHING. Even for a simple query, they use SP. I seen this in numerous applications. The topic poster is using SPs for simple stuff, that is what I recommended against.

Also, the same can be said for n-tier apps. I saw a small "contact/about us" of a website use a 3-tier architecture. That's worse than using SPs!
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-04-14 : 15:30:40
quote:
Originally posted by whitefang

Having dealt with multiple enterprise applications and legacy apps....I have come to the conclusion that SP should be reserved for complex operations.


Yes, that is the opinion of the developers in my office too. Their opinion is that no business logic belongs in the database layer (unless we can't figure out how to do it ourselves).
Too funny. Whitefang, your foolishness is showing. Datalogic belongs in the database layer. Interaction logic belongs in the application layer. You spend your time deciding what color to make the dialog boxes and let us worry about ensuring that all the accounting calculations are consistent.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 17:38:29
quote:
Originally posted by blindman

quote:
Originally posted by whitefang

Having dealt with multiple enterprise applications and legacy apps....I have come to the conclusion that SP should be reserved for complex operations.


Yes, that is the opinion of the developers in my office too. Their opinion is that no business logic belongs in the database layer (unless we can't figure out how to do it ourselves).
Too funny. Whitefang, your foolishness is showing. Datalogic belongs in the database layer. Interaction logic belongs in the application layer. You spend your time deciding what color to make the dialog boxes and let us worry about ensuring that all the accounting calculations are consistent.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________




Your inexperience is clearly showing. "The interaction logic" is part of the UI is the application front-end (1st tier, handles UI, controls, etc). The architecture of the application is separate (business layer, 2nd tier, handles all business related logic). The database layer which handles all CRUD to the DB is another separate layer (3rd tier, handles persistence and caching). Microsoft themselves rolled out their enterprise data utilities because this is the modern/better way of designing applications.

No type of business logic should exist in the database, that is a disaster waiting to happen. A database is nothing more than a datastore. If you store all that crap in the DB, imagine the pain when it comes to migration and upgrading.



Go to Top of Page

notmyrealname

98 Posts

Posted - 2009-04-14 : 20:09:52
Hi Whitefang.

Me again. The one who posted this thread in the first place.

Let me just thank you again for helping out. I apologize for all the abuse you had to take for it.

Honestly, your response about not using sp's wasn't what i wanted to hear because that's exactly what i did. But i'm not an idiot. It's not like i was going to change everything just because you told me to. I welcome opinions. You be hard pressed to find any topic that has a completely definitive answer.

I have to admit that i was really happy to hear that my way is right and you are just crazy!

Thank you everyone for all of your extremely helpful opinions.
Go to Top of Page

jhermiz

3564 Posts

Posted - 2009-04-15 : 07:41:34
quote:
Originally posted by whitefang

quote:
Originally posted by blindman

quote:
Originally posted by whitefang

Having dealt with multiple enterprise applications and legacy apps....I have come to the conclusion that SP should be reserved for complex operations.


Yes, that is the opinion of the developers in my office too. Their opinion is that no business logic belongs in the database layer (unless we can't figure out how to do it ourselves).
Too funny. Whitefang, your foolishness is showing. Datalogic belongs in the database layer. Interaction logic belongs in the application layer. You spend your time deciding what color to make the dialog boxes and let us worry about ensuring that all the accounting calculations are consistent.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________




Your inexperience is clearly showing. "The interaction logic" is part of the UI is the application front-end (1st tier, handles UI, controls, etc). The architecture of the application is separate (business layer, 2nd tier, handles all business related logic). The database layer which handles all CRUD to the DB is another separate layer (3rd tier, handles persistence and caching). Microsoft themselves rolled out their enterprise data utilities because this is the modern/better way of designing applications.

No type of business logic should exist in the database, that is a disaster waiting to happen. A database is nothing more than a datastore. If you store all that crap in the DB, imagine the pain when it comes to migration and upgrading.







Ok whitefang you need to relax, first of all you are trolling.
I don't agree with you on anything you've said so far. And migrations or upgrades for us has been very smooth considering the improvements in sql 2005 and sql 2008. I'd hate to have to deal with fixing your code and redploying it to 1000 end users. Keep doing what you are doing, its people like you that keep us in business :).

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page
    Next Page

- Advertisement -