Entries:
Comments:
Posts:

Loading User Information from Channel 9

Something went wrong getting user information from Channel 9

Latest Achievement:

Loading User Information from MSDN

Something went wrong getting user information from MSDN

Visual Studio Achievements

Latest Achievement:

Loading Visual Studio Achievements

Something went wrong getting the Visual Studio Achievements

Anil Nori

Anil Nori Anil Nori

Niner since 2006

  • 'SQL Everywhere Edition' - What. How. Why.

    Anil Nori wrote:
    
    crifo wrote: Hi Anil,
    I think that you can help me in this field to you congenial.
    I must to realize a database application for an Automation system consisting of a Industrial PC with Operative System microsoft windows embedded.

    1° question: Is Microsoft sql server 2005 everywere compatible for Microsoft windows embedded?
    (I ask this because in specific of its is not clear)

    2° question: In the case that it's compatible,  if I instal it in a industrial Pc can it to act by database server or necessity of a remote server?

     


    I guess you are asking does SQL Server Everywhere Edition run on Windows Eembedded platforms?

    1. I believe SQL Server Everywhere runs on embedded platforms, for example on XP/Embedded. However, currently it is not certified on the embedded platforms. That mean, currently it is not supported on embedded platforms officially

    2. I did not quite understand the 2nd question. Are you asking if it runs as a server -- the answer is no.

    Can you clarify your questions?

    anil


    Looks like I mis-spoke about the SQL Server Everywhere Edition support on XP/embedded. I believe we do certify it and support it. So, the good news is it will be supported on XP/e when SSEv ships later in the year.

    However, the usage is still as a embedded in the application. That is, it cannot be used as a service.

    Hope this clarifies the XP/embedded support issue

    anil
  • 'SQL Everywhere Edition' - What. How. Why.

    crifo wrote:
    yes Anil and sorry for my poor language,


    I'm asking do:
    run SQL Server Everywhere Edition on Windows XP Eembedded platforms?

    &

    runs it as a server(database_server)?

    Thanks
    Crifo


    Dear Crifo

    As I said in my previous response, I believe the Everywhere Edition will run on XP Embedded. However, we have not formally certified and providing support. But it should just work

    As for running as a server, it is not intended to run as a server. It would be interesting to understand your scenario further

    thanks
    anil
  • 'SQL Everywhere Edition' - What. How. Why.

    crifo wrote:
    Hi Anil,
    I think that you can help me in this field to you congenial.
    I must to realize a database application for an Automation system consisting of a Industrial PC with Operative System microsoft windows embedded.

    1° question: Is Microsoft sql server 2005 everywere compatible for Microsoft windows embedded?
    (I ask this because in specific of its is not clear)

    2° question: In the case that it's compatible,  if I instal it in a industrial Pc can it to act by database server or necessity of a remote server?

     


    I guess you are asking does SQL Server Everywhere Edition run on Windows Eembedded platforms?

    1. I believe SQL Server Everywhere runs on embedded platforms, for example on XP/Embedded. However, currently it is not certified on the embedded platforms. That mean, currently it is not supported on embedded platforms officially

    2. I did not quite understand the 2nd question. Are you asking if it runs as a server -- the answer is no.

    Can you clarify your questions?

    anil
  • 'SQL Everywhere Edition' - What. How. Why.

     

    I have been meaning to post a reply on store procedures but did not get around to doing it. I think the discussion on Stored Procedures (sprocs) is somewhat “religious.” I am sure there are some who want to use sprocs for most of their application logic and there is also huge trend where much of the logic is outside the database (so use sprocs in a specialized way).  Here are some of my personal thoughts.

                    

    Prior to Microsoft, I have done both database server development and as well as multi-tier application development. My views on application logic development have evolved over time.

     

    Stored procedures really came into play during the Client – Server days. Sybase is probably the first one to introduce T-SQL procedures in late 80s. Oracle followed it with PL/SQL and DB2 with DB2 Stored Procedures. The main purpose was to push logic into the database - -in fact, that was the only place you could place it because clients were not rich clients.  This happened after the TP monitor-era. As the DBMSs got rich in functionality, most of the TP monitor capabilities were subsumed by the DBMS. Until Oracle Applications Release 10.7 (even may be Release 11), most of the application logic is coded as PL/SQL code running in the Oracle RDBMS.

     

    Application logic cab partitioned into:

    1.      Data-centric logic (including constraints, validations, aggregations, etc),

    2.      Business process logic,

    3.      Presentation logic.

     

    How much of the different types of logic is hosted in stored procs (in the database) depended on the specific database system and the application. For example, earlier Oracle applications placed all the logic (data, business process, and presentation) in stored procedures.  The database server was the hosting server for apps; there is probably couple million lines of PL/SQL code running in the database server.  Even the forms were generated in the DBMS (like HTML pages being generated in the web servers). Oracle workflow engine ran in the DBMS and modeled and interpreted the business processes.  Note that not necessarily all the logic was related to data.  This is partly due to the richness of the PL/SQL language itself (it is pretty much a full-fledged modern block-structured programming language).  Such 2-tier architecture, while my look simpler, has its limitations both in application scalability and in application portability. As the applications got complex and the number of users increased, DBMSs had to be installed on bigger and expensive boxes. Also porting the apps to other databases became a problem; of course this was not a consideration for Oracle applications.  BTW packaged applications like SAP, which ran on multiple DBMSs, coded most of the business process and presentation logic in ABAP and hosted it in the mid-tier.  SAP wanted to be database agnostic; therefore, used very little of the vendor-specific capabilities. However, for performance reasons, SAP does host large number of stored procedures in the DBMS. 

     

    Early to mid 90s saw the emergence of the browser, application server and multi (3) –tier applications. By late 90s, Java and .NET application server became very common. In the 3 –tier app stack, the business process and presentation logic moved to the app server tier. Database hosted the data and the really data-centric logic (as stored procedures) and the browser provided client interaction via HTML. Note that the HTML pages were still generated in the application/web server using ASP/JSP technologies. The key take away is some of the stored procedure logic moved to the mid-tier. In fact, even Oracle apps (11 and 11i) started moving to 3-tier architecture – starting with presentation logic in the mid-tier to more business process logic in the application server. 

     

    What followed the app server era is the web services and application integration era. Web services allowed data and business processes to be composed into richer and flexible applications. This led to the generalization of the application stack to Service Oriented Architecture (SOA), where data and the data-centric logic could be hosted in different service boundaries.  Data as the authoritative source resided in some back-end database or application but caches (or copies) of the same data could reside in other service tiers, as reference data. For example, consider a Product Catalog browsing application, where Product data may be coming from one or more backend apps but the integrated product data could be cached in the mid-tier for performance and scalability reasons. With large amounts of cached data, applications demanded rich data processing on the cached data – like key based access, filtering, reporting etc.  In was unacceptable and inefficient to push the data into a traditional relational database, just to query data or to run stored procedure logic. In such architectures, even the data-centric logic could not be locked inside the DBMS.  These applications require rich data access and management capabilities on the data that is part of the application (physically in the same process). This has lead to the emergence of in-memory/embedded DBMSs.  An interesting and relevant question is whether stored procedures make sense in these embedded/in-memory DB environments, where data co-resides with the application logic. The applications can write even the data-centric logic in an abstraction that is close to the host programming environment (e.g. Linq) and run on the data residing in the application.  This is no longer needs to be stored procedure logic.

     

    More recent phenomenon is the emergence of Web 2.0, where even some of the rich presentation (generation) and web service integration (mash-ups) is occurring in the browser. The trend is to move logic, therefore data, even closer to the client.  This fits-in with SOA apps, where the clients (e.g. browser) become “edge” applications.  As these edges can be anywhere (e.g. on the desktops, devices), the need for disconnected and occasionally connected “edge” applications is increasing. Every app wants to become like an Outlook app with Cached mode, where users interact with the application the same way whether the app is connected to or disconnected from the backend data sources.  This requires rich data processing capabilities in the clients now. Again, what does it mean to have stored procedures in these clients?

     

    I have described the structure of the multi-tiered application logic and the role of stored procedures in such environments. I happened to believe that stored procedures would be scoped primarily to the database servers, capturing data-centric logic for performance reasons. A class of such stored procedures includes specialized user-defined functions (UDFs) and user-defined aggregates (UDAs). It should be noted that embedded and in-memory DBs also need to provide some mechanisms to support user-defined aggregates. This could be achieved by registering application defined aggregates with the database so that they can be invoked on a per row basis.

     

    Another reason why people want stored procedures is to provides encapsulation for some application logic and require the same encapsulation to run on all tiers. For example, the desire to run the same T-SQL stored procedures (app logic) on SQL Server on the backend as well as in SQL Server Everywhere in the mid and client tiers. This is the down-sizing requirement – that is, requirement to run the server-side logic on the client tiers. I am nor sure how practical this is but I believe this by far the most compelling argument in favor of stored procedures in SQL Server Everywhere and in all tiers. For SQL Server Everywhere, we have to weigh this requirement (it benefits) with the negative impact it will have on the footprint and the complexity of SQL Server Everywhere. Some replies here suggested compiling all DB logic to some IL and supporting such IL on all tiers. This is an interesting thought and requires further investigation.

     

    In summary,

    •          I believe stored procedures are definitely useful and will continue to be critical for data-centric logic in DB servers.

    •          Multi-tiered architectures will host more logic outside the database; the benefits of stored procedures outside the back-end database tiers is not very clear

    •          Currently the requirements for small footprint and the embedded-ness of SQL Server Everywhere outweigh the stored procedure support requirement.

     

    Anil

  • 'SQL Everywhere Edition' - What. How. Why.

    raptor3676 wrote:
    

    So,
    Store them in: the "Storage Engine"
    Analyze them in: the "Query Analyzer"

    Which means, Storage Engine without the Query analyzer (or the other way around): No stored procedures, Period!! at least as we know'em.

    The point is, offer the option.

    Raptor

    PS: I'd like to know who wants to use SQL/e with out the Query Analyzer and the T-SQL subset it provides, which really the single MOST important feature of it.



    First to make sure we all are on the same page, SQL Server Everywhere (SSEv -- btw, this is the preferred acronym), does support Storage enginge and T-SQL Query Processor. It also supports Query Analyzer. You can use SSMS (management studio) and look at the query plans. SSEv is well integrated with SSMS.

    Yes, SSEv supports only a subset of T-SQL but a rich SQL subset. What we do not support is the procedural T-SQL (stored procedures) and that is what seem to be the issue. While store procs are interesting they are not a show stopper.

    There is huge interest in SSEv internally and externally. Several Windows apps will be using SSEv technology in Vista. Currently, most customers are using it on devices but many are interested in running these apps on the desktop as well.

    BTW, there many apps which just want to use the storage engine. They just want key based access like in ISAMs. AD (Active Directory) is a good example which just uses the Jet storage engine. It does not use the query processor; Exchage is another example. So, most of the Jet usage is just using the storage engine.

    Again, I go back to the componentization arguments. We provide the storage engine and the QP; app can chose what it wants to use.

    anil
  • 'SQL Everywhere Edition' - What. How. Why.

    Reponse to Richard Rudek's post........

    Richard

    You mail and questions touch on multiple aspects: partitioning, sync, and stored proc. Let me express my thoughts on the first two in this response. I will cover stored procs in a separate replay, as it has wider interest and broader impact.

    Richard, I think your thoughts on partitioning are consistent with distributed applications but SQL Server 2005 partitioning is too low level and at a physical abstraction. Besides table partitioning in commercial DBMS is primarily provided to support high availability and scalability. Most DBMSs provide a single table abstraction and seldom support direct operations on partition (the exception is in Tandem's NonStop SQL, which provided partition level autonomy). Except for some physical admin operations, most of operations are at the table level. Therefore, such partioning does not really help build SOA (service oridented architecture) apps.

    However, the general idea of partitioning data and the logic is a scalable way to build large applications.

    As for sync, data sync is critical in building distributed apps. BTW, I distinguish between synchronization and replication. Synchronization is between any data sources -- i.e the participating data sources can be heterogeneous. Replication is homogeneous synchronization -- i.e. the participating data sources are same (or subsets). While data sync is is practical, logic sync is complex and not pragmatic. That is, synchronizing the logic, whenever data is synchronized, that is associated with data. While logic associated with data can be propogated when the data is synchronized, it is realistic in a highly controlled and homogeneous environments. This is fundamentally contradictory to loosely connected, distributed, SOA based applications. Most loosely coupled and disconnected applications do not requie symmetic logic and synchronization of locig. Since SSEv (SQL Server Everywhere Edition) focus is "edge" apps in the loosely couple application architecture, SSEv does not pursue logic synchronization.

    Now let's get to the crux of Richards mail -- "To sproc or Not To sproc." This I want to continue in a separate mail

    Thanks

    Anil

  • 'SQL Everywhere Edition' - What. How. Why.

    Yes, there have been other small footprint SQL databases. SQL Server Everywhere is different from Derby (Cloudscape) in couple of ways: 
       - SQL Server Everywhere is componentized -- the storage engine, the query processor, API providers, and the sync components are separate DLLs. So, it is possible for an application to choose just the storage engine without taking the other components. This provides extensibility -- in fact, you could right your own query processor on top of the storage engine

       - SQL Server Everywhere runs on the devices as well as on the win32 platforms. I am not sure if Cloudscape runs on devices

       - SQL Server Everywhere provides flexible sync components. Sync is an important part of the Everywhere scenarios

       - SQL Server Everywhere applications can easily scale up to server apps (to SQL Server) 
  • 'SQL Everywhere Edition' - What. How. Why.

    As I replied to some other mail, it is possible for multiple apps to share the same database (.sdf file). SO, there can be n:1 relationship between the apps and the database. The key is it is a single user database (but can be accessed by multiple apps).

    Yes two different two databases can be synchronized. Currently, SQL Server Everywhere supports a Hub and Spoke sync model. That is, several Everywhere databases can sync to a server DB (e.g. SQL Server). SO, it is possible for two .sdf s to sync via SQL Server.

    In future, we do plan to support Peer-to-Peer sync, where two Everywhere DBs can synchronize with each other.

    anil
  • 'SQL Everywhere Edition' - What. How. Why.

    SQL Server Everywhere is a relational store and does not have the file system integration like WinFS did. Yes, it does enable some of the WinFS scenarios. BTW, you can store data from multiple applications in the same .sdf file if they are created by the same user. That is, you can run multiple applications accessing the same .sdf file. The difference is the database (dlls) are embedded in the apps.

    Yes, we are exploring XML support. Do you want to just store XML docs or do you also want to query (e.g. using XPath, XQuery)?