Limits In OLTP
Aug 1, 1998Is it possible / advisable to use Sql Server as the backend database for handling 40 million transactions,
taking around 8 GB space?
ADVERTISEMENT
Oltp && Olap
Dec 19, 2007hello everyone,
Is it make sense to create 2 databases, OLTP for the insert, update and delete and OLAP for selection
and i'll sync between the 2 databases.
Thanks
Please Help With Oltp Solutions
Aug 28, 2007hi. i dont understand what they mean when they say developing oltp solutions. can anybody pls explain it to me. also does anyone know what ways there are to develop sql oltp solutions using SQL 2005 reporting services, OLTO, Excel Services. as well as any good tutorials for it?
thanks for the help.
OLTP Vs Decision Support
Mar 2, 2004Whilst on the Nth hour (n = many) of my magical journey through MS Sql BOL I've come across OLTP Vs Decision Support. After a couple of searches here could someone shore up the following for me please...
A decision support database is the same as warehouse database.
This is for static data commonly used for reporting and analysis.
OLTP is a live database (accomodates inserts, deletes, updates etc).
Is that right?
Also would it be fair to assume that a decision support database is generally going to be spawned from the historical data of an OLTP database? Any real world examples of these two terms would be greatly appreciated too.
Cheers
Dan
ETL: OLTP -&> DATA Store
Jul 23, 2005Greetings All, I was wondering if any of you would share some of yourexperiences regarding the task of loading a data store from an Oltpsource. We are using Analysis Services in a BI product that requiresdata to be pulled from one of our products, an OLTP database. Thedesign is to first run an ETL process from the OLTP source into anoperational data store, from here Analysis Services will pull its datato do its thing. Now, for small OLTP databases (< 1Gb) the storedprocs I have written to do the extraction works well, it is relativelyfast and efficient. However, we have a few databases that are 10Gb'sand the load could end up taking several hours. During this long loadthe OLTP source may be in use and I want to avoid write blocks, or if Iwere to use "select ... NOLOCK" I could get dirty data brought over. Icould used BCP for some of the big tables or Bulk Copy but I wanted tosee if anyone has dealt with this issue and what their specificresolution was for their specific problem. It is my hope that byseeing how others have dealt with this I will be able to architect asolution for my specific problemRegards, TFD.
View 1 Replies View RelatedWhat Are The Differences Between OLTP And OLAP ?
Mar 7, 2008I want to know the basic dfferences between these 2 (OLTP and OLAP) ?
View 3 Replies View RelatedOLTP And Reporting Databases Seperated?
Jan 12, 2006we are using an object database for our OLTP but for reporting we have
got some issues about performance as the cpu becames a bottleneck.And
we want to be able to run on low end computers...
One of our team members suggested to replicate the object database to a
SQL table.But just a single one.The most denormalized thing ever.(358 coloumns)
is this the fastest way we can get in reporting?
*we don't want harddisk,ram or cpu to became a bottleneck. ( must run on cheap staff)
ETL Architecture - Streaming Data Into An OLTP
Oct 15, 2007Interested in feedback from the SQL grand wizards (and would-be wizards) that haunt these forums.
Let's say you need to constantly stream data into an OLTP system. We are talking multiple level hierarchies totaling upwards of 300 MB a day spread out not unlike a typical human sleep cycle (lower data during off-peak, still 24/7 requirements). All data originates from virtual machines running proprietary algorithms. The VM/data capture infrastructure needs to be massively scalable, meaning that incoming data is going to become more and more frequent and involve many different flat record formats.
The data has tremendous value when viewed both historically as well as in real-time (95% of real-time access will be read-only). The database infrastructure is in it's infancy now and I'm trying to develop a growth plan that can meet the needs of the business as the data requirements grow. I have no doubt that the system will need to work with multiple terabytes of data within a year.
Current database environment is a single server composed of a Dell PowerEdge 2950 (Intel Quad Core 5355, 16 GB RAM, 2 x 73 GB 15K RPM SAS ) with an attached Dell PowerVault MD1000 (15 x 300 GB 10K RPM SAS in RAID 5+0 [2x7] w/hot spare) running Win 2k3 64-bit and SQL Server 2005 x64 Standard, 1-CPU.
I am interested in answering the following questions:
Based on the scaling requirements of the data capture and subsequent ETL, what transmission method would you find most favorable? For instance, we are weighing direct database writes via stored procedures for all VM systems versus establishing processes to collect, aggregate and stream CSVs into a specialized ETL environment running SSIS packages that load data and then call SQL Stored procedures to scrub and prepare for production import. The data will require scrub routines that need access to current production data, so distributing the core data structures to multiple ETL processing systems would be expensive and undesireable.
Cost is very important to the overall solution design. In terms of database infrastructure, how would you maximize business value while keeping cost as low as possible? For instance, do you think there is more value in an ACTIVE/ACTIVE cluster (2 x CPU licenses) where one system acts as ETL and the other as OLTP or would you favor replication of production data from ETL to OLTP or (vice-versa). With the second scenario, am I mistaken in thinking we could get away with a Server/CAL licensing model for the ETL server?.
Are there any third party tools that I should research that would greatly aid me here?
I appreciate all feedback, criticism, and thoughts.
Best Regards,
Shane
Need Info Pls: How To Convert OLTP Db To OLAP
May 21, 2008
Hi al,
I need some steps to create OLAP DB .
Actually i have OLTP Db . i created SSAS soln and created cube with necessary dimensions . i deployed it on SSAS instance of management studio.
my qn, is the instance created under SSAS instance OLAP?.
pls provide me steps to have a OLAP database...
Thanks,
Nav
DB Design :: Extracting History From OLTP?
Nov 3, 2015Simple and conventional OLTP database, we need to capture all changes for insert into a DW via staging / ods etc.
Is there a recommended approach for this? Obviously it has to be real time as there might be multiple updates for a time period. I'm thinking of triggers on OLTP tables (bad for performance as it's synchronous), or change data capture, service broker as asynchronous methods.
Writeback To The Source OLTP Database.
Apr 22, 2008I was wondering if it is possible to use SQL Server Reporting Services 2005 with the 'writeback' feature to the source OLTP database?
I have seen article's that refer to using SQL Server Analyses Services (SSAS) and writing back to the ROLAP/MOLAP database, however this is not desirable for our case.
I have almost come to the conclusion that it is not possible without SSAS.
Cheers,
SQL Server 2008 :: Large Tables In OLTP
Jul 14, 2015How many no of records of the tables are called large tables.
We are getting more deadlocks. We are using default isolation. Read & insert statements are blocking each other and causes dead locks.
I am thinking that might be purging will reduce deadlocks.
The table has 15million records. Is this table consider as large table or not in OLTP systems?
In general how many records we need to consider as large table.
Analysis :: Creating Tabular Model On OLTP
Nov 4, 2015I have been looking at implementing a tabular model based on an OLTP database that's not dimensional. I know that this is possible but during my proof of concept I have encountered numerous problems ...
The things that I have run into are: After setting up the relationships I have found that measures filter context don't propagate along the relationships as I would expect. if the measure is coming from a target table and not a source then an ALL member is returned ( as in multi dimensional when a dimension isn't related to a measure group). Given the lay out of an OLTP database this will be hard to avoid.
One thing I have done to try an mitigate the above problem is to combine the tables used for measures in a view and using that in the source to connect to the rest of the tables. however due to the tables being of different grains this has then created duplication in some of the keys and measures. so the keys cant be used in relationships and the measures aren't accurate.
Are these things other people have come across? or should I give up the ghost and just recommend using dimensional models for the source? is tabular just geared towards a DW the same as multidimensional?
Mirroring OLTP DB With Transactional Replication To Staging DB
Mar 12, 2007I want to create a mirrored DB set for data entry in a extremely busy OLTP DB. I want to add transactional replication between the production server and a staging server outside my quorum that I will use to index the data and prepare it for reporting and warehousing purposes.
If/when fail-over takes place, what happens to my transactional replication between the former production sever (now presumably offline) and my staging DB? Does it switch to the new production server automatically or do I have to manually set the replication between the new production server and the staging DB?
Thanks in advance.
Best Backup/restore (OLTP/OLAP) Practices In 2005
Aug 14, 2007We have a live OLTP database for which we create full backups every week and differential backups every day. Recently we added an OLAP database, which we need to update daily with changes from the live database.
This is the process we are planning to use.
1. Restore last full OLTP backup.
2. Apply the last differential OLTP backup.
At this point we should have a replica of the live OLTP database.
3. Update OLAP database based on the OLTP replica database.
4. Delete the OLTP replica database.
Two questions.
1. If different from the process above, how is this OLTP-to-OLAP transformation typically done in the industry?
2. What is the best way to implement this process with SQL Server 2005?
Thanks.
Data Warehousing :: Can Use Dimensional Model For OLTP System?
Jul 9, 2015Question: Is it feasible to use a star schema dimensional model for an OLTP system that incurs few (750 per day)Sales Orders transactions?
Background: My customer wants to replace an existing OLTP system database because it runs on Oracle and their in-house expertise is in SQL Server. Â The original database developers that designed the Oracle DB have apparently retired. Â The Oracle database has been over-normalized, to say the least. Â The number of sales orders being entered daily is small: about 500-750 per day. Â These entries are done at the five clerks' convenience, from a paper form, and are very unlikely to ever be entered in quick succession. Â Nothing else gets regularly entered into this database except for the occasional change to a customer, but new customers are very few and far between. Â
I've designed a star schema for the replacement database with the Sales Order Header and Sales Order detail table combined into a single 'fact' table, and I've introduced some duplication into dimension tables (like customer) in order to eliminate some of the joins (and confusion) that were built into the original database.
I've never tried this before. Â Is there any reason this would not or should not work?
OLTP Database Design Help For Bank's Customer Table
Aug 9, 2007
Hello,friends
1) CustomerID
2) FirstName
3) MiddleName
4) SurName
5) Title
6) Marital Status
7) Education
8) Occupation
9) Annual Income
10) Line of Business
11) DOB
12) Father Name
13) Mother Name
14) SpouseName
15) Gender
16) Email
17) MainTel
18) Home Tel
19) Passport Number
20)----------------------
21)- - - - - - - - - - -
100)-------------------
Above mentioned list is a snapshot of our customer master table ,which contain approximately 100 attributes related to a customer.
We are designing an application for banking sector (but NOT Core banking solution),for which we may need to capture variable number of addresses for bank's customer,i.e more then three types of addresses Fixed,Temporary and Communication addresses(which is generally the case with all banks).
A single address includes address1/address2/city/country/state/pincode fields.
In context of OLTP database,We have option to put multiple addresses in child table but that involves various joins at the time of data retrival and slow down the query.
As another option we can can create redundent addresses columns(address1/address2/city/country/state/pincode) in master table that will accumulate addresses if demand for more then three type addresses arises(although there is reasonable numer of extra addresses is expected, i.e 10)
Database is expected to serve the records of 25 million(approx) bank's customer,so does someone can suggest me how to maintan the balance between two approches.
DB Engine :: Unexpected Update / Delete On OLTP Database In 2005
Jun 17, 2015We had one of the major issues where one of the table on a heavily used OLTP database seems to have updated the records which were not expected.
Scenario:
We got around more than 12K contracts updated to status expired even though the expiry date is not set to be so:
for E.G : Below table has a column contract status which overnight seems to have updated the values to expired.
Even though the start and expiry date does not follow the logic for above.
We had the above working for past 3 years via a SP scheduled via SQL agent Job which Expire active contracts whose expiration date is less than today's 12:00AM.There has been no change in SP.
How can i track how it happened and what caused it?
DB Engine :: In-Memory OLTP Use With Existing Tables / Index / Procedures
Nov 10, 20151. I need to make use of in memory engine for my pr-existed develop procedures ,tables ,index. do I need and code changes for application and how to store tables /indexes in OLTP memory
Assume table index may have primary key index as well.
2. If table with one primary index and 2 foreign constraints, 3 non clusters indexed. which one able o load to memory area and how t do that.
3. In memory is lock free zone. usually locks will happpen in RDMS context . how this works without locks.
RAM Limits
Mar 3, 2004Can anyone tell me what would happen if you install more RAM on a server than SQL 2000 can handle?
We have a SQL 2000 Standard edition server that is getting a RAM upgrade and we are wondering if we exceed the 2Gb limit whether the machine will crash or SQL will just get on and use 2Gb leaving the rest for the other processes / OS etc ?
TIA
Row Limits
May 27, 1999I'm working with a table that has a very large number of rows is there a way to limit the rows that are returned from a query, for example I only want the first 100 rows of data.
View 2 Replies View RelatedOLTP Vs OLAP (Data Warehouse) As A DataSource In Analysis Services 2005
Jul 3, 2007Hello,Can I import an OLTP (Reltional DB) as a Data Source into SQL ServerAnalysis Services 2005 and then use the Cube Wizard and the new DataSource View feature to create the OLAP model ?Or do I have to first design an OLAP Data Warehouse with a Star Schemaand then import this DW as a Data Source into my Analysis ServicesProject.With SQL Server 2000 , OLAP would be the way to go..but with SQLServer 2005 , it seems as though the wizard and data source viewfeatures do half the work for you.I have an OLTP DB and am not sure which route I should take ! Anysuggestions / input would be much appreciated.Thanks in Advance...RegardsRusszee
View 1 Replies View RelatedMS SQL MSDE Limits
Oct 23, 2004On MS SQL Server MSDE what is the limit for a database size, or better yet, where can I view it and what if I need more?
View 1 Replies View RelatedTempdb Limits
Aug 5, 2005Hi,
we want to execute store procedure in paralel up to 250 times
procedure called from diffrent connections and create 4 temp tables during execution
so in total me might have 1000 temp tables with 5000 rows in each table.
I can place tempdb on separate drive and increase to any size.
Would 1000 temp tables be problem?
Thank you
Alex
Resource Limits ??
Apr 11, 2007In Oracle & Sybase you can set login resource limits to restrict, time of day, number of i-o's, cpu use etc for any login, at the login method. All actions done from a resource limit constrained login inherit the constraints.
Is there any way to implment this with any MS SQL Server version from 200-2005/SP2?
Before anyone answers, please do not suggest set rowcount etc.
thanks.
SQL Server Limits
Feb 11, 2004Where can I find info on how much data SQL Server can handle? What is too big of a table in terms of records? When (in terms of table/db size)does processing from a web site using querys and joins begin to bog down?
Thanks!
Group By Limits??
Mar 7, 2008I have a query below that does not work:
select
rc_display,levelid, AccountID, Customer_Number_Tby, rc_series, LanguageID, level_description, leveloptionsid,
rc_unique, levelrcid, ring, ring_eng_block, description, description_long, component, Company_name, logo_location, AccessType, login_Name, account_pin, cb_comments, Emblem_DNF
from tmpKevin
group by
rc_display,levelid, AccountID, Customer_Number_Tby, rc_series, LanguageID, level_description, leveloptionsid,
rc_unique, levelrcid, ring, ring_eng_block, description, description_long, component,company_name, logo_location, AccessType, login_Name, account_pin, cb_comments, Emblem_DNF
Having Count(rc_display) > 1
However if I strip out a few columns:
select
rc_display,levelid, AccountID, Customer_Number_Tby, rc_series, LanguageID, level_description, leveloptionsid,
rc_unique, levelrcid, ring, ring_eng_block, description, description_long, component, Company_name
from tmpKevin
group by
rc_display,levelid, AccountID, Customer_Number_Tby, rc_series, LanguageID, level_description, leveloptionsid,
rc_unique, levelrcid, ring, ring_eng_block, description, description_long, component,company_name
Having Count(rc_display) > 1
This works. I work on MS SQLServer 2000. Is there a field limit with the group by clause?
Thanks
Kevin
Limits In SQL 2000
Jul 12, 2006I have a database with 10,000 records and I want to just see record1000 - 1025 is there an easy way to do this with a query. I have foundinfo for my SQL using limits but it does not work with SQL Server 2000.Please help.Thanks in advance.
View 1 Replies View RelatedSQL Server 2012 :: How To Write Stored Procedures To Load Data Model From OLTP To DWH
Nov 24, 2014How to write Stored Procedures to load the Data Model from OLTP to DWH ?
View 9 Replies View RelatedLimits Of Rows In One Table
Feb 25, 2000I have designed a datawarehouse and I have
tremendous doubt about SQL 7.0 capabilities.
I expect to receive more than 20.000.000 into
a single table and to be honest I do not know if
I will get a reasonable query-response time.
My Server is a Toshiba Lince with a 500 Mhz processor
and 256 Mb of RAM memory. My hard disks are IBM ones.
Can anybody say me something?
Thank you in advance.
Marcos Sá
Cascade Delete,any Limits
Jun 30, 2004Hi ,
I have 4 GB db with 6 tables,
table A parent of table B with cascade
update,delete
table B parent of C,D,E,F with cascade
update,delete
Today I deleted range of values(7000 rows) from table A, but NOT ALL data
deleted from B,C,D,E,F
Is any limitation for cascade delete
Db set to simple recovery, no errors deleting data
SQL Server 2000 Limits
May 15, 2001Im curious about how much data SQL Server 2000 can handle. Where does SQL Server stand today comparing to Oracle? It would be interesting to hear views and experiences from people here on swynk on SQL Server 2000. How big are the biggest tables, how many rows and speed etc. When is it time to consider using Oracle, if ever, instead of SQL Server 2000?
Regards,
S
Limits On Rows In A Table
Apr 27, 2006HiWe received the following in an email from a third-party supplier (whonaturally has a solution for the problem as described). It sounds likegibberish to me, but does anyone have any comments?<quote>SQL in its current incarnation hits a performance brick wall when atable contains more than about 75 million rows. This is not aconfiguration limit as the table could be grown a lot larger but theperformance issue generates problems for ??????; primarily duringsearch and retrieval of archived objects; although if the databaseengine is being heavily hit for retrieval the archiving process canslow down as well.</quote>Chloe CrowderBritish Library
View 6 Replies View Related