Best Practise/architecture Question
Jul 27, 2007
I need to load a lot of Excel, CSV, ... etc. files. These files have hundreds of columns and I need to validate the data. Some are simple range type checking, some are more complex checking involve multiple columns.
There may have several hundreds of such rules. And I may need to let the program to automatically correct some invalid data in the future.
Where to implement it in SSIS?
Or just load the files without any checking (all type to text), and checking using T-SQL?
(BTW, I don't have biztalk server).
Thanks in advance.
Read more >> Options >>
View 5 Replies
ADVERTISEMENT
Oct 13, 2006
Hi!I have 6-7 tables total containing subobjects for different objects like phonenumbers and emails for contacts.This meaning i have to do some querys on each detailpage. I will use stored proc for fetching subobjects.My question therefore was: if i could merge subobjects into same tables making me use perhaps 2 querys instead of 4 and thus perhaps doubling the sizeof the tables would this have a possibility of giving me any performance difference whatsoever?As i see pros arefewer querys, and cons are larger tables and i will need another field separating the types of objects in the table.Anyone have insight to this?
View 5 Replies
View Related
Apr 30, 2008
I want to log all changes made to a table (only updates, since there will be no deletes or inserts).
I would like to see the user who changed it, the date and time, fieldname, old value, new value.
If more fields are changed during the update, than add more records into the logging table.
What is best practise to achieve this?
Thanks,
Frans
View 13 Replies
View Related
Nov 6, 2007
Hi,
I have some data that is updated every day but I don't know when. I'm trying to make a solution that runs a SQL query to check if this data has been updated. If it has, I'll send the updated data with FTP as as text file.
How would you solve this?
My idea is to have 2 SSIS packages.
- Package1: One runs at the same time every day (inserts any missing updates to a table)
- Package2: One runs every hour to check the missing updates table, and runs Package1 if any update for a missing data is found.
My only worries is if Package1 is running and at the same time Package2 decides to run Package1 then I could get into trouble if I'm using temp tables with the same name for the text file updates etc.
Thank you.
View 1 Replies
View Related
Feb 6, 2002
Hi Folks,
I am about to upgrade my main database server (5 db's - largest 16Gb) from
NT 4 SP 6a / SQL Server 7 SP3 to Windows 2000 SP 2 / SQL Server 2000 SP 2
I am planning to detach the db's, backup to tape a few times and then totally
trash the server, rebuilding it with the new software, restore the db's from
tape and the reattach the db's.
Any reason I should not use this method and can folk advise the best practice
way of achieving this?
Regards
Paul
________________________
Paul Starling
ERP Database Administrator
View 1 Replies
View Related
Aug 10, 2007
Hi, hoping I can get a few view on a question I have relating to the above.
I am new to Stored Procedures and Triggers and I am trying to understand 'best practice' a little better. Here is my question: If I have a table that stores information, and when any field in that table is updated (and changes) I would like to inactive the row, prior to change and then add the change by way of a new, active row. This way I can see what it was before and that it's inactive, and what the active value is.
Hope this makes sense, if this is the wrong way to manage change history any suggestions would be appreciated.
A second question I have is as follows: If I have a table that stores a number, based on that number, what would be the best way to create new records in a different table that pulls from the first table. Where the number stored in table 1 represents how many times the record is to be created in the second table.
Thanks. If anyone needs more data, please feel free to ask, I will help as best as I can and appreciate any advice & comments that you can give.
Paul
View 3 Replies
View Related
Jun 15, 2007
As title, what's the best practise of importing XML data into SQL Server 2005?
I have found this one. http://support.microsoft.com/default.aspx/kb/316005. Is it good enough?
The XML is generated by another system on daily basis. So, the import should be able to handle insert and update cases. How can I do that? Thanks!
View 2 Replies
View Related
May 12, 2006
So I got 2 classes one I wrote to interrogate databases using normal ADO:Mine:SqlConnection myConnection = new SqlConnection(m_sConnectionString);SqlCommand myCommand = new SqlCommand(sQuery, myConnection);myCommand.CommandTimeout = 120; // 60 Seconds TimeoutmyConnection.Open();SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);return result;Microsoft WaySqlDatabase dbSvc = new SqlDatabase(m_sConnectionString);DbCommand dbCommand = dbSvc.GetSqlStringCommand(sQuery);return ((SqlDataReader)dbSvc.ExecuteReader(dbCommand));What's faster?My way:SqlConnection myConnection = new SqlConnection(m_sConnectionString);SqlCommand myCommand = new SqlCommand(sQuery, myConnection);myCommand.CommandTimeout = 120; // 60 Seconds Timeout// Use a DataTable – required for default pagingSqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);DataTable myTable = new DataTable();myAdapter.Fill(myTable);myConnection.Close();myConnection.Dispose();myConnection = null;return (myTable);Microsoft Way:SqlDatabase dbSvc = new SqlDatabase(m_sConnectionString);DbCommand dbCommand = dbSvc.GetSqlStringCommand(sQuery);DataTable dtData = null;DataSet dsData = dbSvc.ExecuteDataSet(dbCommand);dtData = dsData.Tables[0];return (dtData);Comments? Ideas?Al
View 4 Replies
View Related
Mar 5, 2007
I have master tables that I will be updating from our ERP system. Some examples I have seen take an approach of dropping a table in SQL server then creating it again before importing; some, and probably my choice, append and update; I have not seen an example where records are all deleted then the data appended afterwards. Of the three approaches which is generally regarded as best practise / most efficient?
View 1 Replies
View Related
Jan 2, 2008
Hi,
I work with a large team developing ASP.NET application that has a large database with over 50 complex stored procedures. It is proving more and more difficult and time consuming to centralise the development and update of the database changes and I was wondering if there were any best practises/tools that could be recommended. I have looked on the web for good articles and haven't found anything difinitive (except that Team Foundation Server is the way forward)..
A brief background to the current process is that everyone develops on the same database, and then updates the stored procedure scripts in source safe (manually). Then when we do a new release someone builds a script of all the database updates and runs it. There are issues related to developers updating there stored procedures over other peoples and other concurrency.
I am looking to move all the developers to start using local databases so that there work only effects them, but then this brings up problems of keeping all the local databases up to date whenever they get the latest source code. The only way I currently see is to build a database update program, that will run and update to the latest version.
Surely this must be a common issue? Anyone have any good ideas/concepts?
Also our setup is Visual Studio 2005, SQL Server 2005 and Source Safe 2005.
Cheers,
Andrew Thomas
View 5 Replies
View Related
Sep 6, 2004
Hello,
I want to kow if the following architecture is good :
Disque 1&2 ( Raid 1)
c: OS
d: sqlserver + system tables + log files
Disque 3&4&5 (Raid 5)
e: data
View 1 Replies
View Related
Sep 29, 2004
Hi,
Someone can tell me if the following architecture is good :
f:master db
g:soft db + log
View 2 Replies
View Related
Dec 18, 2007
where can we get to know the architecture of sqlserver 2005
View 4 Replies
View Related
Jan 22, 2001
We have database thats transaction intensive, so we are trying to sepetrare ldf file from mdf file to a different disk array. what raid should I use for the Transactional log file(.ldf).
Thank You,
John
View 1 Replies
View Related
Apr 2, 1999
I would like to know where I can find a senior database architect. Someone who can develop
and implement the database and its stored procedures. I am looking for an experienced person.
It is a contract position, in San Franicisco. The pay is good. Could anyone help me?
I tried Dice, Monster and it seems all of you are working...
View 2 Replies
View Related
Jun 2, 2004
I am replacing the corporate SQL Server at work. The new server will have 6 striped disks of 160G with about 4G of RAM. The current SQL Server currently has two instances which run web applications and a small database warehouse about 6G. Analysis Services is also installed.
Due to a couple of new apps being added to the server and the SQL Server 2000 enterprise license we acquired, i was thinking of adding 2 more instances so that the applications can be independently managed in terms of restarting the SQL Server. I also would like to permanently fix the memory settings on each instance to give more resources to more important applications. The log and data files would also be spilt onto 2 separate hard disks. i understand there are implications on performance such as CPU etc. Is it normally advisable to have more than 1 or 2 instances ? Most of the applications are not very CPU intensive. What other implications or performance issues would l have ?
View 4 Replies
View Related
Sep 1, 2006
We will be creating a moderately high-volume OLTP database application that needs 24/7 availability. We are planning to offload OLAP processing to a second copy of the system. We will be using SQL Server 2005.
I originally planned to set the second server up with SQL Server 2005 mirroring to cover the 24/7 availability requirement, with the idea that we could also do OLAP reporting off of the mirrored copy of the database. But I've gotten some indications that a mirror database is offline and not available for querying. So I figured I would use transactional replication to keep the OLAP database current. Now I am wondering if I need to use mirroring at all, or if I should just use transactional replication on the entire database and swap to the replicated database if the production server crashes.
What is everyone's opinion?
Replication only, for both OLAP reporting and failover?
Mirroring to one database for failover, with replication to a another database for OLAP reporting?
View 3 Replies
View Related
Feb 21, 2004
Hello, Everyone:
What duties are included within SQL Server Architecture? Thanks a lot.
ZYT
View 1 Replies
View Related
May 13, 2008
Can any one of you please explain the SQL architecture in a short..
Or can you give me the link, so i can refer it..
Its very urgent..
Pls help me..
Thanks In Advance
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
View 4 Replies
View Related
Jun 14, 2007
We are designing a SQL Server architecture that will need to handle a lot of inserts and many users.
We are not sure what approach to take if we should build a distributed server architecture with a Single Master SQL and many Slave SQL Servers.
Or if we should get a really powerful machine with many processers to handle all the connections.
We project we will have about 5,000 Inserts per sec coming from 500,000 users.
What do you think or what has your previous experience been with handling many inserts and few reads for many users?
Thank you
View 2 Replies
View Related
Oct 20, 2007
Hi there,
My question is more "architectural" than technical.
One of our standard is to systematically enclose the code of our stored proc within a TRY/CATCH block.
What is your point of view?
Should a TRY/CATCH be included even for simple operations?
Are there any drawbacks?
Thanks!
EDIT: Thanks for your input.
View 4 Replies
View Related
Jun 29, 2007
I've implemented a solution with application, database and report server on seperate machines. The application is a web app and is Internet facing. What is the best method for executing reports on the RS server that are initiated from the web server? Using URL access requires a login or anonymous access neither of which are desired. Web services works but I loose access to the toolbar. Is there some other way to pull this off where I can let the public access reports and give them access to the toolbar?
Thanks.
View 4 Replies
View Related
Jun 5, 2007
In a situation where one may have a single master SQL Server that ultimately needs to communicate information back down to 1000's of downstream servers, what is the recommended architectural approach?
It doesn't feel right to have to add 1K-5K routes to the master SQL Server. Is there a way to have the dowstream servers "broadcast" their existence to the master, so that new servers can be added and updates can happen seamlessly? Does this fall into a pub-sub scenario or is there a better way? And, if so, how to ensure an open conversation (so that one server doesn't miss information that all the other servers received)? Should the master dynamically create routes or better to rely on an open conversation initiated by the downstream server?
View 20 Replies
View Related
Dec 31, 2007
Hi Everybody ,
Could Anybody please explain in detail about the Achitecture of Sql server reporting services?
1) What a Report processor in detail and what it does?
2) What is the exact work of Extensions?
3)What is SOAP and WMI?
Thanks in Advance
Mahasweta
happy new year
View 4 Replies
View Related
May 31, 2007
hi
i am haveing 1.5 years exp in asp.net(c#),i want to improve my knowlege in Database Architecture (datmodeling,uml,normalization,etc..).could anyone suggest me course or any books
View 4 Replies
View Related
May 23, 2007
I have the following stored procedure:
INSERT INTO MyTable ( Value1, Value 2)
VALUES( @Value1, @Value2)
SELECT SCOPE_IDENTITY()
How do I put this sp in the DAL typed dataset, so I can get the Identity value in the Business Layer?
View 2 Replies
View Related
May 22, 2008
When having multiple files for log, do they get filled one after the other (like Oracle) or proportionately fill?
------------------------
I think, therefore I am - Rene Descartes
View 2 Replies
View Related
Aug 23, 2006
Hi all,
Can u explain what exactly happens after index creation .
View 2 Replies
View Related
Sep 10, 2007
Not sure if this is the right forum?
Is it possible to export an ERD on System Architecture to VISIO/WORD?
Thanks
View 7 Replies
View Related
Dec 18, 2007
Don't know if this is the appropriate forum. I am looking for an experienced SS consultant to review our setup, hardware architecture, recovery plan, and to provide high-level advice moving forward. My company is a CRM hosted software provider with a dynamic, metadata-based product built in Visual Studio 2005. Currently we run on SS 2000, but plan to migrate to SS 2005 or 2008. We anticipate quite a bit of growth and want to make sure that we are on the right path. Let me know if you are interested or know someone who is.
If I should post this elsewhere, let me know.
View 2 Replies
View Related
Jul 23, 2005
Hi,Is 'sqlserver.exe' the only windows process does everything for thatinstance of the database?Please explain in details the SQL server process architecture.Thanks*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Feb 7, 2008
To all the SQL H/A experts, we were wondering if we could have 3 physical nodes and 2 active/passive clusters architecture setup on a SAN as seen in the image below? http://www.geocities.com/juanlieu/CP_Arch.JPGIn case you cannot see the diagram, it would looks something like this:
active/passive Cluster A ---> physical server A (Win2003/SQL2005) ---> HP EVA SAN ---> physical server B (Win2003/SQL2005) ---> HP EVA SANactive/passive Cluster B ---> physical server B (Win2003/SQL2005) ---> HP EVA SAN ---> physical server C (Win2003/SQL2005) ---> HP EVA SAN
In this setup, I understand that Server B cannot be called upon as the active server at the SAME time by both clusters. question: what would happens if it does, would Server B reject the last cluster that calls it?Appreciated in advance.
View 3 Replies
View Related
Jan 5, 2007
Hi All... We're developing an asp.net based project. We plan to deploy it across multiple servers running in an NLB environment. That is NLB via hardware or software - we generally leave that decision up to our customer. Different from prior projects we've done, this application will rely on a SQL Server 2005 database. With the NLB, we essentially install our application many times across multiple servers. As hits come in from clients, they'll get directed to one server or another by whatever NLB technique is being used. The applicaiton generally doesnt care which server is hit.
But what about the database? What's a typical or best architecture to employ? Should each server have an instance of SQL Server and then somehow through replication they all keep each other updated? Or should each of the servers hit one lonely instance of SQL Server and it somehow keeps some other backup instance updated? I think the first approach seems to make more sense from a load balancing point of view, but depending on how many servers there are, it could get quite complicated.
Again, we're in the early stages so we really havent done much research on this yet. Any tips would be appreciated. Any good white papers out there?
Thanks! -- Curt
View 1 Replies
View Related
