I have a large db that goes through regular maintenance
every week. The problem seems to be that while the db is re-indexing some of my
key tables I get time out errors.
Are there any suggestions on how I can combat
this problem.
Management has tasked me
with having 24 hour access to the db because if people aren’t completing there
transactions we don’t get paid.
I have
also noticed the timeout issue when we are running in files that create new
records.
Is this a basic problem with
the way the db is structured?
Are they
any tools that I can use to change our process so that we can maintain the 24
hour access to the db.
How are other
companies maintaining 24 hour access to there db?
i want to check the new username check availability from database in a same page ,i did in normal coding i need ajax or java scripts method because this page look and feel is not good thanks, @mbi
I am developing a .Net application for a Guest house for my final year project. I'm using C# and SQL Server. I want a way to look up if a room is available or not on a particular date.
First I came up with... Room: Room_id(PK), Room_type, Room_rate_pp, Room_availability
but how can I distinguish whether a room is available on a particular date? I think Room_availability is useless in this table because it can't do this. Do I need some sort of calendar table or something?
Your help with this would be greatly appreciated. it's annoying me so much, and I can't move on with the project until I have my database finalised :eek:
I been trying to learn availability groups since I have not implemented it.
From my understanding you can have more than one group.
Lets pretend we have two groups in one instance:
1. Accounting 2. Engineering
From my understanding you can't make a database in two AG because it wouldn't make sense.
But lets pretending there is one database that both are used by accounting and engineering.
Would you have to make a third AG for future fail overs so that other databases in the other two group don't failover when not needed because when you fail over an AG all the databases inside it fail over.
I'm working on an application which is mainly used to look up compressors and their data which are held in documents.
This is a simplified representation of the tables of importance:
Document -------- Id (int, identity) (PK) Number (nvarchar(50))
Compressor ---------- DocumentId (int) (PK, FK) Id (int, identity) (PK) Name (nvarchar(50))
CompressorData -------------- CompressorId (int) (PK, FK)) Id (int, identity) (PK) Value (nvarchar(50))
The tables are linked as follows: Document -> Compressor -> CompressorData
Non clustered indexes are created on Document.Number and Compresor.Name because these fields are used for querying.
At certain points corrections will be released on compressors which will result in: - Creating new documents with new document numbers (note that a non clusterd index exists on Document.Number). - Copying affected compressors of existing documents into the new documents (note that a non clusterd index exists on Compressor.Name). - Copying the data of the affected compressors into the new documents.
This can result in creating ten's of new documents and copying hundreds compressors and thousands compressor data records.
My question:
Will the users still be able to query for compressors while corrections are released (thinking about indexes which need to be modified) or will their be so many locks held that the database becomes unusable?
SQL 2012 EnterprisePrimary server and 2 x secondary serversWindows 2012 R2
I have removed one of the database from availability group by mistake. Luckily I am still operational with primary server. database on secondary servers are on restoring mode.
I have done full backup of database from primary (prod) server and restored on secondary servers with no recovery when I add database into availability group I get an error message log missing what is the best method to achieve and add database again into availability group.
Note I cannot restore database on primary server as it is on production
I would like implementing a database mirroring architecture with SQL server 2005 but i have questions.
If i don't use a cluster architecture, i would like knowing if there's any solution to move the alias SQL server from the primary to the secondary by script.
I've got an availability group with multiple databases, replicating to multiple secondary servers. On one of the secondary servers, some of the databases are not synchronising, and when we try re-establish the sync we get an LSN error. I can't see any obvious way to re-establish only one database on one secondary without affecting all databases on that secondary or affecting that database on all secondary nodes.
The options I seem to have are to either remove the database and then re-add it, in which case this affects all secondary replicas, or to remove the secondary replica and add it, in which case all the DBs are added.
We are planning to upgrade our production servers from mirroring to alwayson. Our current mirror setup gives the advantage that it can failover a single database.To have a similar setup in alwayson we are probably going to create an availability group per database. Any other disadvantage in this except for the extra initial configuration work?
I'm working on an application which is mainly used to look up compressors and their data which are held in documents.
This is a simplified representation of the tables of importance:
Document -------- Id (int, identity) (PK) Number (nvarchar(50))
Compressor ---------- DocumentId (int) (PK, FK) Id (int, identity) (PK) Name (nvarchar(50))
CompressorData -------------- CompressorId (int) (PK, FK)) Id (int, identity) (PK) Value (nvarchar(50))
The tables are linked as follows: Document -> Compressor -> CompressorData
Non clustered indexes are created on Document.Number and Compresor.Name because these fields are used for querying.
At certain points corrections will be released on compressors which will result in: - Creating new documents with new document numbers (note that a non clusterd index exists on Document.Number). - Copying affected compressors of existing documents into the new documents (note that a non clusterd index exists on Compressor.Name). - Copying the data of the affected compressors into the new documents.
This can result in creating ten's of new documents and copying hundreds compressors and thousands compressor data records.
My question:
Will the users still be able to query for compressors while corrections are released (thinking about indexes which need to be modified) or will their be so many locks held that the database becomes unusable?
I need to move files for a lot of databases that are all part of an AG. I've used the method at the bottom of this link with success on a small test DB.
Discovered that a geo-spatial AlwaysOn HA database (1 of 4) was not synchronizing as at a point in time earlier in the day. Â Suspend Data Movement appears to be working perpetually without finishing. Â The SQL Server services is in a Pending Changes state after an attempt to restart it from SQL Configuration Manager. Â The Cluster Dashboard says it is in a Not Synchronizing state, with only the one database in question having a yellow triangle, all 3 others show green. Â
The warning for the cluster is:At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state. Â There is no abnormal data movement from the primary to the seconday.The warnings for the unhealthy database are:
The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.Either a database administrator or the system has suspended data synchronization on this availability database.So how to get this database back to synchronizing state?
Backing up all databases on a sql server that hosts secondary high availability databases as well as other databases. Â The other databases back up fine, but the high availability secondaries all get the same error:
BackupDiskFile::OpenMedia: Backup device 'F:MSSQLBackupdbnamedbname_backup_2015_MM_DD_tttttt_ttttttt.bak' failed to open. Operating system error 2(The system cannot find the file specified.)
I have a situation where I have two servers in SQL Server 2012 R2 AlwaysOn Availability Group. One is primary and the other one being secondary.  I am only running SharePoint Database on it.I have run out of space on the primary server and about to run out of space at the secondary server. I have tried shrinking database transaction log files, but it returns an error that it cannot be shrunk as the database is in the AlwaysOn Availability Group.
Questions: 1. Several forums suggest that databases need to taken out of AlwaysOn Availability Group in order for the shrinking to work properply? 2. Would it have any impact on the database if it is taken out of availability group and then added back?
I have configured replication between Always ON Availability Groups  (Listener) (PUBLISHER), remote distributor to XYZ SUBSCRIBER...with above link ...
Now, I want to know how to replicate Data from XYZ SERVER a PUBLISHER to Always ON Availability Groups  (Listener) (SUBSCRIBER)? Distributor Database being on XYZEX:
XYZ SQL SERVER as PUBLISHER, and DISTRIBUTOR to Always ON Availability Groups  (Listener) SUBSCRIBER...
need to migrate a cluster with an AG dtabases to new data center cluster with AG.
I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.
This is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.
Hi, can I assign a NAS(Network Attached Storage) server to store the database file(readable and writable) and assign other several MS SQL database servers which will use the same database file in NAS to achieve the objective of high availability?
If it can, how can I set it up in MS SQL Server or it requires another 3rd patry software to set it up?
I want to run remote procs from a local - central proc, but don't want to crash my proc if the remote NT Server or SQL Server is not available. So, I want to test for NT and SQL availability first.
/* I find the server-dbasename name in a lookup table, and then build dynamic script */ SELECT @DBName = "<SRVRNAME~DBASENAME>" SELECT @QueryString = N'master..xp_cmdshell "ECHO >' + SUBSTRING(@DBName, 1,6) + 'pipesqlquery", no_output' EXEC @Results = sp_executesql @QueryString
SELECT @Results /* <-- testing here.... */
=====================
If the NT / SQL is running, "@Results" should = 0 (success on the ECHO to the PIPE) If the NT / SQL is NOT running, "@Results" should = 1 (error on the ECHO to the PIPE)
If I test in QA, I get the expected "@Results" values.
If I run the proc, I get "@Results" = 0 for both events, and them my proc fails on the remote proc call if the NT / SQL is NOT running.
I don't know how to write DMO in TSQL to try that. I have also tried using 'odbcping' too.
HINTS? Better way to test remote NT / SQL is NOT running?
Hello everybody, We are starting new project Customer validation with minimum wait time. How to insure High-Availability? We have 2 standard servers and in past used custom log shipping.
Log shipping still requre manual intervention ,while goal to switch between servers automaticly.
Clustering is a last possible solution.
Could someone recomend any other soluion or products?
i'm not fluent with sql and i have a question to ask..
if i want to check whether a product is available between two dates, how can i do so? for example, i want to check whether it's available between 5/23/2007 to 5/25/2007..
My problem is trying to calcuate whether a room is booked during a dateperiod.I have a table with two columns (Start and End date).I need some SQL code to calculate whether a room is booked during a daterange.e.g.The booking entry is:Start21/11/2005End25/11/2005Any help on this would be appreciated.The End Date indicates the last night of the stay.Regards,Steven*** Sent via Developersdex http://www.developersdex.com ***
I am in the process of setting up server replication using the Central Publisher Model. We are doing this for application availability. The application is a .NET app and this application uses the SQL Server name in the connection string. As far as I know we can€™t have two severs on the same domain with the same name. So if our primary server (server A) goes down and that server name is in the connection string how does the application get redirected to our backup server (server B). Thanks for the help.
I am in the midst of designing a new Data Warehouse system. As we get further into the design of the system, the more we are realising how complex our ETL is going to be and that the amount of time it will take to run could be significant i.e. a few days! My question is obviously I don't want to have a down time in my relational system for this long and prevent my users from accessing the data for days at a time each month. So what functionality should I be looking at to allow me to maintain a working copy of the data that users can query whilst I perform database updates and then perform a quick promotion of the updated data to users for querying?
If you can point me in the direction of the right functionality in SQL Server 2005 and possible some relevant white papers that cover this sort of scenario I would be grateful.
Hi, I am new in SQL SERVER.I wanted to setup high availability database.It would be great if any one could answers my questions please. -What are the options availability for HA except clustering? -How to set up log shipping HA ? -How to monitor log shipping? -Is standby DB and log shipping same ? -Can I setup log shipping without backup/restore method.Like I have to create db1 on server A and create db1 on server B and then configure log shipping? -Is it necessary to ship log for master database or only user Database? Thx -Blace
Hi, I'm a businessperson, not a software engineer, so please bear with me if my questions seem simple.
We are about to migrate from MS SQL Server 2000 to 2005. In our 2000 deployment, we have a primary server, with real-time replication to a backup. If we have a hardware failure, we do a manual failover to the backup server. This has served us well for years, with decent performance and no data loss. The SQL server is accessed by a pair of IIS servers, which use network load balancing to split the load between them.
As part of the forthcoming migration to SQL 2005, we are going to a hardware platform with 2 SQL servers sharing a single storage array. We would like to get the performance benefit of utilizing both of the SQL servers to handle requests simultaneously, and not just have the second server as a backup. Ideally, the two IIS servers would dish requests to both SQL servers, either randomly or in some sort of load-balanced manner.
So here is my question: how can we have both SQL servers active and handling requests simultaneously, achieving both high perfromance and high uptime?
If it is not supported natively in SQL Server 2005, is there a third-party software solution that can achieve this?
Finally, where else should I go to do further research on this subject?
I have to describe Microsoft SQL High Availability options in the following layers: infrastructure, middleware and application. I know there are following options available: failover clustering, replication, mirroring and AlwaysOn but I am not sure in which layer each of them are.
I have implemented log shipping between 2 databases, the transaction log size normally between 10mb - 50mb every 15 minutes during normal working hours, but it grows to 9GB when we run database optimization job and that makes it hard and long to transfer and apply the transaction log on the other database. Does anybody encountered a situation like this and is there a way to minimize the size of the Trans log after the optimization job?
