OLTP And Reporting Databases Seperated?
Jan 12, 2006
we 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)
View 2 Replies
ADVERTISEMENT
Sep 11, 2002
I have a reporting server with 5 databases which are currently being updated via log shipping. These databases need to be (read) accessed by the users. All users have System Admin perms due in order to access the databases. I need to tighten security on the server and remove SA perms from the users. The largest database is about 8gb and is growing slowly. What is the best way to move the databases efficiently from the source server to the reporting server? The log shipped databases can't be backed up and restored due to the standby mode. DTS is an option but may pose time issues due to database size especially as the databases grow in the future. Replication is another option but i have heard it has alot of issues. Any help is welcome. Thanks.
View 2 Replies
View Related
Aug 1, 1998
Is it possible / advisable to use Sql Server as the backend database for handling 40 million transactions,
taking around 8 GB space?
View 2 Replies
View Related
Dec 19, 2007
hello 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
View 2 Replies
View Related
Aug 28, 2007
hi. 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.
View 6 Replies
View Related
Jan 29, 2001
We are in the design phase of a project where it is estimated there will be 150 SQL Server databases - each one housing similar information on each of 150 client sites. The client needs a reporting tool to analyze and make comparisons across databases. We were thinking about a using a data warehouse. Does SQL Server have a native solution to this type of problem ? Or is there another, better way to approach this ?
View 1 Replies
View Related
Apr 19, 2007
I am trying to move my reporting databases on Server1 to another server (Server2). The databases are named ReportServer$Server1 and ReportServer$Server1TempDB, and I would like them to be named ReportServer$Server2 and ReportServer$Server2TempDB on Server2. I have followed all the steps in the KB articles on moving databases, and I have restored the key from Server1 onto Server2. I can use the Report Manager to change the Data Source connection string on Server2. However, when I try to access a report, or delete an unwanted object, it always errors out, and there is a reference to ReportServer$Server1TempDB in the error message.
Where is this dependency on the old name coming from? Is there no way to rename the report server databases?
Thx
Helen
View 1 Replies
View Related
Nov 8, 2006
Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.
I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.
Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).
Thanks!
View 6 Replies
View Related
Jul 26, 2005
Is there a way to return a comma seperated list in a query?For example if I have this simple querySelect member_name From members It returns all the members in diff rows (lets assume we have 3)So the result will bemember_name--------------name1name2name3Instead of this I need to have the result in one row with values seperated by commas.member_name---------------name1,name2,name3I hope I am clear enough. Any help would be appreciated. Thank you.
View 4 Replies
View Related
Mar 2, 2004
Whilst 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
View 2 Replies
View Related
May 5, 2008
What is the best way? I have a field of comma seperated article type id values in a users profile. This list is a set of values that sows the article types the person does NOT want to see. Each article has an article type id. I need to do a select joining the article table to the member table that only shows those article id's that are not in the comma seperated list. How would I do that in a sql statement?
View 3 Replies
View Related
May 14, 2008
Not sure the best way to do this. They are passing in a comma seperated string. I have a field that has comma seperated strings in it. I need to compare the 2 strings and do a select on that field that has any of the words in the string passed in. So if the pass...
"car,boat,van"
and in the field 2 of the 3 records get sent back
car,train,bike
boat,truck,plane
bike,plane,train
I would send them back the top 2 because the top one contains car and the second contains boat
View 5 Replies
View Related
Jul 23, 2005
Greetings 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 Related
Mar 7, 2008
I want to know the basic dfferences between these 2 (OLTP and OLAP) ?
View 3 Replies
View Related
Jun 17, 2015
I would like to setup replica for one of the databases for reporting. The current environment is a 2 node cluster(active/passive). I would like to add a 3rd node that can server as a secondary replica. The secondary replica will be on asynchronous commit mode.
The database that needs to have alwayson setup has column level encryption enabled.
* Do I need to backup and restore the service master key on secondary server in order to have the column level encryption to work on secondary server?
* What would be preferred Quorum settings?
* What is the setting for 'readable secondary' for primary and replica db?
* What should be the setting for 'Connections in Primary Role' for primary and replica db?
* We are trying to setup without a Listner. Do I need to setup AG Listner? Can the application exclusively use the [secondary instance name].[replica DB name] without a listner?
View 2 Replies
View Related
May 8, 2007
Hi!
We have multiple databases (1 per client) and we want to use reporting services for ad-hoc reporting. Our authentication is done by using client's database so security is custom.
What is the best way to set this up? Is it possible to somehow allow users/admins to use only their database and never see any others? I know we can install a separate instance of RS and do that but I am wondering if there is a better way to run multiple clients doing different reports using exclusively their databases without having to create a new instance.
Thanks
View 13 Replies
View Related
Mar 7, 2008
Hi I have a text field called testing which shows the selection the user has made from a checked list. I want to save the contents of testing to a datadase with coloum varchar and with a comma to seperate the selections. It stores ok but sql queries do not recognise them as comma seperated values. e.g the selection from the vb code produses: Beauty, Fashion, Travel. Sql takes the lot as one. When I test a query say select from table type where Category = beauy it gives nothing. How do I store it so sql would recognise it as seperate enteries.
my vb code is below
Dim msg As StringDim li As ListItem
msg = ""For Each li In CaregoryCheckBoxList.Items
If li.Selected = True Then
'asp has a security default of no accetping html in input fields br is html code so causes error
'msg = msg & "<br>" & li.Text & " selected."
msg = msg & li.Text & "," & " "
End If
Next
Testing.Text = msg
View 10 Replies
View Related
Mar 15, 2008
can u give me some idea how to make Sp who having two variables as a parameter having values seperated by ","
now thses vaues have to insert in to two tables tbColor .... colorname,product_id
and tbSize.......sizename,product_id
thanksss
View 11 Replies
View Related
Jun 3, 2008
Have a column in my DB called _Venue, which has been populated with address information separated by commas, like this:
Address1,address2,address3,address4
The address has sometimes only a few lines, for example I may have just Address1 or just address1 and address2 filled etc.
I need to write a query that will individually select addressX, so I can pick and choose what part of the full address to use.
Can these be done?
Thanks in advance.
View 9 Replies
View Related
Oct 15, 2007
Interested 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
View 5 Replies
View Related
Jul 23, 2005
To get rid of redundant data in a table, my cleint will be providingsomething like this:IDtokeep Ids to delete34 24,35,4912 14,178,145754 32,65,68I have to write a script for each of the above rows which looks likethis:-----------------------------------update sometableset id = 34where id in (24,35,49)delete from sometablewhere id in (24,35,49)-----------------------------------As I said I have to do this for EACH row. Can I somehow automate thisor will I need to write to same script for each row (there are about5000 rows in this audit table)Any help is highly appreciated.Here is the DDL and inserts for the audit table.IF object_id(N'dbo.dataclean','U') is not nullDROP TABLE [dbo].[dataclean]GOCREATE TABLE [dataclean] ([IdTokeep] int NULL ,[IdsTodelete] varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )GOINSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(34,'24,35,49')INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(12,'14,178,1457')INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(54,'32,65,68')GO
View 3 Replies
View Related
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
View 2 Replies
View Related
Nov 3, 2015
Simple 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.
View 8 Replies
View Related
Apr 22, 2008
I 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,
View 3 Replies
View Related
Jan 21, 2008
Hi,
My SP is,
ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber nvarchar(30),@StoreId nvarchar(500),@Year int)
AS
DECLARE @SQL nvarchar(1000)
-- Searching ItemNumber in Inventory_SKU
DECLARE @Count int
DECLARE @ItemNumSKU varchar(50)
SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId1) AND ItemNum = @ItemNumber1'
EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30)',@Count12=@Count OUTPUT,@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber
IF (@Count = 0)
BEGIN
SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId1) AND AltSKU = @ItemNumber1'
EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30)',@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber,@ItemNumSKU1=@ItemNumSKU OUTPUT
SET @ItemNumber = @ItemNumSKU
END
-- Creating table variable to have values from 1 to 12
CREATE TABLE #MonthSales (MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)
DECLARE @Cnt INT
SET @Cnt = 1
WHILE(@Cnt <= 12)
BEGIN
INSERT INTO #MonthSales VALUES(@Cnt,0,0,0)
SET @Cnt = @Cnt + 1
END
--Joining query result with the table variable to get required result
DECLARE @Status1 Char(1)
SET @Status1 = 'C'
-- putting ' for comma seperated storeid
SET @SQL = N'SELECT'
SET @SQL = @SQL + N' MS.MonthNumber,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'
SET @SQL = @SQL + N' FROM'
SET @SQL = @SQL + N' (SELECT '
SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'
SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'
SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'
SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '
SET @SQL = @SQL + N' FROM '
SET @SQL = @SQL + N' Invoice_Totals '
SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '
SET @SQL = @SQL + N' WHERE '
SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId1) '
SET @SQL = @SQL + N' AND ItemNum = @ItemNumber1'
SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year1'
SET @SQL = @SQL + N' GROUP BY'
SET @SQL = @SQL + N' DATEPART(mm, DateTime)'
SET @SQL = @SQL + N' ) Temp '
SET @SQL = @SQL + N' RIGHT OUTER JOIN #MonthSales MS ON MS.MonthNumber = Temp.Month#'
SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'
EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30),@Year1 int',@Status = @Status1,@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber,@Year1=@Year
It is working If I am passing sigle storeId to the above SP. If I am selecting multiple StoreIDs from the report. It is not returning correct value. Can you please give me a suggestion?
Thanks
View 3 Replies
View Related
Dec 1, 2015
I'm new to SSRS. I've installed SQL Server 2014 on my Windows 7 machine and logged in web interface at localhost/Reports, created data source with ODBC driver connecting to my PostgreSQL database, I've selected "Credentials stored securely in the report server" and provided login and password. Now I wanted to create some test report. So I clicked on Report Builder which launched a desktop application (not ideal I must say) where I tried to click through a wizard to create some report.
I tried table wizard, selected "Create a dataset" and selected my data source. It asked me to provide password again and then I got following error:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Even though test connection works just fine, even from that very same window.
So my questions are:
How do I fix that issue so I could create reports using SSRS and my PostgreSQL database?How do I embed the user/password into connection so users don't have to re-enter the password? The reason is, I want to create data source through SSRS API from my own application and I don't want to have users be bothered with passwords
Just to clarify - I tried creating ODBC data source through admin tools in Control pannel and gave it the same name but it didn't work, both SQL Server and PostgreSQL are installed on the same machine.Â
View 7 Replies
View Related
Oct 30, 2006
I want to pull out some information for documenting my user accounts.I want to be able to show all of the users and what companies those users have access to.
The table which stores the company information lists it in multiple rows, so there will be many results for each user
user -- companyid
johnd -- 2
johnd -- 5
johnd -- 1
I want to select this into one record so it will show up like this
user -- companies
johnd -- 2,5,1
The only way I know how to do this would be with a sub-query that uses a cursor to loop through & dump it into one string value, and then output the string value to the field. This seems extremely inefficient for such a seemingly simple task.
Can somebody help me out here?
thanks
View 3 Replies
View Related
Jul 14, 2015
How 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.
View 1 Replies
View Related
May 12, 2008
Hello,
My SQL knowledge is limited so if I get stuff wrong then correct me... but I can imagine this task will be quite testing...
I am working on a system that logs ([Audit] table) the changes to fields on some tables using a Trigger on UPDATE. I need to produce a 'quick' report that returns the date when the tables overallStatus field was set to 1.
In the [Audit] table I can find all the field changes for the record in question using this SQL...
select *
from audit
where rowid = 1309606
order by auditID asc
My problem is filtering this data. The fields I need are formatted as below, see records returned, in ASC order...
audit.AuditID = 2652583
audit.OperationTime = 2008-04-24 15:12:07.740
audit.ColumnDetail = 'estimatedProductionPriceactualProductionCost'
audit.EditDetail = '0.00000.0000'
audit.AuditID = 2658460
audit.OperationTime = 2008-04-25 10:51:47.930
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '05'
audit.AuditID = 2665723
audit.OperationTime = 2008-04-25 22:06:50.200
audit.ColumnDetail = 'overallStatusdespatchDateInsertionStatus'
audit.EditDetail = '1Â 3'
audit.AuditID = 2711092
audit.OperationTime = 2008-04-30 17:22:12.593
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '34'
audit.AuditID = 2713217
audit.OperationTime = 2008-04-30 20:46:34.817
audit.ColumnDetail = 'clientOrderNumber'
audit.EditDetail = 'PAT12P7640'
The funny character, , is ASCII 127.
So need to find when 1309606's overallStatus was changed to 1. Manually looking at the data, I can see overallStatus was modified in 3 of the above 5 [Audit] records. It started life as 0, then went to 1 and then to 3. I'm aware that I need to look at the previous Audits date for when it was changed to the value I'm looking for... So it was changed to 1 on 2008-04-25 10:51:47.930.
What is the best way to approach this problem? I'm hoping to use T-SQL only and not have to use an external scripting language, unless I can embed vbscript or jscript inside a T-SQL function and then use arrays, etc?
Somehow I need to convert the ASCII(127) seperated list of fieldname into an array or list, find the index of the fieldname 'overallStatus' and then lookup that value in the datafield.
Hope that makes sense and any help would be great!
Cheers,
Nick
View 6 Replies
View Related
Apr 5, 2006
I have a db that contains a column named DSCODES. Values in DSCODES are seperated by a comma.
If I run the following command.
select distinct(DSCODES) from DB
The following is returned.
S100,S102,S103
S100,S103,S105
What I would like returned is the following
S100
S102
S103
S105
Is this possible?
Thank you in advance
Graham
View 2 Replies
View Related
May 3, 2006
Hi All
I'm really new to SQL and would appreciate a solution to the following problem.
I have a table that contains 1 record. in that record there is a column called CODES which contains multiple values that are seperated by a comma.
C200,C201,C202,C204,C200,C203,C200,C202,C203,C205,C200,C202,C203,C204,C205,C206,C200,C201,C202
I need a solution to select all values from CODES that are distinct so I end up with the following.
C200,C201,C202,C203,C204,C205,C206
Any help would be really appreciated. Please bear in mind I'm a complet novice.
View 4 Replies
View Related
Jul 20, 2005
Scenario:Table 1 (a id, b name)Table 2 (a FKid, d value)A standard join on a gives me something like:a1 b1 d1a1 b1 d2What I want is:a1 b1 d1,d2I can easily do this with a function or cursor, but is is somewhatslow, and I need to do this a lot and I don't really want to have tomaintain tons of functions or cursors.Thoughts?
View 3 Replies
View Related
Mar 11, 2008
I use SQL Server 2000.
I have some names stored in a table in the following format...
eg
"Royales, Jon"
"Smith, Alan"
"Jones, Tom"
what I would like to see is...
"Jon Royales"
"Alan Smith"
"Tom Jones"
I assume I should search the string for a comma (,) and use split and then rejoin the values. I need to do this as part of my select statement... is it possible?
I have searched the forums for similar posts but didn't find anything helpful.
Jon
View 11 Replies
View Related
