Best Practice Guidelines
Jun 28, 2006
Hi All,
We have an application requirement for a database supporting field service engineers, which calls for a central SQL Server databse, and laptops with the same database replicated onto SQL Express. I'm resposible for designing the database for this, physical and logical. I've designed and built many a database, but never had to use replication before.
I've read through BOL, and understand how the merge replication process works, and I have no problem designing the database assuming it were to run on a single server.
What I am trying to find are whitepapers, or equivalent, on "best design and implementation practice", and especialy common mistakes to avoid.
I know that the windows programmers responsible for the UI will not completely abstract the database from the code (no matter how desirable that is or how often I tell them!), and I really don't want to find I have to change the physical tables or replication logic after they've coded most of the UI .
Many thanks in advance
Richard R
View 3 Replies
ADVERTISEMENT
Jul 21, 2004
Hi folks, ya all are just fine i hope.
I've a question regarding indexes.
I've a table that is very often updated. My boss asked me to keep a log of each record when the table is updated. I've created an AFTER UPDATE trigger on the table and throw the old record into a log table on each update. The table would get large earlier. I want the updates on the original table fast, so i don't wanna add any kind of indexes on the log table. The log table will be scarcely queried only if the user has made a mistake and we want the old record back.
Now, i've column named event(DATETIME) log table that records CURRENT_TIMESTAMP. Should i create a clustered index on this column so the records get in sequence (the values in the column will be unique due to time and seconds being stored). I could be able to access records fastly in the log table on the basis of EVENT column. But how much it would cost the updates on the original table??
My major concern is to keep the updates on original table fast while logging the old records.
Usually it's said not to created indexes on date fields. Do i need to create non-clustered index or i am following the correct path.
Any tips and guidlines; highly appreciated.
Howdy!
View 4 Replies
View Related
May 31, 2007
hi all
i want to know the basic guidelines for developing and implementing and developing sql server database
Thanks in Advance
Malathi Rao
View 3 Replies
View Related
Feb 16, 2006
What is exactly the recommended way of using a SqlCeConnection object in your application?
In all the examples I see (IBuySpyDelivery for example) it opens the connection object in the constructor and leaves it open most of the time until the class gets disposed.
Is that the way to do it? Or should you open and close the connection for each database action (insert/select/delete) you're doing on the local SQL Mobile database?
I'm a bit confused, but maybe I'm messing up the way of working on remote database servers with how it should be done on a local SQL Mobile db.
View 7 Replies
View Related
Jan 3, 2007
Hello all,
We have some doubts regarding NSTL guidelines for motorola Q. Our application is managed application. Which ocasionally connects to server. It is very helpful if we can get answers urgently.
Hopper Test How can we make it run on Smartphone 5.0 (Motorola Q)? Is this tool only applicable for pocket pc 2003?
Application Verifier Test Tool Is this tool only applicable for unmanaged code? If we are using .NET as the development environment, do we still have to use this tool?
Call Interupts Is their a special way of handling Call Interrupts in the application or the default behaviour provided by the device (Moto Q) is OK?
Send Key (Green key) on the device What is the expected behaviour of this key when the application is running? Do we have to handle it separately in the application
Is their a maximum space limit allowed for creation of the database files for an application?
Is their any specific utility like MaxfileCNT which NSTL uses for checking the behaviour of the application when there is no space available on the device?
thanks & regards,
Rashmi
View 3 Replies
View Related
May 3, 2004
We are about to change the sa password, currently all packages and jobs rely on this account. I imagine there is probably a better architecture that we could employ to ease this process. Any suggestions recommendations?
Also any caveats I should be aware of regarding places to look that might currently rely on the sa account so that we do not need to worry about existing processes from breaking?
I think we are going to create an NT account for DTS Packages and possibly use the same account for any DTSRun jobs, does this make sense? Or is there anything to gain by having these as separate accounts? Also should this be the same account used to run the MSSQLServer process?
I tried doing a search for this information here, thinking it was already covered, but could not find anything that informative, any resources that you could point me to would be appreciated, I will look on BOL as well as MSFT to see what I can dig up.
View 1 Replies
View Related
Apr 16, 2008
I am looking for guidelines for log file sizing, specifically where database reorgs are required
View 1 Replies
View Related
Nov 7, 2007
Good Morning, I've been searching through all the tutorials and questions, have tried many things. I am still getting "[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]"
as an error. This is what I've got:
Im using SQL Server Express 2005, installed with the default settings. Had not touched this program for anything until I started to follow directions to fix Error 26. Visual Web Developer Express ASP.NET is what Ive used to build the webpage. I was using the ASP.NET web configuration to add users to the database, which is set to use the provider ASPSqlServerProvider.
SQL Server 2005 Surface Area Configuration
Database Engine Remote Connections set to Local and Remote Connections Using TCP/IP Only?
SQL Server Browser is Enabled and Running
- is set to Active, under Built In Account Network Service
I have created Windows firewall exceptions for:
sqlservr.exe
sqlbrowser.exe
udp port 1434
SQL Server Configuration Manager
both SQL Server and SQL Server Browser are running.
Under SQL Server 2005 Network Configuration
Shared Memory and TCP/IP are enabled only.
SQL Native Client Configuration
Shared Memory 1
TCP/IP 2
Named Pipes 3
all enabled
I read through the post at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but as I'm new to SqlServer I do not know how to check on the first three items.
I'm getting extremely frustrated, and would just like the login portion of this website to start working before my boot ends up through the computer. Please help, lol, thank you.
View 9 Replies
View Related
Oct 17, 2006
Hi,
We have a number of databases running on our SQL2000 Servers. The databases use Stored Procedures which we call from our VB.NET apps, developed in-house. We now wish to upgrade our servers to SQL2005.
In the past, when upgrading from VS2002 to 2003 and then 2005, we have developed and executed Test Plans to ensure that the code upgrades properly. Microsoft even supplies FXCop and other tools to help. I now wish to develop and execute a similar Test Plan to ensure that our databases will work as required.
Does Microsoft supply any similar tools or guidelines for testing that databases written for SQL2000 will work as required on SQL2005? I see the Upgrade Wizard to examine the Server but is there a similar tool for testing databases? Are there any guidelines on the tests to include in such a Test Plan?
Thanks
View 3 Replies
View Related
Nov 24, 2006
Hi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)
Best Regards,
Hans
View 1 Replies
View Related
Jun 2, 2004
Hello, everyone:
Does any one know the good T-SQL practice topic web site? It is better to include solutions. Thanks a lot.
ZYT
View 2 Replies
View Related
Sep 3, 2006
When setting up databases for end users, what's the best practice regarding who's the dbo for each individual database - the user itself or a sysadmin?
Does it really have any importance at all who the owner (as defined by 'dbo') is ?
View 5 Replies
View Related
May 7, 2007
(Terms)
TermID, Term
1----- Abc
2----- Arcico
3----- Tunic
and
(RelatedTerms)
TermID, RelatedTermID
1 ------ 3
1------ 2
2------ 4
I want to get the following results
1.- a list of all the terms that start with A%
2.- a list of all the related terms β¦ that belong to terms that start with A%
For number 1 - I am doing a select on Terms table with where term like A%.
For number 2 β I am joining both tables and then once again doing a where term like A%.
Would it be more efficient to take the first results and put them in a table variable, and then just do a join with the second table RelatedTerms.TermID = Terms .TermID
The number of records that generally comeback are between 500 to 1000 records that
What would you consider is a better approach ? or maybe there is an even better way ?
View 4 Replies
View Related
Aug 14, 2007
Wasn't sure where to ask this question.
Was wondering what everyone is doing in regards to server/db protection?
Do you run your DB's on independent servers?
Do you run your DB's on clustered servers?
Do you run your DB's on redundant haardware w/ a 3rd party
application for bit-to-bit data replication?
Is anyone placing the DB's on a SAN's?
We're at a crossroads, and looking for a good direction to ensure the DB's are up.
Any insight welcome.
Thanks,
Kerry
View 3 Replies
View Related
Dec 11, 2007
hello
i'm a newbie for sql , but i want to learn sql on my own , is there any way that i can learn sql , do i have to download sample database from the internet, do i need to have my own server to play with. Hopefully someone show some lights on this.
regards
sutha
View 2 Replies
View Related
Apr 10, 2006
Please point me to a web resource from where I can study:1) writing complex queries such as those involving HAVING, mult-levelnested queries, GROUP BY, T-SQL functions2) Joins - a lot of practice3) Stored Procedures, transactions, cursors and triggers - I need someheavy-duty practiceWhere can I get some good practice of the above? Also, please recommenda good SQL Server/T-SQL book in the light of the above requirement.
View 3 Replies
View Related
Jan 11, 2006
Folks - had a look around Google and no surprises, but never found what i was looking for.
I want to see a real work best practice C# Stored Procedure for Sql 2005 (express is what i am using, but don't mind the Sql edition).
Almost everything i see is a "select * from table" which to be honest was my first stored proc many years ago - everything since has been fairly detailed.
I ask as i am sceptical, after years of trying to STOP building Sql queries in code (as it's hellish!) that the CLR technique really makes any kind of a diffence.
If someone has found that it HAS i'd love to hear about it. The thought of:
SqlCommand cmd = new SqlCommand ( "My Whole Stored Proc as Text" );
... doesn't appeal, never mind the potential for debugging syntactical issues and so on.
I was excited by this, until it became something i had to do in a real situation and then i got a little worried. Should i be?
View 7 Replies
View Related
Nov 2, 2006
create a table and name it Salary Information. Add an Employee Name and Salary column to the table. Create a column in the Employee table and name it Salary. Create a trigger that updates the Salary table with the employees's name and salary each time u insert data into the Salary column of the Employee table.
I tried but it didnt work!
Pls, help me!
View 4 Replies
View Related
Mar 12, 2008
Hi, my database is growing over 1Gb, and I only have one .mdf to keep them all. Should I use a secondary data file for my data? Can I do that now? Thanks.
View 5 Replies
View Related
Jan 24, 2007
Good Morning,
I work for a company that has sees alot of people come and go. The one thing I have noticed is that people use their admin accounts to log into SQL and create sp, views and databases.When the user leaves I am stuck with all these objects that are owned by somone no longer working for the company.
So my question to you guys is: What is the best practice to use in creating new objects?
Thanks for your guru-ness!
View 3 Replies
View Related
Jul 24, 2007
say i have a customer.aspx that allows a user to enter in customer data.
on customer.aspx, i have dropdownSalesRep which allows the user to associate a sales rep with the customerbut some customers come to directly, and not thru a sales rep, so I want the user to be able to specify "none"
Is it best to have a dummy record in my SalesReps table called "none" with an ID of say "999", or is there some other better way to deal with this?
View 5 Replies
View Related
Aug 7, 2007
Hi.
We have developed as quite simple ASP.Net webpage that fetches a number of information from a SQL 2005 database. We are having some problems though, becuase of a firewall that is beetween the webserver and the SQL server, and I think this is because of bad code from my part. I'm not that experiensed yet, so I'm sure that there is much to learn.
Usualy when I do a query against a SQL database, I do something like this:
Function GO_FormatRecordBy(ByVal intRecordBy As Integer) Dim dbQueryString As String Dim dbCommand As OleDbCommand Dim dbQueryResult As OleDbDataReader dbQueryString = "SELECT Name FROM tblRegistrators WHERE tblRegistratorsID = '" & intRecordBy & "'" dbCommand = New OleDbCommand(dbQueryString, dbConn) dbConn.Open() dbQueryResult = dbCommand.ExecuteReader(CommandBehavior.CloseConnection) dbQueryResult.Read() dbConn.Close() dbCommand = Nothing Return dbQueryResult("Name") End Function
Now, lets say that I have a DataList that I populate with Integer values, and I want to "resolve" the from another table, then i do a function like the one above. I guess that this means that I open and close quite alot of connections against the database server when I have a large tabel. Is there any better way of doing this? Chould one open a database connection globaly in lets say the ASA fil? Whould that be a better aproch?
When I added the CommandBehavior.CloseConnection to the ExecuteReader statment, I noticed that it was a bit faster, and I think there was fewer connections in the database, so maby there is more to the "closing connections" then I usualy do.
Any tips on this?
Best reagrds,Johan Christensson
View 6 Replies
View Related
Dec 16, 2003
Ok.
I recently started developing a web site for a client using storefront.net and ms sql server.
the db schema of storefront.net has autonumbers as the PKs for the products table (even though the products table contains an additional field for product_number.)
So here's my dilemma if you care to read:
I typically develop local, deploy remote (after testing). I have a local SQL server, and then the remote SQL server.
When I'm developing for this project, I'll insert data such as products to the products table (sometimes several times while i'm working out routines to import data to the products table.) this has the effect of creating a unique ID for each product based upon SQL auto-incrementing INTs.
This StoreFront.net (SF.NET) has another table that is a lookup table. For each part number, it has a corresponding categoryID number.
Now, if i have product_ID 1234, and I set the category ID to say 10 and get it working on my local box, every thing is fine.
Here's where the problem comes in: When I use DTS to transfer the database during remote deployment, each product is inserted into the remote DBs products table and gets a NEW product id. Same with the categories.
This has the effect of breaking the relationships. (SF.NET has no ref integrity nor relationships defined in the db.) let's say my product_id 1234 gets put into the remote copy, it'll get a new product_ID (PK). let's say it's now 5775. now my category ID will also get a new value. so my data is now not related.
I don't know how to handle this situation. The unique IDs generated on my local sql will nearly almost always be different from those generated on the remote db.
How do i handle this situatoin is my question? advice, guys?
View 3 Replies
View Related
Jun 28, 2005
Hi to everyone! I've to create a little auction system that runs on web. Before starting developing, I'll would like to be sure to use the best practice...The main aspect is to avoid conflicts on database updating with bids, i.e. if a user places his bid I've to be absolutely sure that his bid is the highest at the moment of updating database. If not, I have to refuse it...So I ask you: using transaction is the best way for assuring the non-conflicts? And may I have to be careful of some other aspect in ASP.NET pages? Or there's no problem of conflicts at page level?Thank you very much in advance for any suggestion, and If anyone has some other thing to say about possible problem on auctions I'll be glad to hear him!!!! ;-)
View 6 Replies
View Related
Jun 4, 2001
Following works fine in QA
master..xp_cmdshell 'osql -Usa -E -h -w250 -Q"set nocount on exec unallocated" -dTest -oc:est.txt' ,no_output
but does not from within a sp
err -
Server: Msg 170, Level 15, State 1, Procedure test, Line 2
Line 2: Incorrect syntax near 'master'.
Thank you.
Ivan
View 1 Replies
View Related
Aug 20, 2004
I have around 10 databases currently residing on different platforms which make-up for roughly a terrabyte of information. I would like to migrate all of these DBs over so that they are all managed under one instance of SQL server 2K. In my view this streamlines things a lot and reduces costs of licensing/hardware.
However, is managing all of these databases on one clustered instance of SQL 2k the best approach from a performace stand point? Would it be better to seperate each database onto its own machine? I am under the impression that given enough hardware (processors, RAM) using just one instance of SQL 2k enterprise should be enough to perform the mangement of this data. Is this correct? Is there an optimal model?
Money is always a concern but in this case, performance is the main objective. The size of the data managed will be growing significantly so the system should be scalable.
My background is as a developer so I may not have provided enough to give a good answer. Please ask questions if you need more detail. I am looking for suggestions on the best way to handle this.
Specifically I would like to know the preferred architecture as well as any suggested hardware.
Thanks in advance
View 3 Replies
View Related
Dec 11, 2004
I'm building a database that has maybe four unique tables Student,
Advertiser, Employee, maybe Account. Three of the four table (Student,
Advertiser, Employee) have something in common in which they all contain
fields such as emailAddress, password, role, isAccountActive, etc. which
allow them to access their respected data. However, is it best practice to
build a fourth table which contain Account information or should I just
include that information in their respected tables?
My thinking is that if you have a fourth table such as Account then you can
manage all accounts (Student, Advertiser, Employee) from one table, but as
the database gets more in-depth you have to build more and more complex
stored procedure to do simply task such as update, delete, select, etc.
View 11 Replies
View Related
Oct 10, 2005
Hello All ..
This is the scenario I'm having :
-- I'm a beginner so bear the way I'm putting it ... sorry !
* I have a database with tables
- company: CompanyID, CompanyName
- Person: PersonID, PersonName, CompanyID (fk)
- Supplier: SupplierID, SupplierCode, SupplierName, CompanyID (fk)
In the Stored Procedures associated (insertCompany, insertPerson, insertSupplier), I want to check the existance of SupplierID .. which should be the 'Output' ...
There could be different ways to do it like:
1) - In the supplier stored procedure I can read the ID (SELECT) and :
if it exists (I save the existing SupplierID - to 'return' it at the end).
if it doesn't (I insert the Company, the Person and save the new SupplierID - to 'return' it at the end)
------------------------------------
2) - Other way is by doing multiple stored procedures,
. one SP that checks,
. another SP that do inserts
. and a main SP that calls the check SP and gets the values and base the results according to conditions (if - else)
3) it could be done (maybe) using Functions in SQL SERVER...
There should be some reasons why I need to go for one of the methods or another method !
I want to know the best practice for this scenario in terms of performance and other issues - consider a similar big scenario ..... !!!
I'll appreciate your help ...
Thanks in Advance . ! .
View 1 Replies
View Related
Jan 16, 2008
Need the following question addressed, as it keeps coming up in our development meetings and has been creating a divide. Pease voice your opinion.
To keep it simple, we have Table1 which identifies several questions that are revised on a regular basis. One of it's columns is called "Revision Status". Within revision Status, we would like to identify the possible status of a question such as:
New Questions;
Revised;
Resubmit;
Inactive;
Active;
...as well as several more.
I'm of the mind to have these in a seperate table identified with a unique ID... call it StatusTable.
Such as:
1 New Questions;
2 Revised;
3 Resubmit;
4 Inactive;
5 Active;
However others feel, just use the "Revision Status" column and simply use the numbers "WITHOUT" a table or description. The developer documentation will tell the developer which number equals the description. ie the following would be found in the Revision Status column.
1
2
3
4
5
My mind says the above is ilogical. I would rather join and say in my statement:
WHERE StatusTable.Status = 'Inactive'
Where the other way would be
Where [Revision Status] = 4
I hope i'm not being thick-headed.
Please advise.
View 10 Replies
View Related
Jul 20, 2007
I'm looking for advice on the best way to stop stored procedures and CLR assemblies from being
copied from their originally installed server to a different server, for the same company or even copied
to another company.
Are there established ways for achieving this level of protection.
Also, I was hoping that encrypting stored procedures would be a 100% reliable way to stop
malicious copying of the code. But I have read that this is not the case. Any advice in this
area would also be appreciated.
Thanks
Steve
View 4 Replies
View Related
Aug 9, 2006
Hi Everyone
We are building a new site using ASP and SQL as the backend.
Any idea where I can find some information about best practices to coonect the 2.
I was thinking about IIS on a DMZ with port 80 only open and the SQL inside the internal network and open port 1443 between them.
Any ideas will be welcomed.
Thank you
Oren Levy
View 2 Replies
View Related
Mar 27, 2008
What is the best way to do this:
I have a table (TableA) say below with 6 colunms
CPubID --------CCTable ---------CCField--------------- DWTable----- DWField
101 XYZ Type NULL NULL
102 XYZ ClaimAssocType NULL NULL
103 XYZ ID NULL NULL
104 XYZ ID NULL NULL
105 XYZ PublicID NULL NULL
106 XYZ PublicID NULL NULL
107 XYZ PublicID NULL NULL
109 XYZ PublicID NULL NULL
201 XYZ PublicID NULL NULL
301 XYZ PublicID NULL NULL
and a Table (TableB) with three Colunm
CPubID --------DWTable ---------DWField---------------
101 T1 F1
102 T1 F2
103 T3 F1
104 T1 F3
105 XT1 F4
106 T1 F5
107 T2 F8
109 T1 F9
201 T1 G1
301 T2 G2
001 T1 F1
002 T1 F2
903 T3 F1
904 T1 F3
905 XT1 F4
706 T1 F5
307 T2 F8
409 T1 F9
801 T1 G1
301 T2 G2
I want to move data of TableB to TableA depending upon the CPubID and TableA should finally look as below
CPubID --------CCTable ---------CCField--------------- DWTable----- DWField
101 XYZ Type T1 F1
102 XYZ ClaimAssocType T1 F2
103 XYZ ID T3 F1
104 XYZ ID T1 F3
105 XYZ PublicID XT1 F4
106 XYZ PublicID T1 F5
107 XYZ PublicID T2 F8
109 XYZ PublicID T1 F9
201 XYZ PublicID T1 G1
301 XYZ PublicID T2 G2
It needs to update the TableA with respective data in Table B
View 3 Replies
View Related
May 5, 2008
Please advice on the best way to this
Create Table TEST
(
[ColID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SubColID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Table1] [varchar](55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field1] [varchar](55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Key1] [int] NULL,
[Key2] [int] NULL
)
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1001', 'Payment', 'Amount', '','')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1001', 'Payment', 'ID', '','')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1001', 'Payment', 'CheckPublicID', '121','1221')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1002', 'Payment', 'Amount', '','')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1002', 'Payment', 'ID', '','')
INSERT INTO TEST (ColID, SubColID, Table1, Field1, Key1, Key2)
VALUES ('101', '1002', 'Payment', 'CheckPublicID', '131','1321')
select * from test
101 1001 Payment Amount 0 0
101 1001 Payment ID 0 0
101 1001 Payment CheckPublicID 121 1221
101 1002 Payment Amount 0 0
101 1002 Payment ID 0 0
101 1002 Payment CheckPublicID 131 1321
I want to replace the 0's and populate with respective keys as below
101 1001 Payment Amount 121 1221
101 1001 Payment ID 121 1221
101 1001 Payment CheckPublicID 121 1221
101 1002 Payment Amount 131 1321
101 1002 Payment ID 131 1321
101 1002 Payment CheckPublicID 131 1321
View 6 Replies
View Related
